MySQL压测--参数压测

zhoujun.g

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.