July 30, 2014

10 MySQL settings to tune after installation

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings

Here are 3 settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

InnoDB settings

InnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently used than any other storage engine. That’s why it should be configured carefully.

innodb_file_per_table: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).

With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only.

innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your primary concern is data safety, for instance on a master. However it can have a significant overhead on systems with slow disks because of the extra fsyncs that are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable because committed transactions will be flushed to the redo logs only once a second, but that can be acceptable on some situations for a master and that is definitely a good value for a replica. 0 is even faster but you are more likely to lose some data in case of a crash: it is only a good value for a replica.

innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values.

innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase innodb_log_buffer_size.

Other settings

query_cache_size: the query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your MySQL application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it.

log_bin: enabling binary logging is mandatory if you want the server to act as a replication master. If so, don’t forget to also set server_id to a unique value. It is also useful for a single server when you want to be able to do point-in-time recovery: restore your latest backup and apply the binary logs. Once created, binary log files are kept forever. So if you do not want to run out of disk space, you should either purge old files with PURGE BINARY LOGS or set expire_logs_days to specify after how many days the logs will be automatically purged.

Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled.

skip_name_resolve: when a client connects, the server will perform hostname resolution, and when DNS is slow, establishing the connection will become slow as well. It is therefore recommended to start the server with skip-name-resolve to disable all DNS lookups. The only limitation is that the GRANT statements must then use IP addresses only, so be careful when adding this setting to an existing system.

Conclusion

There are of course other settings that can make a difference depending on your workload or your hardware: low memory and fast disks, high concurrency, write-intensive workloads for instance are cases when you will need specific tuning. However the goal here is to allow you to quickly get a sane MySQL configuration without spending too much time on changing non-essential MySQL settings or on reading documentation to understand which settings do matter to you.

About Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom.

In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

Comments

  1. To get a decent my.cnf you can start with https://tools.percona.com/wizard and then proceed to tune based on that.

  2. aftab says:

    >Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled.

    I think binary logging is required for point-in-time recovery, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made.

    innodb-purge-threads – This option variable controls purge function, In versions prior to 5.5 that was part of the responsibility of the InnoDB master thread. A high load on the server that dirtied a lot of pages would force the master thread to spend most of its time flushing and therefore no purging would get done and vice a versa.

  3. Aftab,

    This is interesting topic to look at the Binary log on the slaves. I like to have binary log at the slaves enabled in all cases. By default slaves are not going to write anything into binary log anyway – unless there is log_slave_updates option enabled.

    Keeping binary logs enabled with log_slave_updates off allows me to very easily see if there have been any direct writes to the slave because of some mistake.

    In many cases we want one of the slaves to be ready to become master easily in this case it is best if it has both binary log and log_slave_updates enabled.

    From recovery standpoint it is entirely possible to use master binary logs for crash recovery whenever you’re using master or slave as a source.

  4. Rick James says:

    A quibble: “For instance, innodb_buffer_pool_size should be set in MB”. That setting is in bytes, not “MB”, although you can use a suffix of M (or G).

  5. Stephane Combaudon says:

    Thanks Rick, this is fixed!

  6. Karl says:

    It would be interesting to know more about the query cache being a performance bottleneck – in what situations?

  7. Rick James says:

    Query Cache:

    * EVERY update to a table causes ALL entries in the QC for that table to be flushed. If the QC is large and/or updates happen frequently, this leads to pauses for the purge. (If you use the QC, don’t make it bigger than 50M.)

    * If the QC is on (type != OFF _or_ size > 0), a “mutex” is taken out on _every_ SELECT so that it can check the QC. (Yes, even before it notices SQL_NO_CACHE.) An old benchmark showed 11% overhead.

    * With multiple cores, and multiple threads running, the QC serializes (at some level) the SELECTs. This makes it hard to get parallelism. (There are many other things in the way of parallelism, too.)

  8. Roman says:

    I performed recomendation above but I see slow “Copying to tmp table” (tmpdir on RAM device). I tried to increase join_buffer_size but problem still the same. Can expedite the processing of such requests without changing the sql requests (unfortunately at the moment there is no possibility to optimize queries)?

    MYSQL server 5.5.34 on zfs filesystem.
    > show global status like ‘Created_tmp%’;
    +————————-+——–+
    | Variable_name | Value |
    +————————-+——–+
    | Created_tmp_disk_tables | 305388 |
    | Created_tmp_files | 7944 |
    | Created_tmp_tables | 590596 |
    +————————-+——–+
    3 rows in set (0.00 sec)

    Example:
    > SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = 28178 OR c.uid = 28178) ORDER BY last_updated DESC LIMIT 0, 25;

    rows in set (5.42 sec)

    > show profile for query 8;
    +——————————–+———-+
    | Status | Duration |
    +——————————–+———-+
    | starting | 0.000027 |
    | Waiting for query cache lock | 0.000005 |
    | checking query cache for query | 0.000114 |
    | checking permissions | 0.000006 |
    | checking permissions | 0.000003 |
    | checking permissions | 0.000003 |
    | checking permissions | 0.000007 |
    | Opening tables | 0.000035 |
    | System lock | 0.000014 |
    | Waiting for query cache lock | 0.000038 |
    | init | 0.000044 |
    | optimizing | 0.000035 |
    | statistics | 0.000122 |
    | preparing | 0.000030 |
    | Creating tmp table | 0.000039 |
    | executing | 0.000005 |
    | Copying to tmp table | 5.419415 |
    | Sorting result | 0.000056 |
    | Sending data | 0.000037 |
    | end | 0.000006 |
    | removing tmp table | 0.000050 |
    | end | 0.000006 |
    | query end | 0.000022 |
    | closing tables | 0.000020 |
    | freeing items | 0.000041 |
    | logging slow query | 0.000004 |
    | logging slow query | 0.000004 |
    | cleaning up | 0.000009 |
    +——————————–+———-+
    28 rows in set (0.00 sec)

    > explain SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = 28178 OR c.uid = 28178) ORDER BY last_updated DESC LIMIT 0, 25;
    +—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+
    | 1 | SIMPLE | n | ref | PRIMARY,status,uid,node_status_type,nid | status | 4 | const | 403897 | Using temporary; Using filesort |
    | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.n.uid | 1 | Using where |
    | 1 | SIMPLE | c | ref | lid | lid | 4 | xxxx.n.nid | 2 | Using where |
    | 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.n.nid | 1 | |
    +—-+————-+——-+——–+—————————————–+———+———+————-+——–+———————————+

    mysql config:
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    skip-external-locking
    key_buffer_size = 256M
    max_allowed_packet = 1024M
    table_open_cache = 256
    sort_buffer_size = 3M
    read_buffer_size = 3M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 512M
    query_cache_limit = 16M
    tmp_table_size = 512M
    max_heap_table_size = 512M
    max_connections = 100
    back-log = 20
    thread_cache=256
    join_buffer_size=128M
    key_buffer = 512M
    query_cache_type = 1
    skip-innodb_doublewrite
    innodb-doublewrite = FALSE
    long_query_time = 5
    slow-query-log = 0
    slow_query_log_file = /var/log/mysql/mysqld_slow_query.log
    thread_concurrency = 12
    skip-name-resolve
    server-id = 2
    auto_increment_increment=2
    auto_increment_offset=2
    log-bin=x_mysql-bin
    expire_logs_days=7
    binlog_format=mixed
    slave-compressed = 1
    relay-log=x_slave-relay-bin
    relay-log-index=x_slave-relay-bin.index
    innodb=on
    innodb_data_home_dir = /var/db/mysql/innodb
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/db/mysql/iblogs
    innodb_buffer_pool_size = 12G
    innodb_buffer_pool_instances = 9
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 1500M
    innodb_log_buffer_size = 16M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency=16
    tmpdir=/tmp
    [mysqldump]
    quick
    max_allowed_packet = 1024M
    [mysql]
    no-auto-rehash
    [mysqlhotcopy]
    interactive-timeout

    ZFS properties for /var/db/mysql/data:
    recordsize 8K
    atime off
    primarycache all

    for /var/db/mysql/innodb:
    recordsize 16K
    atime off
    primarycache metadata

    for /var/db/mysql/iblogs:
    recordsize 128K
    atime off
    primarycache all

    Thanks

  9. Stephane Combaudon says:

    Roman, a temporary table is needed because you have DISTINCT(n.nid). This has nothing to do with configuration.
    Try adding an index on (status, nid) and see if the temp table is still needed.

  10. bibi says:

    Nice one.

Speak Your Mind

*