Troubleshooting for high MySQL database load

What are the possible causes of high load on MySQL databases? How to troubleshoot? This article will introduce the troubleshooting methods when the MySQL database load is high. Let's take a look at this process.

The Troubleshooting logic is as follows:

1: whether the memory occupies a large number of swap partitions.

2: I/O pressure is not high, it is best to paste the top display information.

3: whether the database engine is MyISAM or InnoDB.

4: In case of I/O pressure, the BINLOG analysis write frequency is reversed.

5: if it is not the I/O pressure, the sampling query request analysis query frequency.

Show status; view the specific status value, and analyze the status value to find out the cause and solve the problem.

Use nmon to monitor disk I/O and use LoadRunner for a period of time. If there is no slow Query within 1 second, it does not mean that the index is OK. To solve the index problem, the load should be much lower.

Iostat-DX 1: Observe the disk read/write status. If there are more than 10 MB of read/write data, pay attention to it.

Vmstat 1 check the CPU idle. If the idle is very low, it may be that MySQL statements consume a lot of CPU.

Top: Check whether there are processes with high losses.

Summary:

1. First, use the most basic TOP command to check where the CPU is busy. Is it because of iowait or USR usage, or is it consumed by sys.

2. Use new monitoring tools such as vmstat and dstat based on the problem to check which equipment is the busiest in the system.

3. Continue to solve the problem.

This article introduces how to troubleshoot the high load of MySQL databases. If you want to learn more about MySQL databases, you can refer to the article http://database.51cto.com/mysql/, which will surely bring you a harvest!