Tuning Mysql 8.0 server for performance

Mysql server comes in default configuration which is too general and not optimized for specific server. If you don’t tune Mysql, you don’t get efficient server resources utilization. By tuning Mysql you can gain 10x…100x improvements in performance of the database server.

Use latest Mysql version

Man, just upgrade your server and don’t argue. Latest version brings (among other benefits) performance optimizations with no efforts (don’t say that to your devops, because he’s afraid to upgrade anything).

InnoDB vs MyISAM

Long story short, don’t use Myisam, use InnoDB. Smart people say, that you could use MyISAM when you have low write volumes and high read volumes. But shit, that’s hard to control and even harder to plan. And the most important — it’s not only about performance and saved disk space.

Why InnoDB is better?

First of all, InnoDB is future and MyISAM is past. There are tens of benefits of InnoDB vs MyISAM, but what is important from performance perpective — is row-level lock and clustered indexes.

Row-level vs table-level locks

While MyISAM will lock full table for each write, InnoDB will lock only corresponding row (or rows):

This leads to huge performance difference in case of large amount of simultaneous write operations. As you can see, MyISAM will just line-up all clients into long waiting queue, while InnoDB will process as much clients in parallel as possible (as long as they access different rows). This creates signigicat performance boost when you have combined read/write environment (99.99% of cases in our hard lives).

Clustered indexes

InnoDB stores row data “near” its primary key (that’s why they’re called clustered). This approach saves time to retrieve row data found by primary key:

Mysql server will need single operation to find row by primary key and get full row data. If no primary key is defined for the InnoDB table (please don’t do so), Mysql will try to do its best and either use unique index or virtual column as a primary key.

Tuning InnoDB configuration

Tweaking multiple InnoDB options in my.conf file (Mysql server configuration file) is a must have to improve Mysql server performance:

nano /etc/mysql/my.cnf

Increase buffer pool

Buffer Pool is basically memory area where InnoDB stores tables data and index cache.

Increasing buffer pool is always a good step to start tuning from. Set buffer pool size to 80% of available RAM on your server (e.g. on a 16G RAM server):

innodb_buffer_pool_size = 12G

Be ready to wait a bit when you’re restarting Mysql server with large buffer pool and a lot of data in the database itself. But man, do you restart your production Mysql server frequenty?

Increase log files and log buffers

When data is changes in InnoDB table, Mysql will write changes to buffer pool first. After that it will log change operations (in a low-level format) to a log file (so called “redo log”):

If those log files are small, Mysql have to make a lot of writes to disk directly to data files. So set log files large (as big as buffer pool, if possible):

innodb_log_file_size = 12G

Warning. When you change innodb_log_file_size option you have to remove old log files when restarting server:

service mysql stop
rm /var/lib/mysql/ib_logfile*
service mysql start

Large transactions will force Mysql writting a lot of data into log files. In order to save resources, Mysql will first write data to log buffers:

If log buffer is smaller than the transaction impact, Mysql will have to wait till it writes everything to disk. Increase log buffers if you have queries that insert/update/delete many rows at once:

innodb_log_buffer_size = 128M

Change data flush periods and methods

Mysqly has to write data to disk once in a while. This is called flushing data to disk. First thing to change — is how often Mysql flushes data, which is controlled by innodb_flush_log_at_trx_commit option:

As you can see, default value of 1 will flush data to disk after each transaction. Setting innodb_flush_log_at_trx_commit to 0 will ask Mysql to flush data to disk every second. This will improve write performance dramatically. But be careful. This can lead to up to 1 second of lost transactions on system crashes. So absolutely replicate your Mysql server for reliability before changing this option:

innodb_flush_log_at_trx_commit = 0

Not only period of flushing, but the method of flushing will affect Mysql performance. Among multiple methods there are couple we should mention:

As we can see using O_DSYNC option will skip double-caching when writing data to disk:

innodb_flush_method = O_DSYNC

Summary

Keeping an eye on these options is a must when tuning Mysql performance:

innodb_buffer_pool_size = 12G        # set to 80% of RAM
innodb_log_file_size = 12G           # same as buffer pool
innodb_log_buffer_size = 128M        # increase for large
                                       transactions
innodb_flush_log_at_trx_commit = 0   # will increase write speed 
                                       but be sure 
                                       to backup/replicate 
                                       for reliability
innodb_flush_method = O_DSYNC        # avoid double-caching
                                       when writing to disk

Tune your Mysql or Mysql will feel offended and be slow!