Linux to open MySQL slow query, analysis query statements

One, why do you want to open this query?

The database is a very easy place to create bottlenecks, and now nosql people are talking about the heat, and it's estimated that the database is depressing. MySQL is the most affected by the speed of the query is very slow, these slow statements, may be not enough to write reasonable or big data under the multi-table joint query and so on, so we have to find these statements, analyze the reasons, to optimize. That's why you're posting this blog post.

Second, turn on MySQL slow query

Method 1, open a slow query with a command

View copy print?

2. Mysql> Show variables like "%long%"; //Check the default is slow query time 10 seconds
4. +-----------------+-----------+
6. | variable_name | Value |
8. +-----------------+-----------+
10. | Long_query_time | 10.000000 |
12. +-----------------+-----------+
14. 1 row in Set (0.00 sec)
18. Mysql> set global long_query_time=2; //Set to 2 seconds, plus global, the next time you enter MySQL is already in effect
20. Query OK, 0 rows Affected (0.00 sec)
24. Mysql> Show variables like "%slow%"; //See if the slow query is turned on
26. +---------------------+---------------------------------+
28. | variable_name | Value |
30. +---------------------+---------------------------------+
32. | log_slow_queries | OFF |
34. | Slow_launch_time | 2 |
36. | Slow_query_log | OFF |
38. | Slow_query_log_file | /usr/local/mysql/mysql-slow.log |
40. +---------------------+---------------------------------+
42. 4 rows in Set (0.00 sec)
46. mysql> set slow_query_log=' on ';  //Add global, or you will get an error.
48. ERROR 1229 (HY000): Variable ' Slow_query_log ' is a GLOBAL Variable and should BES set with set GLOBAL
50. Mysql> set global slow_query_log=' on '; //Enable slow query
52. Query OK, 0 rows affected (0.28 sec)
56. Mysql> Show variables like "%slow%"; //See if it's turned on
58. +---------------------+---------------------------------+
60. | variable_name | Value |
62. +---------------------+---------------------------------+
64. | log_slow_queries | On |
66. | Slow_launch_time | 2 |
68. | Slow_query_log | On |
70. | Slow_query_log_file | /usr/local/mysql/mysql-slow.log |
72. +---------------------+---------------------------------+
74. 4 rows in Set (0.00 sec)

Method 2, modify the MySQL configuration file my.cnf

In [mysqld], add the following content

1. Long_query_time = 2
2. Log-slow-queries =/usr/local/mysql/mysql-slow.log

Back up.
/usr/local/mysql/libexec/mysqld restart

Three, analysis tools

Analysis tool What to do, in fact, is the mysql-slow.log inside the data recorded, analysis of the show. Actually writing a shell script can also take out the information you want. Let's see what's inside Mysql-slow.log.

View copy print?

2. [[email protected] mysql]# cat Mysql-slow.log //view command
4. /usr/local/mysql/libexec/mysqld, Version:5.1.26-rc-log (Source distribution). Started with:
6. TCP port:3306 Unix Socket:/tmp/mysql.sock
8. Time Id Command Argument
10. # time:100814 13:28:30
12. # [email protected]: root[root] @ localhost []
14. # query_time:10.096500 lock_time:0.045791 rows_sent:1 rows_examined:2374192
16. SET timestamp=1281763710;
18. Select count (Distinct ad_code) as x from Ad_visit_history where Ad_code in (select Ad_code from Ad_list where Medi  A_ID=15);
20. # time:100814 13:37:02
22. # [email protected]: root[root] @ localhost []
24. # query_time:10.394134 lock_time:0.000091 rows_sent:1 rows_examined:2374192
26. SET timestamp=1281764222;
28. Select count (Distinct ad_code) as x from Ad_visit_history where Ad_code in (select Ad_code from Ad_list where Medi  A_ID=15);
30. # time:100814 13:37:16
32. # [email protected]: root[root] @ localhost []
34. # query_time:4.608920 lock_time:0.000078 rows_sent:1 rows_examined:1260544
36. SET timestamp=1281764236;
38. 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, is to record the execution of SQL statements, including execution time, lock time, etc., so do not analyze the tool to see the personal situation, the analysis of a lot of tools, here only to talk about the MySQL comes with the slow query analysis tool Mysqldumpslow method.

View copy print?

2. [Email protected] bin]# mysqldumpslow-h
4. Option h requires an argument
6. Error:bad option
10. Usage:mysqldumpslow [OPTS ...] [LOGS ...]
14. Parse and summarize the MySQL slow query log. Options are
18. --verbose verbose
20. --debug Debug
22. --help write this text to standard output
26. -V Verbose
28. -D debug //Troubleshooting
30. -S order what to sort by (t, at, L, AL, R, AR etc), "at" is default //sorted by query count, time, lock time and number of records returned to sort
32. -R Reverse the sort order (largest last instead of first) //Inverted sort
34. -T NUM just show the top n queries //display top N multiple
36. -a Don' t abstract all numbers-N and strings to ' S'
38. -N NUM abstract numbers with at least n digits within names//abstract number, at least n bits in name
40. -G pattern Grep:only Consider stmts that include the This string//configuration mode
42. -H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),//mysql so the machine name or IP
44. Default is ' *', i.e. match all
46. -I name name of server instance (if using Mysql.server startup script)
48. -L don ' t subtract lock time from total time//not minus lock times

Example:

[Email protected] bin]#/mysqldumpslow-s r-t 20/usr/local/mysql/mysql-slow.log

[Email protected] bin]#/mysqldumpslow-s r-t 20-g ' count '/usr/local/mysql/mysql-slow.log

Linux to open MySQL slow query, analysis query statements