Shulou(Shulou.com)05/31 Report--

Mysql database disk io high troubleshooting, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Recently, the database will report a high disk IO alarm, but the cpu is not high.

Malfunction

● hostname: xxxx

● alarm message: Disk Iamp O is overloaded on xxxx

● alarm time: 2020.04.10-13:09:06

● host address: xxxxxx

Current status of ●: 36.14%

When the database disk io is high, the sql executed is as follows:

2527 | xxxx | 172.xxxx:35072 | xxxx | Query | 0 | update | insert ignore into `xxxannotations` (`trace_ |

| | 2528 | xxxn | 172.xxxx:37270 | xxxx | Query | 0 | update | insert ignore into `xxxannotations` (`trace_ id`, `s) |

| | 2530 | xxxx | 172.xxxx:44210 | xxxx | Query | 0 | update | insert into `xxxx_ spans` (`trace_ id`, `id`, `debug`, `start_ ts`, `name) |

| | 2531 | xxxx | 172.xxxx:45910 | xxxx | Query | 0 | query end | insert ignore into `xxxx_ annotations`4',',-1408108278, 8031) |

| | 2532 | xxx | 172.xxxx:58890 | xxxx | Sleep | 0 | | NULL |

That is, the database will execute insert ignore into statements in batches.

Mysql > show engine innodb status\ G

-TRANSACTION 1557551308, not started flushing log

-TRANSACTION 1557551309, not started flushing log

-TRANSACTION 1557551310, not started flushing log

-TRANSACTION 1557551311, not started flushing log

-TRANSACTION 1557551313, not started flushing log

-TRANSACTION 1557551304, not started flushing log

.

As you can see, every transaction is in flushing log, indicating that it is slow to swipe redo log. Maybe the redo log is relatively small.

Mysql > show variables like'% innodb_log_file_size%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_log_file_size | 50331648 | |

+-+ +

1 row in set (0.00 sec)

Facts have proved that the innodb_log_file_size is indeed relatively small, only 50m, it is recommended to increase to 2 4G.

Continue to analyze:

Mysql > show engine innodb status\ G

-

FILE I/O

-

.

Pending flushes (fsync) log: 1; buffer pool: 0

1 pending preads, 0 pending pwrites

.

LOG

-

Log sequence number 988322448590

Log flushed up to 988322444468

Pages flushed up to 988311239867

Last checkpoint at 988309561881

1 pending log writes, 0 pending chkp writes

23371463 log i/o's done, 132.33 log i/o's/second

The fsync () operation data seen above that log thread is pending is 1, indicating that log thread is waiting to flush the disk.

In addition, last checkpoint lags too much behind log flushed up to, close to the size of the redo log file, which triggers innodb to swipe redo crazily, resulting in high disk io, which has a very bad impact on performance.

Also, the innodb buffer pool of this database is also very small, using a default value of 128m, which also needs to be zoomed in.

Optimization method:

Set innodb_log_file_size=4G, set innodb_buffer_pool_size=4G.

After observation, the problem of high io and low cpu of database disk disappeared.

After reading the above, have you mastered the method of troubleshooting the io height of mysql database disk? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.