Difference between revisions of "MySQL"

From wiki.mikejung.biz
Jump to navigation Jump to search
(IO Scheduler and Kernel Tweaks)
Line 7: Line 7:
# To change to deadline
# To change to deadline
echo deadline > /sys/block/sd$/queue/scheduler
echo deadline > /sys/block/sd$/queue/scheduler

Revision as of 20:29, 27 July 2013


IO Scheduler and Kernel Tweaks

For almost all cases, you should be using deadline for the IO scheduler. CFQ is the default on a lot of old distros (CentOS). Luckily this is easy enough to change. The command below only sets this while the server is up, a restart will clear the setting. There are ways to make this permanent, but for this section I'm only going to show how to change this in real time.

# First, see what scheduler is in use
cat /sys/block/sd$/queue/scheduler

# To change to deadline
echo deadline > /sys/block/sd$/queue/scheduler

General File System Mount Options for MySQL

Generally, you will want to make sure the volumes used for MySQL Data, or Logs are mounted with the following options:


MySQL and SSDs

RAID Card Settings

  • Percona suggests disabling ReadAhead, or Read caching on the controller. Typically the OS and MySQL will have their own Read caches, so it's best to leave the RAID card's cache for writes.

General Thoughts and Concerns when running MySQL on SSD

  • Percona recommends that you keep the databases on SSDs, but move over the innodb log files, or bin logs to a RAID array backed by a BBU with write cache enabled. I'm not 100% sure about this, as it sometimes makes more sense to just use one large 8 disk array and store all the data on it, instead of splitting this up and using two 4 disk RAID 10s, one with SSDs, the other with SATA or SAS drives. I will need to do more testing to see how quickly the SSDs wear out.
  • Percona also mentions that since the log writes are very small, which can cause write amplification on an SSD, which could lead to increased latency and quickly degraded drives. I can see how this would happen, however as SSDs get more and more efficient at writes, and more endurance this could be less of an issue. This really seems to depend on what kind of SSDs you use. For instance, you might not run into an issue if you use an Intel DC S3700 drive, but you might kill a cheap consumer SSD in a hurry.
  • With Percona, you can write transaction logs in 4KB blocks instead of the default 512-byte blocks, this can be much more efficient on SSDs
  • This is an example of how you could split up the MySQL files between an SSD and a RAID backed by a BBU with write caching:
On the BBU RAID:
- InnoDB Log Files
- ibdata1
- MySQL Bin logs (If using Replication)

On the SSD:
- Everything else :)

InnoDB Read and Write Threads

  • With SSDs, MySQL can be tuned to allow for higher read and write concurrency than typical spinning disks. You can tune the following to help with performance:
##Default is 4 for both of these, might need to try various numbers to find the best performance
innodb_read_io_threads = 16
innodb_write_io_threads = 16

InnoDB IO Capacity

  • This parameter should be set to approximately the number of I/O operations that the system can perform per second. You probably don't want to set this really high, otherwise the OS might suffer. I will do some testing with this, but I would imagine settings this anywhere from 5 - 10 x the default should be fine, depending on your storage system. I would start at 1000, or 2000 and raise this until you get the best performance. Settings this much above 20,000 might cause system issues.
# Default here is 200
innodb_io_capacity = 2000

InnoDB Log Size

Typically, you want this to be high enough to improve performance, but low enough so that it doesn't take forever to recover from the logs if there is a crash. However, recovery time is only an issue with spinning disks, since their random IO sucks. The recovery time is MUCH faster when using SSDs, so raising the COMBINED log sizes to around 3-4GB(Version 5.5 or lower) should help. If you have two logs in the group, you would set the size to 2G since 2 x 2GB would hit the max size of 4GB.

innodb_log_file_size = 2047M

Percona/MariaDB Specific InnoDB Flushing Tweaks

Setting this to "keep_average" will help avoid stalling on SSD systems. Percona added this specifically for SSDs.

# Default is estimate
innodb_adaptive_checkpoint = keep_average

# If this is enabled, it can add a lot of extra latency, if you are using SSDs, percona recommends disabling this.
innodb_flush_neighbor_pages = 0

DoubleWrite Buffer

  • Percona mentions that disabling this can sometimes be safe, however, even they are not 100% sure this is totally safe. It does sound like disabling this will offer a larger performance increase on SSD systems.

General Variables

This section is meant to be an easy reference guide. Not all of these are set in stone as far as what is good or bad. Some of these settings do not even need to be added to my.cnf, some are defaults already. I plan on expanding this section further as I get more information as to what each setting does. A great guide on the basic settings to tune is here:


key_buffer (previously key_buffer_size)

  • Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
  • Divide the Key_reads by the Key_read_requests, generally you want this to be less than 25%
key_buffer = 256M  !!Cannot set over 4G at this time


  • Generally can be set to 8190, but can be raised further if this is mentioned in the logs, or if your table_cache needs to be set higher
open_files_limit = 8190


  • 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger.
table_cache = $amount_of_tables


  • InnoDB relies heavily on the buffer pool
  • The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB.
  • The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size.
  • Having a large buffer pool can cause the server to take a LONG time to shutdown.
# For a dedicated MySQL server with 8GB RAM, this is a good starting point (assuming they have all InnoDB tables)
innodb_buffer_pool_size = 5000M
# To take advantage of a larger buffer pool, split this between multiple instances (MySQL 5.5 + only)
innodb_buffer_pool_instances = 5


This setting determines the size of the log files in use. By default, there are two log files with default size of 5M.Raising this value can improve performance on busy servers.

  • Recommended settings would be between 32M and 64M
  • To apply a new size you need to cleanly shut down MySQL, remove the old logs out of the way(ib_logfile0 ib_logfile1), adjust the size in my.cnf, then start MySQL


  • The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

More here: http://blog.secaserver.com/2011/08/mysql-recommended-my-cnf-settings-innodb-engine/


When innodb makes changes to data, it stores this in the log buffer, which is stored in RAM. Raising this value can reduce bottlenecks, especially if large transactions are being processed. This can cause small queries to lock up.

  • Recommended settings are between 1M and 8M


Changed to imrpove performance, but it can result in less durability.

  • 0 = write the log buffer to the log file and flush the log every second, but do nothing at transaction commit.
  • 1 = default setting, guarantees that you won't lose commited transactions
  • 2 = if MySQL crashes, you will not lose transactions. However if the server crashes you can lose 1 second of transactions



  • When innodb_file_per_table is set to 1, doing a DROP TABLE can take a long time on servers with a large buffer pool, even on an empty InnoDB table. This is because InnoDB has to scan through the buffer pool to purge pages that belong to the corresponding tablespace. Furthermore, no other queries can start while that scan is in progress.
  • When innodb_lazy_drop_table is ON, XtraDB optimizes that process by only marking the pages corresponding to the tablespace being deleted. It defers the actual work of evicting those pages until it needs to find some free pages in the buffer pool.
  • When innodb_lazy_drop_table is OFF, the usual behavior for dropping tables is in effect.


This allows you to assign one file per table. generally a good setting to configure on any server.

  • Slow DROP TABLE performance when enabled.
  • Requires innodb to lock and scan the buffer pool while it looks for pages belonging to the table. This is very slow on servers with large buffer pools.
  • To mitigate this, use innodb_lazy_drop_table


This should always be enabled, as it helps to protect against data corruption. Really should not need to change unless there is a specific reason given. Does not really impact performance that much.

===tmp_table_size and max_heap_table_size=== These need to be set to the same value.

  • A good starting point is 32M for both values


For the most part, this is not a huge deal to set. A good default starting point is 500 on busy servers. Watch the max_used_connections variable to find out what this needs to be set to.


Watch the threads_connected variable to find out what the average is. Really should not be set higher than 250 in most cases

SHOW STATUS LIKE '%thread%';
SHOW STATUS LIKE 'Connections';
  • You would then use the following formula to attempt to get this number as close to 100% as possible
100 - ((Threads_created / Connections) * 100)
thread_cache_size = 32  !! or higher if you really need to


Disables DNS lookups for hosts connecting to the server. This can be disabled unless you are relying on hostnames in your setup. This can actually cause issues with a lot of dns lookups are failing, so adding this should prevent that from happening.



Each session that needs to do a sort allocates a buffer of this size. From reading online, this is best left alone, as in don't set it unless there is a good reason. If you do set this, leave this as a small value, no larger than 2MB.

sort_buffer_size = 512K



Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072.

I have not really heard a good argument for changing this from the default. This is not a game changer setting. I'm not putting a recommended value here, just the default. Tune if there is a good reason to do so, otherwise just leave this alone.

read_buffer_size = 128K



  • When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
  • However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
  • Again, this is not really effective unless you are running single queries and really know what is going on. Leaving this at the default should be fine unless there is a specific need.
read_rnd_buffer_size = 256K



  • The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
  • Might want to raise this if a MyISAM repair is taking a looong time, otherwise might not need to change this.
myisam_sort_buffer_size = 8M



  • The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.
  • The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
  • Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0
  • query_cache_limit – This is the maximum size query that will be cached.

tmp_table_size and max_heap_table_size

http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/ http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

  • The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)
  • You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
  • Below are good starting points. Raise this if you have a good reason to.
max_heap_table_size = 32M
tmp_table_size = 32M



  • The number of seconds the server waits for activity on a noninteractive connection before closing it.
  • Raise this if you see “MySQL server has gone away”
  • A good starting point
wait_timeout = 30



  • The number of seconds the server waits for activity on an interactive connection before closing it.
  • Good starting point
interactive_timeout = 150

Install MySQL 5.5

wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

rpm -i MySQL-client-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-devel-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-server-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-shared-5.5.27-1.el6.x86_64.rpm

Percona Toolkit

Link to pdf (will need to register): http://form.percona.com/Percona_Toolkit_212_Operations_Manual.html


Percona repo added:

yum install percona-toolkit.noarch

From source (works on MySQL, Percona, MariaDB, etc)

wget percona.com/get/percona-toolkit.tar.gz

Slow log enhancements Add more verbosity to the slow query log:

mysql -e "SET GLOBAL log_slow_verbosity = 'full';"

Slow query enhancment:

mysql -e "SET GLOBAL profiling_server = 1";

Development tools

pt-duplicate-key-checker -- Find redundant foreign keys and indexes. Displays the results, then gives you the commands to remove duplicate / redundant indexes and keys.

pt-query-advisor -- Searches log files for bad MySQL habits and queries.

pt-show-grants -- Shows all the grants on the server and diplays them in an easy to read fashion.

Profiling tools

pt-index-usage -- Reads log files and prints out a list of unused indexes. Be sure to take into account the time frame of the log file, as some indexes may be useful, but the logs do not contain queries due to a short time frame.

pt-visual-explain -- The same as the EXPLAIN statement, however it formats things in an easy to read tree.

MySQL configuration

pt-config-diff -- Compares my.cnf accross multiple MySQL servers.

pt-mysql-summary -- Grabs the current variable from the live MySQL server. This gives a lot of detailed, useful information.

pt-variable-advisor -- Analyzes current variables and points out some obvious issues, or unneeded settings, etc.


pt-deadlock-logger -- Runs as a daemon that extracts and logs deadlock information as it happens. This can be used as a diagnostic tool to gather otherwise difficult to find information. Can start this tool, leave it running overnight, then review the results later on. Can cause some performance overhead, but can be very useful.

pt-fk-error-logger -- Similar to deadlock-logger, but logs foreign key errors instead.

pt-query-digest -- Analyzes query execuation. Logs and generates a query report. Can be used to filter, replay or transform queries. Best to set slow query time to 0, then let this run for a few minutes during peak hours. According to Percona, if you only use one tool, this should be it!

Percona's my.cnf suggestions

datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
pid_file                = /var/lib/mysql.pid
user                    = mysql
port                    = 3306
storage_engine          = InnoDB

innodb_buffer_pool_size = <value>
innodb_log_file_size    = <value>
innodb_file_per_table   = 1
innodb_flush_method     = 0_DIRECT

key_buffer_size         = <value>

log_error               = /var/lib/mysql/mysql-error.log
log_slow_queries        = /var/lib/mysql/mysql-slow.log

tmp_table_size          = 32M
max_heap_table_size     = 32M
query_cache_type        = 0
query_cache_size        = 0
max_connections         = <value>
thread_cache_size       = <value>
table_cache_size        = <value>
open_file_limit         = 65535

socket                  = /var/lib/mysql/mysql.sock
port                    = 3306

General commands

Get info on current settings:



Get info on tables in a database:

show table status;

See MySQL status. Updates every 1 s

watch -n 1 'mysqladmin proc stat'

Repair and Optimize

Optimize Tables

for i in $(mysql -e "show databases;" | sed 's/Database//') ; do for each in $(mysql -e "use $i; show tables;" \
| sed 's/Tables.*//' ;) ; do mysql -e "use $i ; optimize table $each" ; done ; done

MySQL check that will: check all databases, analyze, optimize and repair. Pretty useful, usually safe

mysqlcheck -Aaor

MySQL check for specified dbs, ie, check dbs that start with a-g, etc

for list in $(ls -lahd /var/lib/mysql/* | cut -d \/ -f5 | grep -e '^[m-p]'); do mysqlcheck -r $list; done

Grants and Privileges

Grant Privileges on a database

GRANT ALL PRIVILEGES ON data_base.* to 'user_name'@'location / ip' IDENTIFIED BY 'whateverthepassis';
SHOW GRANTS ON user_name;

Dump and Import

Dump a database

mysqldump database > database.sql

Import a database

mysql database < database.sql

Connect String

Connect to a database

mysql -u user -h ip -p databasename

Viewing and Deleting Tables and Databases

Look at databases and tables

> use databasename;
> show tables;

Drop (delete) a database. Can be useful if importing a database and it gives you an error

> drop database databasename;

Upgrading MySQL

Template:Box Warning

If pre-MySQL 5.0

 mysqlcheck -Aaor

If MySQL 5.0 to 5.1

 mysqlcheck -Agr

Back up all the databases

 mkdir -p /backup/mysqldumps
 cd /backup/mysqldumps
 for i in $(mysql -e "show databases;" | cut -d ' ' -f2 | grep -v Database); do `mysqldump $i > $i.sql`; done

Enabling a Slow Query Log

How to enable a slow query log

touch /var/lib/mysql/slow.log
chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this:


Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf :

long_query_time = 5

changing 5 to whatever number of seconds you want.

Types of data and storage engines



Show status:


Find all INNODB tables:

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB'"

Find all MyISAM tables:

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MyISAM'"


  • The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information, and speeds up processing so much, that dedicated database servers assign up to 80% of their physical memory to the InnoDB buffer pool.
  • Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
  • If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.

Overview of InnoDB Tablespace and Log Files

  • Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory.
  • In some cases, database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance.
  • For example, the following setting explicitly creates a tablespace having the same characteristics as the default:
innodb_data_home_dir =
  • By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.


MySQL 5.5 depricated settings

  • MySQL 5.5.xx will not start if you add either of the following two options into my.cnf

Detecting Mutex Contention

  • You can detect when you suffer from mutex contention in the buffer pool by reading the information provided in the SEMAPHORES section of the output of SHOW INNODB STATUS:
  • Under normal circumstances this section should look like this:
OS WAIT ARRAY INFO: reservation count 50238, signal count 17465
Mutex spin waits 0, rounds 628280, OS waits 31338
RW-shared spins 38074, OS waits 18900; RW-excl spins 0, OS waits 0

If you have a high-concurrency workload this section may look like this:

1 ----------
3 ----------
4 OS WAIT ARRAY INFO: reservation count 36255, signal count 12675
5 --Thread 10607472 has waited at buf/buf0rea.c line 420 for 0.00 seconds the semaphore:
6 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
7 waiters flag 0
8 --Thread 3488624 has waited at buf/buf0buf.c line 1177 for 0.00 seconds the semaphore:
9 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
10 waiters flag 0
11 --Thread 6896496 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore:
12 S-lock on RW-latch at 0x8800244 created in file buf/buf0buf.c line 547
13 a writer (thread id 14879600) has reserved it in mode  exclusive
14 number of readers 0, waiters flag 1
15 Last time read locked in file btr/btr0cur.c line 442
16 Last time write locked in file buf/buf0buf.c line 1797
17 Mutex spin waits 0, rounds 452650, OS waits 22573
18 RW-shared spins 27550, OS waits 13682; RW-excl spins 0, OS waits 0

May want to raise innodb_buffer_pool_size if the server has memory to spare.

Locking due to query cache

If we experience query cache contention, the output of SHOW PROCESSLIST will look like this:

Id      User    Host            db      Command Time    State                          Info
2       root    localhost       test    Sleep   2       NULL
3       root    localhost       test    Query   2       Waiting on query cache mutex  SELECT number from t where id > 0;
4       root    localhost       test    Query   1       Waiting on query cache mutex   SELECT number from t where id > 0;

It may be a good idea to disable query cache entirely.

Locking and Concurrency


  • Locks table on INSERT, UPDATE, DELETE.


  • Locks row on INSERT, UPDATE, DELETE.
  • Supports transactions which will not commit until all the queries are completed.

Types of locking

  • Shared (read only) Statements like SELECT will work, however any statements that change data will need to wait until after the lock is released.
  • Exclusive (read and write) All statements need to wait until the lock is released, typically this is the lock that transactions use.
  • Deadlocking happens when two different users or sessions are attempting to update the same data in a row.

MyISAM and InnoDB repairs



  • REPAIR TABLE for MyISAM tables is similar to using myisamchk for repair operations, and some of the same performance optimizations apply
  • For REPAIR TABLE, the same principle applies, but because the repair is done by the server, you set server system variables instead of myisamchk variables. Also, in addition to setting memory-allocation variables, increasing the myisam_max_sort_file_size system variable increases the likelihood that the repair will use the faster filesort method and avoid the slower repair by key cache method.
  • Please note that most of the time you only need to repair a crashed table, NOT the entire database. To do this do the following, in a screen!
use $database;
REPAIR $crashed_table;
  • If you notice a repair is taking a long time, check to make sure it's not repairing from key cache:
  • Repairing from Key Cache is very slow and raising the following variables could speed things up:
PLEASE NOTE, these are arbitrary values, use common sense 
key_buffer_size = 1GB
sort_buffer_size = 16M
read_buffer_size = 16M
write_buffer_size = 16M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 512M
  • Also, keep an eye on the .err log while the repair is happening. You might need to change the location of tmpdir if you see errors in the log while the repair is going.




  • Typically InnoDB recovery will happen automatically after a reboot or crash. However, if this is not successful you might need to force recovery
innodb_force_recovery = $1-6
  • innodb_force_recovery is 0 by default (normal startup without forced recovery)
  • Other settings are as follows; PLEASE do not raise this value without understanding what you are doing!
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

Do not run transaction rollbacks after recovery.

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

Do not do the log roll-forward in connection with recovery.
  • If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost.
  • Once / IF you are able to get things started, you will want to dump the database then reimport to get things working again. This can get messy, so don't use this as your only guide.