Summary of Mysql slow query operation

Mysql Slow Query Interpretation

The slow query log of MySQL is a kind of log record provided by MySQL, which is used to record statements whose response time exceeds the threshold value in MySQL. Specifically, SQL whose running time exceeds the value of long_query_time will be recorded in the slow query log. The default value for long_query_time is 10, meaning to run statements above 10S. By default, the Mysql database does not start the slow query log, so we need to set this parameter manually. Of course, if it is not necessary for tuning, it is not recommended to start this parameter, because starting the slow query log will bring more or less a certain performance impact. Slow query log supports writing log records to files and writing log records to database tables; Slow query log is used to record some slow query statements, which can help administrators to analyze the problem. The log is not opened by default, and it needs to be started manually by adding 1 series of parameters in the configuration file

Reasons for opening Mysql slow query

Database is very easy to produce bottlenecks. Now Nosql is so hot that it is estimated that the database is depressed. The statements that affect the speed most in MySQL are those with very slow queries. These slow statements may be unreasonable or joint queries of multiple tables under big data, etc., so we should find out these statements, analyze the reasons and optimize them. That's why I posted this blog post

Open mysql slow query method

1) Method 1. Log in to mysql database terminal and open it

mysql> show variables like "%long%";    <SPAN style="COLOR: #ff00ff"> // View 1 Default to slow query time under 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 Seconds, plus global, Enter next time mysql Has come into effect </SPAN>  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> show variables like "%slow%";     <SPAN style="COLOR: #ff00ff"> // View 1 Is the next slow query already opened  </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"> // Plus global Or you'll report an error </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">// This parameter is set to ON Slow queries are enabled to capture execution times that exceed 1 Constant value SQL Statement. </SPAN> 
Query OK, 0 rows affected (0.28 sec)  
  
mysql> show variables like "%slow%";       <SPAN style="COLOR: #ff00ff">// Check to see if it is turned on  </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 configuration file my. cnf of mysql

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

slow_query_log = 1//Turn on slow query log and change 1 to ON
long_query_time = 1//mysql slow query time, specifying how many seconds to count as a slow query; It is recommended to set the execution statement exceeding 1 second to be recorded in the slow query log
slow_query_log_file=/var/lib/mysql/mysql-slow. log//Slow query log path. It is also possible to modify slow_query_log_file to log-slow-queries
//It is mainly the above three lines, and the following lines of detailed configuration can be added
long-queries-not-using-indexes//Record queries that do not use indexes
min_examined_row_limit = 1000//Slow query caused by records looking up to 1000 times
log-slow-admin-statements//Record slow queries raised by statements such as ALTER TABLE
log-slow-slave-statements//Record slow queries generated from the server

Analysis tool

In fact, the analysis tool displays the data recorded in mysql-slow. log after analysis 1 (in fact, writing an shell script can also take out 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;

See, it is to record the execution of sql statement under 1, including execution time, locking time, etc., so whether to analyze tools depends on personal situation, and there are many analysis tools. Here, we only talk about the use of mysqldumpslow, a slow query analysis tool that comes with mysql under 1.

[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 mode query Frequency, time, lock And the number of records returned   
 -r      reverse the sort order (largest last instead of first)    // Reverse sort   
 -t NUM    just show the top n queries                    // Before Display N Multiple   
 -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, to   Rare n The name within the bit  
 -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 So the machine name or IP 
 default is '*', i.e. match all 
 -i NAME   name of server instance (if using mysql.server startup script) 
 -l      don't subtract lock time from total time      // The lock time is not subtracted from the total time 

Example:

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

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