摘要：
当设置为“1”时，它是性能损失最大的最安全设置。因为当它设置为1时，即使系统崩溃，binlog也最多会丢失t_当缓存中未完成的事务设置为0时，这意味着事务提交后MySQL不会执行fs
一、现象
[root@push-5-221 ~]# iotop otal DISK READ: 0.00 B/s | Total DISK WRITE: 6.26 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 795 be/3 root 0.00 B/s 0.00 B/s 0.00 % 95.80 % [jbd2/dm-0-8] 22952 be/4 rabbitmq 0.00 B/s 4.04 M/s 0.00 % 50.10 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 8597 be/4 mysql 0.00 B/s 90.05 K/s 0.00 % 16.08 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 22960 be/4 rabbitmq 0.00 B/s 52.53 K/s 0.00 % 13.40 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22955 be/4 rabbitmq 0.00 B/s 105.06 K/s 0.00 % 11.76 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22948 be/4 rabbitmq 0.00 B/s 41.27 K/s 0.00 % 10.10 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22947 be/4 rabbitmq 0.00 B/s 48.78 K/s 0.00 % 7.44 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22921 be/4 rabbitmq 0.00 B/s 48.78 K/s 0.00 % 6.55 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22946 be/4 rabbitmq 0.00 B/s 45.03 K/s 0.00 % 6.30 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 23039 be/4 rabbitmq 0.00 B/s 26.27 K/s 0.00 % 6.07 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 23002 be/4 rabbitmq 0.00 B/s 45.03 K/s 0.00 % 6.02 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 22964 be/4 rabbitmq 0.00 B/s 48.78 K/s 0.00 % 4.67 % beam.smp -W w -A 128 -P 10~inet_dist_listen_max 25672 11655 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 2.77 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 30533 be/4 mysql 0.00 B/s 7.50 K/s 0.00 % 2.72 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 4458 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 1.87 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 6629 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 1.66 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 6116 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 1.62 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 19645 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 1.44 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 25633 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 1.22 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 8603 be/4 mysql 0.00 B/s 7.50 K/s 0.00 % 0.89 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 11659 be/4 mysql 0.00 B/s 180.11 K/s 0.00 % 0.43 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 22803 be/4 mysql 0.00 B/s 11.26 K/s 0.00 % 0.36 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 4470 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 0.03 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock 8601 be/4 mysql 0.00 B/s 3.75 K/s 0.00 % 0.02 % mysqld --basedir=/usr --da~=/var/lib/mysql/mysql.sock
发现[jbd2/dm-0-8]这个进程占用IO95%。
二、解决
进入mysql ，查看sync_binlog变量设置
[root@hlsms-fensheng-4 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2118462 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like '%sync_binlog%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
当前值为1，表示每次提交事务后，将binlog_cache中的数据强制写入磁盘。当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务
当设置为0时，表示当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
所以sync_binlog=1，导致事务写入太频繁，从而出现[jbd2/dm-0-8]这个进程占用IO95%。
因此将sync_log设置为500
mysql> set global sync_binlog=500; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%sync_binlog%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 500 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
三、设置innodb_flush_log_at_trx_commit变量
innodb_flush_log_at_trx_commit是配置MySql日志何时写入硬盘的参数：
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时mysql都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作
一般设置为2
mysql> show variables like '%innodb_flush_log_at_trx_commit%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.01 sec) mysql> set global innodb_flush_log_at_trx_commit=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%innodb_flush_log_at_trx_commit%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 2 | +--------------------------------+-------+ 1 row in set (0.01 sec)
四、验证
再次查看iotop
Total DISK READ: 0.00 B/s | Total DISK WRITE: 781.51 K/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 1458 be/3 root 0.00 B/s 0.00 B/s 0.00 % 6.97 % [jbd2/dm-2-8] 26496 be/4 mysql 0.00 B/s 14.47 K/s 0.00 % 4.02 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 26500 be/4 mysql 0.00 B/s 665.73 K/s 0.00 % 2.42 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 26498 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 1.52 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 26505 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.17 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 26499 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 14668 be/4 mysql 0.00 B/s 21.71 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock 16970 be/4 mysql 0.00 B/s 18.09 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=~ocket=/home/data/mysql/mysql.sock
[jbd2/dm-2-8]明显降低