解决MySQL磁盘（IO）占用过高问题 sync_binlog innodb_flush_log_at_trx_commit

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set, 6 warnings (0.01 sec)

mysql> set global sync_binlog=500;
Query OK, 0 rows affected (0.06 sec)

mysql> show variables like '%innodb_flush%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_flush_log_at_timeout    | 1          |
| innodb_flush_log_at_trx_commit | 1          |
| innodb_flush_method            | unbuffered |
| innodb_flush_neighbors         | 0          |
| innodb_flush_sync              | ON         |
| innodb_flushing_avg_loops      | 30         |
+--------------------------------+------------+
6 rows in set, 6 warnings (0.01 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

主要是这两个参数

• innodb_flush_log_at_trx_commit
• sync_binlog

set global sync_binlog=500;  
set global innodb_flush_log_at_trx_commit=2; 

执行这两条命令之后磁盘IO占用显著降低，至于每个参数的意义参见：

https://www.cnblogs.com/wangzhigang/p/5756398.html

https://blog.csdn.net/zimeng0/article/details/68066146/