MySQL database server slowly slows down analysis

The first step is to check the status of the system

1.1 Using SAR to check the operating system for IO problems

#sar-U 2 10-that is, every 2 seconds, the total execution 20 times.

[Email protected] tester]# sar-u2 TenTen:Wuyi: AAM CPU%user%nice%system%iowait%steal%IdleTen:Wuyi: -AM All13.74      0.00      5.60     42.49      0.00     38.17Ten:Wuyi: -AM All20.15      0.00     11.99     43.37      0.00     24.49Ten:Wuyi: -AM All14.07      0.00      9.72     56.78      0.00     19.44Ten:Wuyi: -AM All37.24      0.00     11.99     30.61      0.00     20.15Ten:Wuyi: +AM All33.25      0.00     10.23     44.50      0.00     12.02Ten:Wuyi: theAM All22.76      0.00     12.28     49.10      0.00     15.86Ten:Wuyi: $AM All17.97      0.00      8.10     43.29      0.00     30.63Ten:Wuyi: -AM All19.44      0.00      9.21     53.20      0.00     18.16Ten:Wuyi: +AM All26.85      0.00     12.53     38.36      0.00     22.25Ten:Wuyi: theAM All22.25      0.00      9.21     42.46      0.00     26.09Average:all22.77      0.00     10.08     44.41      0.00     22.74

which
%USR refers to the percentage of CPU resources that are used by the user process;
%sys refers to the percentage of CPU resources that are used by system resources;
%wio refers to the percentage of waiting for IO to be completed, which is a worthwhile observation;
%idle is the percentage of idle.
If the value of the Wio column is large, as above 35% indicates that the system has a bottleneck in Io, the CPU spends a significant amount of time waiting for I/O to complete. Idle is a small indication that the system CPU is busy. As in the example above, you can see that the Wio average is 11, indicating that I/O has no particular problem, and that the idle value is zero, indicating that the CPU is running at full capacity.

1.2 Using VMSTAT to monitor memory CPU resources

# Vmstat

[email protected] tester]# Vmstatprocs-----------Memory-------------Swap-------io------System-------CPU-----r b swpd free buff cache si so bi boinchCS US sy ID WA St0  5      0 2654300 181588 2070476    0    0     0     to    4    0  0  0  About  0  0

Vmstat the output of those messages deserves attention?
Io bo: Disk write a slightly larger amount of data, if it is a large file write, 10M within the basic need not worry, if it is small files write 2M or less basic normal

1.3 Using Iostat to view IO Read and write situations

#iostat-X-k-d 1 10

-D sampling time is 1 seconds, sampled 2 times

-X Displays more detailed IO device statistics

-K displays read and write information in kilobytes

Linux # Iostat-x-k-d1 TenLinux2.6.16.60-0.21-SMP (Linux) ./ -/ A... DEVICE:RRQM/s wrqm/s r/s w/s rkb/s wkb/s avgrq-sz Avgqu-szawaitSvctm%UTILSDA0.00  9915.00    1.00   90.00     4.00 34360.00   755.25    11.79  120.57   6.33  57.60

The above columns have the following meanings:

• rrqm/s: The number of times per second read requests to the device are merged, and the file system merges requests to read the same block
• wrqm/s: Number of times per second write requests to the device are merged
• r/s: Number of reads completed per second
• w/s: Number of writes completed per second
• rkb/s: Amount of Read data per second (KB)
• wkb/s: Amount of Write data per second (in kilobytes)
• Avgrq-sz: The average amount of data per IO operation (units of sectors)
• Avgqu-sz: Average queue Length of IO requests waiting to be processed
• await: Average per IO request wait time (including wait time and processing time, in milliseconds)
• SVCTM: Average processing time per IO request (in milliseconds)
• %util: The time ratio used for IO operations in cycles, that is, the time ratio of IO queue non-empty

For the example output above, we can obtain the following information:

1. Write about 30M data (wkb/s value) to disk per second
2. 91 IO operations per second (R/S+W/S), with write as the principal
3. Average per IO request waits 120.57 milliseconds, processing time is 6.33 milliseconds
4. In the queue of IO requests waiting to be processed, there are an average of 11.79 requests residing

1.4 Network problems

Telnet to the open port of MySQL, if not, see if the firewall is set up correctly.

See if MySQL turns on the skip-networking option, turn it off if you turn it on.

Second step to check MySQL parameters

2.1.1 Max_connect_errors

The default value of Max_connect_errors is 10, if the number of trusted account errors reached 10 is automatically blocked, need to flush hosts to release. If you get a mistake like this:
Host ' hostname ' is blocked because of many connection errors.
Unblock with ' mysqladmin flush-hosts '
This means that mysqld has been given a large number (max_connect_errors) of host ' hostname ' in the middle of a connection request that was interrupted. After max_connect_errors a failed request, MYSQLD identified an error (like a hacker attack) and blocked further connections to the site until someone executed the command mysqladmin flush-hosts.
Intranet connection, it is recommended to set above 10000, has avoided clogging, and regularly flush the hosts.

2.1.2 Connect_timeout

Specifies the maximum number of seconds that the MySQL service waits to answer a connection message, beyond which MySQL returns bad handshake to the client. The default value is 5 seconds, which is recommended for 10-15 seconds in an intranet high concurrency environment to avoid bad hand shake. It is recommended that you pay attention to thread_cache_size and set Thread_cache_size to a value other than 0, which is size specific.

2.1.3 Skip-name-resolve
Skip-name-resolve can greatly speed up the user's access to the connection, especially in the case of poor network conditions. When a connection request is received, MySQL reverses the requestor's hostname based on the IP obtained in the request packet. The IP is then retrieved again based on the host name returned. If the IP is the same two times, then the connection is successfully established. In the case of DNS instability or too many hosts in the LAN, a successful connection can take a lot of unnecessary time. If the IP address of the MySQL server is WAN, it is best not to set skip-name-resolve.

2.1.4 Slave-net-timeout=seconds
Parameter meaning: When slave reads log data from the primary database, it waits for how long to reestablish the connection and obtain the data. The default value is 3,600 seconds, if you need to ensure synchronization, so the NC parameters, please try to control under 10 seconds.

2.1.5 Master-connect-retry
Parameter meaning: When the master-slave connection is re-established, if the connection establishment fails, how long after the interval is retried. The default is 60 seconds, please set the parameters according to reasonable conditions.

Step three check MySQL related status values

3.1 Attention to the number of connections
If the number of connections reaches the maximum number of connections, the user will block out regardless of the amount of resources.
To modify the maximum number of MySQL connections:
Open My.ini, modify max_connections=100 (default = 100).

Mysql> Show variables like ' max_connections ';

Please adjust to the appropriate size according to the hardware, the general experience value can be set to 3000. Windows Server probably supports a volume of 1500-1800 connections, and Linux servers can support up to 8,000 or so.
Please set Max_user_connections 0--– This 0 represents the maximum number of connections for a single user, whose maximum connection value can be equal to max_connections value.
Mysql> show global status like ' Max_used_connections ';
Check the maximum number of past use connections, this value in Max_connections of about 85% is more appropriate, if too high is max_connections too little or the system load is too high.

3.1.1 Mysqladmin-uroot Status

Mysql>status;--------------Connection ID:1Current database:current User: [Email protected]ssl:notinchusecurrent pager:stdoutusing outfile:"'Using delimiter:; Server version:5.5. the-log MySQL Community Server (GPL) Protocol version:TenConnection:localhost via UNIX socketserver characterset:latin1db characterset:latin1client Ch  Aracterset:utf8conn. Characterset:utf8unix Socket:/tmp/Mysql.sockuptime:2Min -secthreads:1Questions:9Slow Queries:0Opens: -Flush tables:1Open tables: -Queries per second avg:0.065

3.1.2 Show processlist (show processlist state shows only 100, show all with show full processlist)

1. Show All Processes

Mysql> Show full processlist; +-–+--+ ——— –+--+ ——— +--+ ——-+ ——————— –+| Id  | User | Host      | db   | Command | Time | State | Info                  629 | root | localhost | NULL | Query   |    0 | NULL  633 | root | localhost | NULL | Sleep   |    One |       | NULL                  |+-–+--+ ——— –+--+ ——— +--+ ——-+ ——————— –+2inset (0.00 sec)

2. If you are running too many statements, the running time is too long, it indicates that MySQL efficiency is a problem. The corresponding process can be killed when necessary.

Kill the dormant process kill ID number
Mysql> kill 633;
Query OK, 0 rows Affected (0.00 sec)

3.2 Follow slow query (slow) log
The log will inevitably slow down the system speed, especially CPU resources, so if the CPU resources are sufficient, can be opened, if not sufficient, it is necessary to adjust the time, or in the replication from the server open (for select)
Mysql> Show variables like '%slow% ';
+ ——————— + —————————————-+
| variable_name | Value |
+ ——————— + —————————————-+
| log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file | /data0/mysql/3306/data/mysql1-slow.log |
+ ——————— + —————————————-+
4 rows in Set (0.00 sec)

Mysql> set GLOBAL slow_query_log=on;
Query OK, 0 rows Affected (0.00 sec)

3.2.1 Focus on the related state of tables involved in slow queries
1. The number of records in the table. Try to control it within 5 million lines (with index), and advise control on 2 million rows
2. Use of the index within the table.
3. If Update,delete,insert is frequent, you can consider optimize table optimization under File storage, indexing, storage space.
4. The lock time of the Update,insert,delete query within the table.
5. Select for Update if the condition field is not indexed, it will cause the lock full table instead of the row lock, please pay attention.
6. If the query includes group by but you want to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting.

MySQL database server slowly slows down analysis