Wednesday, October 19, 2016

InnoDB innodb_flush_log_at_trx_commit

 innodb_flush_log_at_trx_commit=0 or 1 or 2

With trx=0, the contents of the InnoDB log buffer are written to the log file and flushed to disk approximately once per second. However, flushing to dick once-per-second is not 100% guaranteed to happen every second, due to process scheduling issues [1].

With trx= 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. 

So the difference really between the two is how often they write the contents of the innodb buffer pool to log files - trx=0 is once every second, and trx=2 is after each commit. Frequency regarding flushing to disk is somewhat the same, which is once per second in normal circumstances without any process scheduling issues.

About the variable innodb_flush_log_at_timeout, the value of N means write and flush the logs every N seconds with MySQL 5.6.6 and later versions. So when innodb_flush_at_timeout plays a part, the master thread will always try to write/flush logs every innodb_flush_log_at_timeout seconds, no matter how you set innodb_flush_log_at_trx_commit. So the "InnoDB log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1) ". So in case of a crash, you can lose up to N seconds of transactions. But this should not be a problem in PXC since replication based on Galera is asynchronous among master nodes. The only problem with losing data in a Galera setup would come if the whole cluster crashed at once. So if the value of N is large, say 10, then a crashing cluster would cost data lose of upto 10 seconds. 

Benchmark:


I used Sysbench for benchmarking with Updated_key param to see how changing the innodb_flush_log_at_trx_commit and innodb_flush_log_at_timeout values would impact the cluster server’s performance overall. The Sysbench query used was for read/write over 10 tables each with 1000,000 records. Each benchmark run was configured to run for 60 seconds for different threads. 

The tests were done on a 3 node Percona Xtradb Cluster with a single slave. Sys bench benchmarks were run via HAProxy which provided access to the cluster using roundrobin balancing. 

Sysbench OLTP RW


sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-host=10.1.5.102 --mysql-port=3306 --mysql-table-engine=InnoDB --mysql-engine-trx=yes --oltp-auto-inc=off --oltp-tables-count=10 --oltp-table-size=1000000 --mysql-db=sbtest --mysql-user=kkuser --mysql-password=kkPass --max-time=60 --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=update_key --oltp-read-only=off --num-threads=N run




OLTP: RW chart for IO bound scenario that includes results for innodb_flush_log_at_trx_commit={0|2} and innodb_flush_log_at_timeout={0|2|10}. The settings deal with commit-related I/O operations, and our goal was to find out how changing these parameters affect the performance of the database system. 

Performing the benchmark was not easy, and this is the best approach i could think of. The network also did not seem to be very stable and sometimes results using the same sybench query would vary significantly. To help with clear MySQL caching (of course OS caching would still be there), i had to restart the cluster before each run.

With the above findings, i would advise that we leave innodb_flush_log_at_trx_commit at 2 is currently is. The difference is not decisive enough to make the change. 


REFERENCES

[1]http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_timeout

No comments:

Post a Comment