MySQL consumes a lot of write I/O

In the morning to receive the Zabbix alarm, found that a monitoring data stored in the database host CPU Iowait High, has lasted for a long time.

650) this.width=650; "Style=" background-image:none; Display:inline "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/8E/5B/ Wkiom1i-w9rjojg7aagr09lftga628.png "" 1028 "height=" 258 "/>

Logon server view disk IO Discovery queue up to 90%, and often repeatedly

650) this.width=650; "Style=" background-image:none; Display:inline "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/8E/5B/ Wkiom1i-w-3buox4aacfqvjfbnw098.png "" 1028 "height=" 811 "/>

By Iotop View found that the process of consuming IO is MySQL

650) this.width=650; "Style=" background-image:none; Display:inline "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/8E/59/ Wkiol1i-w-6zksjwaae24wurii4413.png "" 1028 "height="/>

Log in to MySQL view show processlist, found that basically each time the IO queue is inserted at the time of the insert, thinking that there is a problem with the insertion statement, so open the MySQL slow query log, to observe a period of time disk IO is still high, but found that there is no slow query statements;

Find out about MySQL IO problem optimization data, "reprint" Sync_binlog and innodb_flush_log_at_trx_commit the analysis of Sync_binlog and Innodb_flush_log_at_trx_ Commit two parameters, which are key parameters for controlling MySQL disk write policy and data security

650) this.width=650; "Style=" background-image:none; Display:inline "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/8E/5B/ Wkiom1i-xdnxpcslaapeptkkqpw263.png "" 844 "height=" 539 "/>

Since this host is the monitoring service database, there is not much optimization at the beginning of the deployment, and the configuration remains the default, so that each transaction commits a write operation, which causes the write operation to be too frequent.

Then modify the Sync_binlog and Innodb_flush_log_at_trx_commit parameters

set global sync_binlog=;   set global innodb_flush_log_at_trx_commit=2;

After modifying the two parameters of Sync_binlog and Innodb_flush_log_at_trx_commit, observe a period of time to find the CPU iowait significantly reduced

650) this.width=650; "Style=" background-image:none; Display:inline "title=" image "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/8E/5B/ Wkiom1i-xdqxfh9gaae75tn-mvy901.png "" 1028 "height=" 258 "/>

MySQL consumes a lot of write I/O