Summary of Mysql slow query operations and summary of mysql

Summary of Mysql slow query operations and summary of mysql

Mysql slow query explanation

The slow query log of MySQL is a log record provided by MySQL. It is used to record the statements whose response time exceeds the threshold value in MySQL, specifically the SQL statement whose running time exceeds the long_query_time value, the log is recorded in the slow query log. The default value of long_query_time is 10, which means to run a statement over 10 s. By default, the Mysql database does not start slow query logs. We need to manually set this parameter. Of course, if it is not required for optimization, it is generally not recommended to start this parameter, because enabling slow query logs will affect performance more or less. Slow query logs can be written to files or database tables. Slow query logs can be used to record slow query statements to help administrators analyze the problem, this log is not enabled by default. You need to add a series of parameters in the configuration file to manually start the log.

Why Mysql slow query is enabled

Databases are prone to bottlenecks. Now Nosql is so popular that it is estimated that they are all depressed by databases. The statements that affect the query speed in MySQL are very slow. These slow statements may be written improperly or the Combined Query of multiple tables in big data, therefore, we need to find these statements, analyze the causes, and optimize them. This is why I posted this blog post.

Enable mysql slow Query

1) Method 1: log on to the mysql database terminal and enable

Mysql> show variables like "% long %"; <SPAN style = "COLOR: # ff00ff "> // check that the default slow query time is 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 it to 1 second, add global, the next mysql entry takes effect </SPAN> Query OK, 0 rows affected (0.00 sec) mysql> show variables like "% slow %"; <SPAN style = "COLOR: # ff00ff "> // check whether the slow query is enabled </SPAN> + ------------------- + Enabled + | Variable_name | Value | + Enabled + | 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 occurs. </SPAN> ERROR 1229 (HY000 ): variable 'slow _ query_log 'is a GLOBAL variable and shocould be set with set global mysql> set global slow_query_log = 'on'; <SPAN style = "COLOR: # ff00ff "> // this parameter is set to ON, which indicates to enable slow query and 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 whether it is enabled </SPAN> + ------------------- + feature + | Variable_name | Value | + feature + | 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 in the [mysqld] region Configuration
......

Slow_query_log = 1 // enable slow query log. You can also change 1 to ON.
Long_query_time = 1 // mysql slow query time. the specified number of seconds is considered as slow query. Here, we recommend that you 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.
// The content of the preceding three lines is used. You can add the following lines of detail configuration.
Long-queries-not-using-indexes // records query statements without using indexes
Min_examined_row_limit = 1000 // records are queried for up to 1000 slow queries.
Log-slow-admin-statements // record slow queries caused by statements such as ALTER TABLE
Log-slow-slave-statements // record slow queries generated from the server

Analysis Tools

The analysis tool is the data recorded in the mysql-slow.log, the analysis shows (in fact, write a shell script can also take the information out ).

[Root @ www ~] # Cat mysql-slow.log // view command

/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 is to record the execution of SQL statements, including the execution time and lock time. Therefore, there are many analysis tools to check your personal situation, here we will only talk about how to use mysqldumpslow, a mysql-provided slow query and analysis tool.

[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 -- debug -- help write this text to standard output-v verbose-d debug // check-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default // Number of query times in the sorting mode, time, sort the lock time and the number of returned records-r reverse the sort order (largest last instead of first) // inverted sorting-t NUM just show the top n queries // display the first 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 number, -g PATTERN grep: only consider should ts that include this string // configuration mode-h HOSTNAME hostname of db server *-slow. log filename (can be wildcard), // mysql 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 // do not subtract the lock time in the total time

Instance:

[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

The summary of the above Mysql slow query operation is all the content shared by the editor. I hope you can give us a reference and support the help house.