Shulou(Shulou.com)05/31 Report--

This article is about how to achieve mysql database performance diagnosis, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

One: check the performance of the server where mysql is located. The linux environment uses top and iostat.

[root@S243 etc] # top

Top-11:40:41 up 23 days, 17:06, 2 users, load average: 0.11,0.31,0.32

Tasks: 617 total, 1 running, 616 sleeping, 0 stopped, 0 zombie

Cpu0: 0.0%us, 0.0%sy, 0.0% Nijie 100.0% id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu1: 0.0%us, 0.0%sy, 0.0% Nijie 100.0% id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Cpu2: 1.0%us, 0.7%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st

Cpu3: 0.0%us, 0.0%sy, 0.0% Nijie 100.0% id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

The results of top are mainly concerned with load average: 0.11,0.31,0.32.

The three values of the system load (the average length of the task queue) are 1 minute, 5 minutes, and 15 minutes up to now, respectively, and less than 1 is normal. [these three are generally less than 1, if they continue to be higher than 5. Please check carefully which program affects the operation of the system]. "

[root@S243 etc] # iostat-d-x

Linux 2.6.32-131.0.15.el6.x86_64 (S243) 20 October 2016 _ x86 CPU 6424)

Device: rrqm/s wrqm/s rUnip s wdeband s rsec/s wsec/s avgrq-sz avgqu-sz await svctm% util

Sda 1.02 14.59 6.77 27.88 297.25 519.70 23.58 0.07 1.92 0.34 1.17

The meaning of output information

Rrqm/s: how many read requests related to this device have been Merge per second (when the system call needs to read data, VFS sends the request to each FS. If FS finds that different read requests read data from the same Block, FS will merge this request with Merge); wrqm/s: how many write requests related to this device have been Merge per second.

Rsec/s: number of sectors read per second

The number of sectors written by wsec/: per second.

RKB/s:The number of read requests that were issued to the device per second

WKB/s:The number of write requests that were issued to the device per second

Average request sector size of avgrq-sz

Avgqu-sz is the average length of the request queue. There is no doubt that the shorter the queue, the better.

Await: the average processing time (in microseconds and milliseconds) of each IO request. It can be understood as the response time of IO. Generally speaking, the response time of system IO should be lower than that of 5ms, and it is larger if it is greater than 10ms.

This time includes queue time and service time, that is to say, in general, await is greater than svctm, and the smaller the difference between them, the shorter the queue time, and the greater the reverse difference, the longer the queue time, indicating that there is something wrong with the system.

Svctm represents the average service time (in milliseconds) for each device Istroke O operation. If the value of svctm is very close to that of await, it means that there is almost no await waiting, and the disk performance is good. If the value of svctm is much higher than the value, the queue wait is too long and the application running on the system will be slower.

% util: all processing IO times during the statistical time divided by the total statistical time. For example, if the statistical interval is 1 second and the device has 0.8 seconds of processing IO and 0.2 seconds of idle, then the% util of the device = 0.8 shock 1 = 80%, so this parameter indicates how busy the device is

. Generally speaking, if this parameter is 100%, it means that the device is running at nearly full capacity (of course, if it is multiple disks, even if the% util is 100%, because of the concurrency of disks, disk usage may not be a bottleneck).

Two: check to see if there is any blockage:

Mysql > show processlist; pays attention to whether state has the status of lock. If there is, kill will solve it.

+-- -+-+

| | Id | User | Host | db | Command | Time | State | Info |

| | 3 | mailer | 192.168.0.225 NULL 45135 | mailer | Sleep | 26 | | NULL |

| | 8 | info_reader | 192.168.0.225 Sleep 45194 | info | Sleep | 56 | NULL |

| | 23 | info_reader | 192.168.0.225 Sleep 45451 | info | Sleep | 13 | | NULL |

| | 78 | info_reader | 192.168.0.225 NULL 54249 | info | Sleep | 68 | | NULL |

| | 180 | web_editer | 192.168.0.225 NULL 46200 | info | Sleep | 26 | | NULL |

| | 226 | web_editer | 192.168.0.225 Sleep 46584 | info | Sleep | 13 | | NULL |

| | 2035 | info_reader | 192.168.0.225 Sleep 53314 | info | Sleep | 36 | NULL |

| | 2052 | info_reader | 192.168.0.225 NULL 53447 | info | Sleep | 36 | | NULL |

| | 2384 | ruby_syncer | 192.168.0.218 Sleep 41868 | info | Sleep | 0 | | NULL |

| | 2387 | ruby_syncer | 192.168.0.218 Sleep 41870 | info | Sleep | 0 | | NULL |

Three: check mysql slow sql

1) first check whether slow sql is enabled. The red below shows that mysql slow query is turned on.

Mysql > show variables like'% query%'

+-+

| | Variable_name | Value |

+-+

| | binlog_rows_query_log_events | OFF |

| | ft_query_expansion_limit | 20 |

| | have_query_cache | YES |

| | long_query_time | 4.000000 | |

| | query_alloc_block_size | 8192 | |

| | query_cache_limit | 2097152 | |

| | query_cache_min_res_unit | 4096 | |

| | query_cache_size | 104857600 | |

| | query_cache_type | OFF |

| | query_cache_wlock_invalidate | OFF |

| | query_prealloc_size | 8192 | |

| | slow_query_log | ON |

| | slow_query_log_file | / mysql/datadir/S243-slow.log |

+-+

13 rows in set (0.00 sec)

Extension: the three main parameters related to mysql slow query are

(1) slow_query_log # specifies whether to enable slow query logs (0Uniq1 or off/on)

(2) slow_query_log_file # specifies the location of slow log files, which can be empty, and the system will give a default file host_name-slow.log

(3) long_query_time # sets the threshold for slow query. SQL that exceeds the secondary setting value is recorded in the slow query log. The default value is 10s.

(4) log_queries_not_using_indexes: whether the slow query log that does not use the index is recorded to the index (on/off)

(5) min_examined_row_limit: query check returns that SQL that is less than the row specified in this parameter is not recorded in the slow query log.

2) mysql > show status like'% Slow_queries%'; # shows the current number of slow queries, that is, those that are executing. If 0 is displayed, there is no sql. Exe that is currently executing longer than the long_query_time value. You need to check it in the slow query log.

+-+ +

| | Variable_name | Value |

+-+ +

| | Slow_queries | 2 | |

+-+ +

1 row in set (0.00 sec)

3) you can directly view the internal use of the slow log. Two slow sql are shown below.

[root@S243 datadir] # tail-n 10 S243-slow.log

# Time: 161020 15:19:39

# User@Host: mailer [mailer] @ [192.168.0.226] Id: 1372832

# Query_time: 12.617656 Lock_time: 0.000072 Rows_sent: 1 Rows_examined: 19796776

SET timestamp=1476947979

Select count (*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state100 and kehuguanzh0_.guanzhu_id='4f41910e-0ec6-4042-8c4aMurb2f0f9c8' and kehuguanzh0_.last_modify > = '2016-09-20' and kehuguanzh0_.last_modify='2016-09-20' and kehuguanzh0_.last_modify

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.