mysql磁盘写io高_转：mysql占用磁盘IO过高的解决办法

一、现象

最近发现Mysql服务器磁盘IO一直很高

[root@push-5-221 ~]# iostat -k -d -x 1 10

Linux 3.10.0-957.el7.x86_64 (push-5-221) 2019年07月05日 _x86_64_ (8 CPU)

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util

sda 0.00 0.08 0.00 14.89 0.09 177.79 23.88 0.34 23.05 5.42 23.05 11.01 16.40

dm-0 0.00 0.00 0.00 0.18 0.09 3.62 39.49 0.07 380.21 5.59 387.46 12.69 0.24

dm-1 0.00 0.00 0.00 0.00 0.00 0.01 8.98 0.00 91.25 12.04 92.94 6.55 0.00

dm-2 0.00 0.00 0.00 14.79 0.00 174.15 23.56 0.33 22.27 8.84 22.27 10.96 16.20

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util

sda 0.00 0.00 0.00 83.00 0.00 650.50 15.67 2.55 30.66 0.00 30.66 12.02 99.80

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 83.00 0.00 636.50 15.34 2.55 30.66 0.00 30.66 12.02 99.80

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util

sda 0.00 0.00 0.00 88.00 0.00 684.50 15.56 2.60 29.42 0.00 29.42 11.35 99.90

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 87.00 0.00 677.00 15.56 2.60 29.76 0.00 29.76 11.49 100.00

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util

sda 0.00 0.00 0.00 87.00 0.00 687.50 15.80 2.48 28.77 0.00 28.77 11.51 100.10

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 88.00 0.00 699.00 15.89 2.48 28.48 0.00 28.48 11.36 100.00

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util

sda 0.00 0.00 0.00 88.00 0.00 756.50 17.19 2.48 28.22 0.00 28.22 11.35 99.90

dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

dm-2 0.00 0.00 0.00 86.00 0.00 740.00 17.21 2.48 28.87 0.00 28.87 11.62 99.90

%util已经接近100%了，IO性能严重瓶颈，这台服务器有两个Mysql实例，3306是主库，3307是另一台Mysql的从库，进入从库查看从库状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.28.5.223

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-223.000012

Read_Master_Log_Pos: 511363808

Relay_Log_File: push-5-221-relay-bin.000034

Relay_Log_Pos: 382425277

Relay_Master_Log_File: master-223.000012

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 382425062

Relay_Log_Space: 511364283

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 2345

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2233306

Master_UUID: 165a203c-8021-11e9-bc40-6c2b5992e592

Master_Info_File: /home/mysql-5.7.26/data/3307/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Reading event from the relay log

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: