Linux to open MySQL slow query, analysis query statements

Source: Internet
Author: User
Tags mysql slow query log

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?
  1. Mysql> Show variables like "%long%"; //Check the default is slow query time 10 seconds
  2. +-----------------+-----------+
  3. | variable_name | Value |
  4. +-----------------+-----------+
  5. | Long_query_time | 10.000000 |
  6. +-----------------+-----------+
  7. 1 row in Set (0.00 sec)
  8. Mysql> set global long_query_time=2; //Set to 2 seconds, plus global, the next time you enter MySQL is already in effect
  9. Query OK, 0 rows Affected (0.00 sec)
  10. Mysql> Show variables like "%slow%"; //See if the slow query is turned on
  11. +---------------------+---------------------------------+
  12. | variable_name | Value |
  13. +---------------------+---------------------------------+
  14. | log_slow_queries | OFF |
  15. | Slow_launch_time | 2 |
  16. | Slow_query_log | OFF |
  17. | Slow_query_log_file | /usr/local/mysql/mysql-slow.log |
  18. +---------------------+---------------------------------+
  19. 4 rows in Set (0.00 sec)
  20. mysql> set slow_query_log=' on ';  //Add global, or you will get an error.
  21. ERROR 1229 (HY000): Variable ' Slow_query_log ' is a GLOBAL Variable and should BES set with set GLOBAL
  22. Mysql> set global slow_query_log=' on '; //Enable slow query
  23. Query OK, 0 rows affected (0.28 sec)
  24. Mysql> Show variables like "%slow%"; //See if it's turned on
  25. +---------------------+---------------------------------+
  26. | variable_name | Value |
  27. +---------------------+---------------------------------+
  28. | log_slow_queries | On |
  29. | Slow_launch_time | 2 |
  30. | Slow_query_log | On |
  31. | Slow_query_log_file | /usr/local/mysql/mysql-slow.log |
  32. +---------------------+---------------------------------+
  33. 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?
  1. [[email protected] mysql]# cat Mysql-slow.log //view command
  2. /usr/local/mysql/libexec/mysqld, Version:5.1.26-rc-log (Source distribution). Started with:
  3. TCP port:3306 Unix Socket:/tmp/mysql.sock
  4. Time Id Command Argument
  5. # time:100814 13:28:30
  6. # [email protected]: root[root] @ localhost []
  7. # query_time:10.096500 lock_time:0.045791 rows_sent:1 rows_examined:2374192
  8. SET timestamp=1281763710;
  9. 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);
  10. # time:100814 13:37:02
  11. # [email protected]: root[root] @ localhost []
  12. # query_time:10.394134 lock_time:0.000091 rows_sent:1 rows_examined:2374192
  13. SET timestamp=1281764222;
  14. 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);
  15. # time:100814 13:37:16
  16. # [email protected]: root[root] @ localhost []
  17. # query_time:4.608920 lock_time:0.000078 rows_sent:1 rows_examined:1260544
  18. SET timestamp=1281764236;
  19. 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?
  1. [Email protected] bin]# mysqldumpslow-h
  2. Option h requires an argument
  3. Error:bad option
  4. Usage:mysqldumpslow [OPTS ...] [LOGS ...]
  5. Parse and summarize the MySQL slow query log. Options are
  6. --verbose verbose
  7. --debug Debug
  8. --help write this text to standard output
  9. -V Verbose
  10. -D debug //Troubleshooting
  11. -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
  12. -R Reverse the sort order (largest last instead of first) //Inverted sort
  13. -T NUM just show the top n queries //display top N multiple
  14. -a Don' t abstract all numbers-N and strings to ' S'
  15. -N NUM abstract numbers with at least n digits within names//abstract number, at least n bits in name
  16. -G pattern Grep:only Consider stmts that include the This string//configuration mode
  17. -H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),//mysql so the machine name or IP
  18. Default is ' *', i.e. match all
  19. -I name name of server instance (if using Mysql.server startup script)
  20. -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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.