MySQL optimization (V)

I am going to reopen the last article that I wrote about MySQL optimization because I came across three new parameters (for me) which enhance the performance of a MySQL database, and I would like to note down them on my blog.

The first parameter is innodb-flush-log-at-trx-commit, which manages both when the log buffer is written out to the log file and the flush to disk operation is performed. Its default value is 1, which means that the log buffer is dumped to the log file at each transaction commit and the flush to disk operation is carried out directly on the log file.

When its value is 0, the log buffer is sent to the log file once per second, so in this way, you are turning down the disk accesses. In respect of the flush to disk, the operation is also effected on the log file but not coinciding with the commit, but taking advantage of free periods. And when it takes the value of 2 (less aggressive than 0), the log buffer is written out to the log file at each commit but as in the previous case, the flush is done at any free moment for the server.

The other parameter that I would like to talk about is innodb_buffer_pool_instances (in case of you are using the InnoDB engine), which represents the number of regions that the InnoDB buffer is broken up.  This parameter is really useful when you are using a server with several cores, and thereby, each core (thread) can work on a separate instance. A good recommendation is to set it to the same value as the number of cores, but another popular option is to follow the next rule: (innodb_buffer_pool_size [in GB] + number of cores) / 2.

And finally, the last parameter is innodb_log_file_size, related to the InnoDB log file. Its default value is 5 MB and I consider that is not enough for production environments. The larger the value is, the less control flush activity is needed in the buffer pool, saving disk I/O operations. I think that a right value would be between 64 and 256 MB.