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 - U 2 10 -Check every two seconds for a total of 20 times.
Result example:
Note: in RedHat, % system is the so-called % wio.
Linux 2.4 . 21 - 20 . ELsmp (YY075) 05 / 19 / 2005
10 : 36 : 07 AM CPU % User % Nice % System % Idle
10 : 36 : 09 AM All 0.00 0.00 0.13 99.87
10 : 36 : 11 AM All 0.00 0.00 0.00 100.00
10 : 36 : 13 AM All 0.25 0.00 0.25 99.49
10 : 36 : 15 AM All 0.13 0.00 0.13 99.75
10 : 36 : 17 AM All 0.00 0.00 0.00 100.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 54712 672264 0 0 14 43 53 59 1 1 98 0 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.
Processes In The run queue (procs r)
User Time (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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- Next Page
|
[Content navigation] |
Page 1: Check the system status |
Page 1: Check MySQL Parameters |
Page 1: Check mysql Status values |
Page 7: Install the sysstat system status package |
Page 5: five examples of SAR commands |
Page 6: Six output parts of the vmstat command |
Page 7: adjust system parameters based on mysql status |
Page 7: adjust system parameters based on mysql status |