MySQL database server slows down analysis and solutions _ MySQL

MySQL database server slows down analysis and solution sharing bitsCN.com 1. check the system status

Check the status of the system by using some operating system tools, such as CPU, memory, swap, and disk utilization. based on experience or compared with the normal status of the system, sometimes the system looks idle on the surface, which may not be a normal status, because the cpu may be waiting for the completion of IO. In addition, you should also observe the processes that occupy system resources (cpu and memory.

1. use sar to check whether the operating system has IO problems

# Sar-u210-that is, the inspection once every 2 seconds, a total of 20 times.

Result example:

Note: in redhat, % system is the so-called % wio.

Linux2.4.21-20. ELsmp (YY075) 05/19/2005

10: 36: 07 AMCPU % user % nice % system % idle

10: 36: 09AMall0. 000.000.1399.87

10: 36: 11AMall0. 000.000.00100.00

10: 36: 13AMall0. 250.000.2599.49

10: 36: 15AMall0. 130.000.1399.75

10: 36: 17AMall0. 000.000.00100.00

Where:

% Usr indicates the percentage of cpu resources used by the user process;

% Sys indicates the percentage of cpu resources used by system resources;

% Wio refers to the percentage waiting for io completion, which is worth noting;

% Idle indicates the percentage of idle resources.

If the value of the wio column is large, for example, more than 35%, it indicates that there is a bottleneck in the system IO, and the CPU spends a lot of time waiting for the completion of I/O. If the Idle is small, the CPU usage is very busy. As shown in the preceding example, the average wio value is 11, indicating that I/O has no special problems, and the idle value is zero, indicating that the cpu is fully loaded.

　　 2. use vmstat to monitor memory cpu resources

[Root @ mysql1 ~] # Vmstat

Procs ―――