我們知道，MySQL為了保證資料的盡量不丢失，一般會把參數sync_binlog 和innodb_flush_log_at_trx_commit都設定為1，即雙1原則，但是，凡是都有兩面性，高安全和高性能往往是讓人很難取舍的，需要根據你的實際業務需求進行抉擇；
1.測試背景
首先分别了解一下這兩個參數的官方解釋:
sync_binlog
Controls how often the MySQL server synchronizes the binary log to disk.
控制MySQL server 同步binlog到磁盤的頻率
sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.
關閉MySQL server binlog同步到磁盤，由OS不時的重新整理到磁盤，這是MySQL最好的性能，但是當斷電或者系統崩潰，MySQL server已經送出的事物可能還沒有同步到binlog，導緻事物丢失。
sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.
在事物送出之前同步binlog到磁盤，這是最安全的，但是由于增加了磁盤的寫操作，會有一些消極的性能影響。當斷電或者系統崩潰，從binlog中丢失的事物僅僅處于準備狀态。這個允許自動恢複程式去rollback事物，保證binlog沒有事物丢失。
sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.
在N個binlog送出組被收集到之後，binglog同步到磁盤。當斷電或者系統崩潰，已經送出的事物可能還沒有重新整理到binlog。這樣的配置增加了磁盤的寫操作，會有一些消極的性能影響。N值越高，資料庫性能越好，但是資料越可能丢失。
For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
為了使使用InnoDB的複制設定具有最大的持久性和一緻性，請使用以下設定:
sync_binlog=1.
innodb_flush_log_at_trx_commit=1.
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.
許多作業系統和一些磁盤硬體欺騙了重新整理磁盤的操作。它們可能會告訴sqmyld flush已經發生了，盡管沒有發生。在這種情況下，即使使用推薦的設定，事務的持久性也不能得到保證，在最壞的情況下，斷電可能損壞InnoDB資料。在SCSI磁盤控制器或磁盤本身中使用電池支援的磁盤緩存可以加速檔案重新整理，并使操作更加安全。您還可以嘗試禁用硬體緩存中的磁盤寫緩存。 innodb_flush_log_at_trx_commit
Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash.
控制送出操作嚴格遵守ACID和提高性能之間的平衡，這在批量重新安排和執行與送出相關的I/O操作時是可能的，通過更改預設值，您可以獲得更好的性能，但是在崩潰時，你可能會損失多達一秒鐘的事務
The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
預設值1是必須的對ACID性.InnoDB日志緩沖區的内容在每個事務送出時被寫到日志檔案，日志檔案被重新整理到磁盤。
With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not guaranteed to happen every second due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.
值為0時，InnoDB日志緩沖區的内容大約每秒寫入一次日志檔案，日志檔案被重新整理到磁盤。事務送出時不執行從日志緩沖區寫入日志檔案的操作。由于程序排程問題，不能保證每秒一次重新整理。由于對磁盤的重新整理操作大約每秒鐘隻發生一次，是以您可以在任何mysqld程序中損失最多一秒鐘的事務
With a value of 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. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.
當值為2時，InnoDB日志緩沖區的内容在每次事務送出後被寫入日志檔案，日志檔案大約每秒被重新整理一次。由于程序排程問題，每秒一次的重新整理不能100%保證每秒發生。由于磁盤重新整理操作大約每秒發生一次，是以在作業系統崩潰或斷電時，最多可能損失1秒的事務。
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). However, any mysqld process crash can erase up to N seconds of transactions.
DDL changes and other internal InnoDB activities flush the InnoDB log independent of the innodb_flush_log_at_trx_commit setting.
InnoDB crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.
For durability and consistency in a replication setup that uses InnoDB with transactions:
If binary logging is enabled, set sync_binlog=1.
Always set innodb_flush_log_at_trx_commit=1.
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches. 2.測試配置
這裡TPCC和MySQL分别在兩台機器上面，是為了排除TPCC運作過程中對MySQL服務資源的占用的影響
IP:172.16.101.54
CPU:2core
Memory:8G
軟體:tpcc-mysql-master
IP:172.16.101.55
Memory:6G
軟體:MySQL5.7.21
MySQL其他部分參數配置:
key_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
query_cache_size= 0M
query_cache_type= off
max_allowed_packet = 64M
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache = 2000
thread_cache_size = 8
wait_timeout = 300
interactive_timeout = 300
max_connections = 8000
max_user_connections=0
innodb_thread_concurrency = 4
transaction_isolation = READ-COMMITTED
gtid-mode = on
enforce-gtid-consistency = true
log_slave_updates = on
innodb_buffer_pool_size = 5120M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 100 3.測試過程
測試主要分兩種情況，通過tpcc測得MySQL server的tps和qps，每一種情況測試三遍，取平均值，并做成表格和折線圖。
innodb_flush_log_at_trx_commit = 1 && sync_binlog = 1
or
innodb_flush_log_at_trx_commit = 0 && sync_binlog = 0
4.測試結果
首先說明一下，因為手頭上隻有這樣低配置的伺服器，是以測試結果不是那麼的可靠，是以僅僅參考測試思想和過程即可，不要太在意測試結果，哈哈。
（1）TpmC
即每分鐘的事物數
| 線程數 | 4 | 8 | 16 | 32 | 64 | 128 | 256 | 512 | 1024 | 1536 | 2048 |
| i=1&s=1 | 864.433 | 1411.367 | 2020.667 | 2465.867 | 2666.533 | 2666.633 | 2614.333 | 2431.267 | 2011.233 | 1507.733 | 1266.1 |
| i=0&s=0 | 2761.383 | 3160.05 | 2835.817 | 2510.683 | 2366.017 | 2303.25 | 2367.517 | 2251.817 | 1834.583 | 1439.684 | 1166.3 |
從圖中我們可以看到：
- 并發0-32的時候，雙0的MySQL TpmC遠遠大于雙1;
- 并發>32後，兩者之間的TpmC數量差不多
（2）雙1情況下的 slow queries，open tables，Queries per second avg等名額的情況
- 随着并發越來越大，slow queries也越來越多；
- 當并發達到512左右，open tables也達到參數table_open_cache = 2000的設定值；
- 随着并發越來越大，QPS越來越小，剛開始并不明顯，一度處于平衡，知道并發太大，QPS性能下降；
參考連結：
《MySQL壓測--TPCC安裝，測試》 http://blog.51cto.com/darrenmemos/2130202