innodb_flush_log_at_trx_commit参数性能的测试

innodb_flush_log_at_trx_commit = 0，Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush 操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread 将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。

innodb_flush_log_at_trx_commit = 1，这也是Innodb 的默认设置。我们每次事务的结束都会触发LogThread 将log buffer 中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提交的数据。

innodb_flush_log_at_trx_commit = 2，当我们设置为2 的时候，Log Thread 会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以，当设置为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 INTO wp_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 INTO wp_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 INTO wp_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 INTO wp_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%的差异。