Mysql database server slows down analysis and solution sharing _mysql

First, check the status of the system

Check the state of the system through some of the operating system's tools, such as CPU, memory, swapping, disk utilization, or, depending on experience or the state of the system when it is normal, sometimes the system appears to look idle, which may not be a normal state, because the CPU may be waiting for IO to complete. In addition, you should also view the processes that occupy system resources (CPU, memory).

1. Use SAR to check operating system for IO problems

#sar-u210-that every 2 seconds, a total of 20 times.

Results Sample:

Note: Under the Redhat,%system is the so-called%wio.

LINUX2.4.21-20.ELSMP (YY075) 05/19/2005

10:36:07amcpu%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

which

%USR refers to the percentage of CPU resources used by the user process;

%sys refers to the percentage of CPU resources used by system resources;

%wio refers to the percentage of waiting for IO to be completed, which is worth noting;

%idle is the percentage of idle.

If the value of the Wio column is large, as above 35%, the system's IO has a bottleneck, and the CPU spends a lot of time waiting for I/O to complete. Idle is very small to indicate that the system CPU is busy. Like the example above, you can see that the Wio average is 11, stating that I/O is nothing special, and that the idle value is zero, indicating that the CPU is running at full capacity.

　　 2. Using Vmstat to monitor memory CPU resources

[Root@mysql1 ~]# Vmstat

Procs ——— –memory ———-— swap–-–io--–system–-–cpu--

R b swpd free buff cache si so bi bo in CS us sy ID WA St

0 0 72 25428, 54712672264 0 0 14 43 53 59 1 198 0 0

Vmstat the output of that information worth paying attention to?

Io bo: Disk to write a slightly larger amount of data, if it is a large file to write, within 10M basic need not worry, if it is small file to write 2M within the basic normal

①cpu problem

The following columns need to be viewed to determine if there is a problem with the CPU

Processesinthe Run queue (procs R)

Usertime (CPU US)

System time (CPU sy)

Idle time (CPU ID)

Problem situation:

If the number of processes in run queue (Procs R) is much larger than the number of CPUs in the system, it will slow the system down.

If this number is 4 times times the size of the CPU, the system is facing a shortage of CPU capacity, which will cause the system to run significantly slower

If the CPU idle time is often 0, or system occupancy time (CPU sy) is the user time (CPU us) Two generations, the system faced with the lack of CPU resources

Solution:

Addressing these situations involves tweaking the application so that it can use the CPU more efficiently, while increasing the capacity or quantity of the CPU

② Memory problem

The main view of the page import values (si in swap), if the value of the larger to consider memory, the approximate method is as follows:

The simplest, increase RAM

Reduce the need for RAM

3. Disk IO issues

Treatment method: Do RAID10 Improve performance

4. Network issues

Telnet to the open port of MySQL, if not, see if the firewall is set correctly. Also, check to see if MySQL is turning on the skip-networking option, and turn off if it is turned on.