Phase two: mysql write-related variables

Having covered the basis about buffer pool and other read-related variables, we can now move our attention to write-related configuration parameters. The configuration directive innodb_flush_log_at_trx_commit controls mysql behavior when dealing with writes. Its default values, 1, provide you a very safe configuration, where each commit is guaranteed to be flushed to physical disk platters (note: this is true only if your operating system issue the corresponding write barrier. However, this is an OS-related issue, not a mysql one). While this is obviously the safest choice, it is the slower one also.

Other possible values for this variable are 0 and 2. When set to 0, mysql wait a full second before write anything to the log file and flushing it to disk. This means that, for a full second, data modifications are not notified to the underlying operating system, rather they are stored into an application-specific write cache. Only after the timer expires these modifications are written out to the relative files and an os-and-disk-cache-flush command is performed. While this setting can led to greater performance, a mysql crash can results in data loss. Note that when I state “mysql crash”, I really mean a mysqld server process crash – not an operating system crash.

If set to 2, mysql continue to wait a full second before flushing the os and disks caches, but it write modifications at each commit. This is confusing, it isn't? The point to understand is that if innodb_flush_log_at_trx_commit is set to 2, mysql write out any modifications immediately at transaction commit, but it does not immediately force a full cache-flush command. This means that a data loss can incur only with a full server crash (os-level panic, for example) or power outage. A “simple” mysqld crash can not give you data loss, because the modified data were already passed to os-level pagecache. If you are short on write performance and you can tolerate an one-second data loss in the rare case of a full server crash, this can be a reasonable tradeoff (but remember to use a good UPS!).

For this reason, while I didn't run any test with innodb_flush_log_at_trx_commit set 0, I both use values of 1 and 2.

Ok, let see the effects of innodb_flush_log_at_trx_commit and innodb_flush_method on DBT-2 workload:

mysql performance tuning - dbt2 - phase2

On the bottom, we have the scores obtained with default config and with innodb_buffer_pool_size set to 2 GB. Remember that, in this state, trx_commit has its default value of 1. Starting from here, you can see that using O_DSYNC significantly lowers the results, while O_DIRECT is equivalent to the default fdatasync() flush method (in this setup at least; reading mysql's documentation, it seems that O_DIRECT can impair database performance when using network-attached storage). Setting trx_commit to 2 slightly improve our transaction speed, but by less then 10%.

Sysbench simpler test paint a different picture:

mysql performance tuning - sysbench simple - phase2

Being a read-only test, it is not affected at all by trx_commit. On the other hand, O_DSYNC continue to hamper database performance.

Will the complex, transactional test give us different results?

mysql performance tuning - sysbench complex - phase2

Yes, absolutely. Here trx_commit = 2 led to over 40% speedup versus the default setting (1). O_DSYNC continue to kill write speed, while O_DIRECT show us no differences. After all, it seems that mysql's team chose fdatasync() for a good reason.