Summary of Mysql slow query operations

Mysql slow query explanation

The MySQL slow query log is a type of log record provided by MySQL. It is used to record statements in MySQL whose response time exceeds the threshold. Specifically, SQL statements whose running time exceeds the long_query_time value will be recorded in the slow query log. The default value of long_query_time is 10, which means that the statement runs for more than 10 seconds. By default, the MySQL database does not start the slow query log, and we need to manually set this parameter. Of course, if it is not required for tuning, it is generally not recommended to start this parameter, because turning on the slow query log will more or less bring certain performance impact. The slow query log supports writing log records to files and database tables. The slow query log is used to record some slow query statements, which can help administrators analyze the problem. The log is not enabled by default and needs to be manually enabled by adding a series of parameters in the configuration file.

Reasons for enabling MySQL slow query

Databases are places where bottlenecks can easily occur. Nosql is being discussed so hotly now, and I guess everyone is frustrated by the database. The statements that have the greatest impact on MySQL speed are those that are very slow to query. These slow statements may be written improperly or may be joint queries of multiple tables under large data, etc. Therefore, we need to find these statements, analyze the reasons, and optimize them. This is also the reason for posting this blog post

How to enable MySQL slow query

1) Method 1: Log in to the MySQL database terminal and open

mysql> show variables like "%long%"; <SPAN style="COLOR: #ff00ff"> // Check the default slow query time of 10 seconds</SPAN> 
+-----------------+-----------+  
| Variable_name | Value |  
+-----------------+-----------+  
| long_query_time | 10.000000 |  
+-----------------+-----------+  
1 row in set (0.00 sec)  
  
mysql> set global long_query_time=1; <SPAN style="COLOR: #ff00ff">//Set to 1 second, add global, and it will take effect next time you enter mysql</SPAN>  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> show variables like "%slow%"; <SPAN style="COLOR: #ff00ff"> // Check if slow query is enabled</SPAN> 
+---------------------+---------------------------------+  
| Variable_name | Value |  
+---------------------+---------------------------------+  
| log_slow_queries | OFF |  
| slow_launch_time | 2 |  
| slow_query_log | OFF |  
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
+---------------------+---------------------------------+  
4 rows in set (0.00 sec)  
  
mysql> set slow_query_log='ON'; <SPAN style="COLOR: #ff00ff"> //Add global, otherwise an error will be reported</SPAN> 
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL 
mysql> set global slow_query_log='ON'; <SPAN style="COLOR: #ff00ff">//Setting this parameter to ON means enabling slow queries, which can capture SQL statements whose execution time exceeds a certain value. </SPAN> 
Query OK, 0 rows affected (0.28 sec)  
  
mysql> show variables like "%slow%"; <SPAN style="COLOR: #ff00ff">//Check if it is enabled</SPAN> 
+---------------------+---------------------------------+  
| Variable_name | Value |  
+---------------------+---------------------------------+  
| log_slow_queries | ON |  
| slow_launch_time | 2 |  
| slow_query_log | ON |  
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
+---------------------+---------------------------------+  
4 rows in set (0.00 sec)

Method 2: Modify the MySQL configuration file my.cnf

[root@www ~]# vim /etc/mysql.cnf
Add the following content to the [mysqld] area configuration
......

slow_query_log = 1 //Open the slow query log, you can also change 1 to ON
long_query_time = 1 //mysql slow query time, specify how many seconds it takes to be considered a slow query; it is recommended to set execution statements that exceed 1 second to be recorded in the slow query log
slow_query_log_file= /var/lib/mysql/mysql-slow.log //Slow query log path. Here, slow_query_log_file can be changed to log-slow-queries.
// Mainly the above three lines of content, you can also add the following lines of detailed configuration
long-queries- not-using-indexes //Record query statements that do not use indexes
min_examined_row_limit = 1000 //Slow query caused by searching for records up to 1000 times
log-slow-admin-statements //Record slow queries caused by ALTER TABLE and other statements
log-slow-slave-statements //Record slow queries generated from the server

Analysis tools

The analysis tool actually analyzes the data recorded in mysql-slow.log and displays it (in fact, you can also write a shell script to extract the required information).

[root@www ~]# cat mysql-slow.log //View commands

/usr/local/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started with:  
Tcp port: 3306 Unix socket: /tmp/mysql.sock  
Time Id Command Argument  
# Time: 100814 13:28:30  
# User@Host: root[root] @ localhost []  
# Query_time: 10.096500 Lock_time: 0.045791 Rows_sent: 1 Rows_examined: 2374192  
SET timestamp=1281763710;  
select count(distinct ad_code) as x from ad_visit_history where ad_code in (select ad_code from ad_list where media_id=15);  
# Time: 100814 13:37:02  
# User@Host: root[root] @ localhost []  
# Query_time: 10.394134 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 2374192  
SET timestamp=1281764222;  
select count(distinct ad_code) as x from ad_visit_history where ad_code in (select ad_code from ad_list where media_id=15);  
# Time: 100814 13:37:16  
# User@Host: root[root] @ localhost []  
# Query_time: 4.608920 Lock_time: 0.000078 Rows_sent: 1 Rows_examined: 1260544  
SET timestamp=1281764236;  
select count(*) as cou from ad_visit_history where ad_code in (select ad_code from ad_list where id=41) order by id desc;

As you can see, it just records the execution status of SQL statements, including execution time, lock time, etc., so whether to use analysis tools depends on personal situation. There are many analysis tools. Here I will only talk about how to use mysqldumpslow, a slow query analysis tool that comes with MySQL.

[root@www ~]# mysqldumpslow -h

Option h requires an argument  
ERROR: bad option 
  
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  
  
Parse and summarize the MySQL slow query log. Options are  
  
 --verbose verbose  
 --debug debug  
 --help write this text to standard output  
  
 -v verbose  
 -d debug //Error checking -s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default //Sorting method: query times, time, lock time and number of records returned -r reverse the sort order (largest last instead of first) //Reverse sort -t NUM just show the top n queries //Show the top N queries -a don't abstract all numbers to N and strings to 'S' 
 -n NUM abstract numbers with at least n digits within names //Abstract numbers, at least n digits within the name -g PATTERN grep: only consider stmts that include this string //Configuration mode -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), //MySQL machine name or IP 
 The default is '*', ie match all 
 -i NAME name of server instance (if using mysql.server startup script) 
 -l don't subtract lock time from total time //Do not subtract lock time from total time

Examples:

[root@BlackGhost bin]# ./mysqldumpslow -sr -t 20 /var/lib/mysql/mysql-slow.log

[root@BlackGhost bin]# ./mysqldumpslow -sr -t 20 -g 'count' /var/lib/mysql/mysql-slow.log

The above summary of MySQL slow query operations is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in: