innodb_flush_log_at_trx_commit = 1,这也是Innodb的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置,能够保证不论是MySQL Crash 还是OS Crash或者是主机断电都不会丢失任何已经提交的数据。
innodb_flush_log_at_trx_commit = 2,当我们设置为2 的时候,Log Thread会在我们每次事务结束的时候将数据写入事务日志,但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的,所以LogThread的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件LogThread 就完全不知道了。所以,当设置为2 的时候,MySQL Crash 并不会造成数据的丢失,但是OS Crash或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样,大家可以自行参阅相关的手册。
根据上面三种参数值的说明,0的时候,如果mysql crash可能会丢失数据,可靠性不高。我们着重测试1和2两种情况。
操作:从一个客户端执行sql语句到数据库服务器
主机:服务器,硬盘RAID 1
innodb_flush_log_at_trx_commit:1
线程数:20
插入记录数:20W
执行语句:/usr/local/mysql/bin/mysqlslap --query="INSERT INTOwp_home(post_id,cat_id,post_title,score,TYPE) VALUES(2093329,4295,'abcdfdadcvda32daaabc',1,0);"--number-of-queries=200000 --concurrency=20 --port=3308--host="10.0.0.100" --user="abc" --password="abc"--create-schema="test"
Benchmark
Average number of seconds to run all queries: 16.386 seconds
Minimum number of seconds to run all queries: 16.386 seconds
Maximum number of seconds to run all queries: 16.386 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 17.508 seconds
Minimum number of seconds to run all queries: 17.508 seconds
Maximum number of seconds to run all queries: 17.508 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 17.042 seconds
Minimum number of seconds to run all queries: 17.042 seconds
Maximum number of seconds to run all queries: 17.042 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
操作:从一个客户端执行sql语句到数据库服务器
主机:服务器,硬盘RAID 1
innodb_flush_log_at_trx_commit:2
线程数:20
插入记录数:20W
执行语句:/usr/local/mysql/bin/mysqlslap --query="INSERT INTOwp_home(post_id,cat_id,post_title,score,TYPE) VALUES(2093329,4295,'abcdfdadcvda32daaabc',1,0);"--number-of-queries=200000 --concurrency=20 --port=3308--host="10.0.0.100" --user="abc" --password="abc"--create-schema="test"
Benchmark
Average number of seconds to run all queries: 15.566 seconds
Minimum number of seconds to run all queries: 15.566 seconds
Maximum number of seconds to run all queries: 15.566 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 12.886 seconds
Minimum number of seconds to run all queries: 12.886 seconds
Maximum number of seconds to run all queries: 12.886 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 14.502 seconds
Minimum number of seconds to run all queries: 14.502 seconds
Maximum number of seconds to run all queries: 14.502 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
操作:数据库服务器端操作
主机:普通pc,普通硬盘
innodb_flush_log_at_trx_commit:1
线程数:20
插入记录数:20W
执行语句:/usr/local/mysql/bin/mysqlslap --query="INSERT INTOwp_home(post_id,cat_id,post_title,score,TYPE) VALUES(2093329,4295,'abcdfdadcvda32daaabc',1,0);"--number-of-queries=200000 --concurrency=20 --port=3307--host="10.0.0.28" --user="abc" --password="abc"--create-schema="test"
Benchmark
Average number of seconds to run all queries: 183.998 seconds
Minimum number of seconds to run all queries: 183.998 seconds
Maximum number of seconds to run all queries: 183.998 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 176.250 seconds
Minimum number of seconds to run all queries: 176.250 seconds
Maximum number of seconds to run all queries: 176.250 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 182.423 seconds
Minimum number of seconds to run all queries: 182.423 seconds
Maximum number of seconds to run all queries: 182.423 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
操作:数据库服务器端操作
主机:普通pc,普通硬盘
innodb_flush_log_at_trx_commit:2
线程数:20
插入记录数:20W
执行语句:usr/local/mysql/bin/mysqlslap --query="INSERT INTOwp_home(post_id,cat_id,post_title,score,TYPE) VALUES(2093329,4295,'abcdfdadcvda32daaabc',1,0);"--number-of-queries=200000 --concurrency=20 --port=3307--host="10.0.0.28" --user="abc" --password="abc"--create-schema="test"
Benchmark
Average number of seconds to run all queries: 20.191 seconds
Minimum number of seconds to run all queries: 20.191 seconds
Maximum number of seconds to run all queries: 20.191 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 25.374 seconds
Minimum number of seconds to run all queries: 25.374 seconds
Maximum number of seconds to run all queries: 25.374 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
Benchmark
Average number of seconds to run all queries: 19.065 seconds
Minimum number of seconds to run all queries: 19.065 seconds
Maximum number of seconds to run all queries: 19.065 seconds
Number of clients running queries: 20
Average number of queries per client: 10000
从测试结果看出:
1:普通硬盘情况下,innodb_flush_log_at_trx_commit参数分别是1和2的情况,两者差别巨大,差异几乎到了9倍。
2:在做了raid的情况下,innodb_flush_log_at_trx_commit参数分别是1和2的情况,两者有10%左右的差异。
3:硬盘的IO性能对性能的影响是巨大的。
因为我的测试环境是在公司的测试服务器上面测试的,有时候测试会有一些误差,今天我又在raid服务器机器上测试了一遍,两者性能大约有20%-30%的差异。