MySQL database server slows down analysis and solution _ MySQL

MySQL database server slows down and analyzes and solves bitsCN.com

This article analyzes the problem of slow MySQL database servers and proposes corresponding solutions.

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 ---- 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

What information is output by vmstat worth noting?

Io bo: the volume of data written to the disk is a little large. if it is a large file, you don't have to worry about writing within 10 MB. if it is a small file, writing within 2 MB is basically normal.

① CPU problems

The following columns need to be viewed to check whether the cpu is faulty.

Processesinthe run queue (procs r)

Usertime (cpu us)

System time (cpu sy)

Idle time (cpu id)

Problem:

If the number of processes in run queue (procs r) is greater than the number of CPUs in the system, the system will be slow.

If the number is 4 times that of the cpu, the system is facing a shortage of cpu capacity, which will greatly reduce the system running speed.

If the idle time of the cpu is usually 0, or the system takes up time (cpu sy) for two generations (cpu us), the system faces a lack of cpu resources.

Solution:

To solve these problems, we need to adjust the application so that it can use the cpu more effectively and increase the cpu capacity or quantity.

② Memory problems

Mainly view the page import value (si in swap). If this value is large, consider the memory. the approximate method is as follows:

The simplest way is to increase RAM

Reduce RAM requirements

3. disk IO problems

Solution: run raid10 to improve performance

4. network problems

Telnet the port opened by MySQL to the outside. if it doesn't work, check whether the firewall is correctly set. In addition, check whether the skip-networking option is enabled for MySQL. if so, disable it.

BitsCN.com