Troubleshoot MySQL consuming IO too high

1, the performance impact of the log:
The direct performance loss due to log logging is the most expensive IO resource in the database system. MySQL logs include error log (errorlog), update log (updatelog), binary log (Binlog), query log (querylog), slow query log (slowquerylog), etc. Of course, the update log is an older version of MySQL, and is now replaced by binary logs.

By default, the system simply turns on the error log, shutting down all other logs to minimize IO loss to improve system performance. However, in practical scenarios where it is generally slightly more important, it is necessary to open the binary log at least, as this is the basis for the incremental backup of many MySQL storage engines and the basic condition for MySQL to replicate. Sometimes for further performance optimizations, locating a slower SQL statement, many systems also turn on the slow query log to record SQL statements that execute more than a certain value (set by US).

In general, there are few systems in the production system that open the query log. Since the query log is opened, each query executed in MySQL will be recorded in the log, resulting in a larger IO burden on the system, and the actual benefit is not very large. Generally only in the development test environment, in order to locate some features specific use of the SQL statements, only in a short period of time to open the log to do the corresponding analysis. So, in the MySQL system, the MySQL logs (which do not include the respective storage engine's own logs) that have an impact on performance are mostly binlog.

2. The following commands are executed in MySQL:set global sync_binlog=500; after every 500 transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk. set global innodb_flush_log_at_trx_commit=2; The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Setting to 2 means writing to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system goes down.   Note: This command fails after rebooting. The last two items can be set when the service starts.

Troubleshoot MySQL consuming IO too high