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占用显著降低，至于每个参数的意义参见：
