Enable mysql slow query and analyze query statements in linux

Source: Internet
Author: User
Tags mysql slow query log
I. Why should I enable this query? 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, so we need to find out these words.

I. Why should I enable this query? 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, so we need to find out these words.

I. Why should I enable this query?

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.

2. Enable mysql slow Query

Method 1: Use the command to enable slow Query

View copy print?

  1. Mysql> show variables like "% long %"; // check that the default slow query time is 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. The next mysql entry takes effect when global is added.
  9. Query OK, 0 rows affected (0.00 sec)
  10. Mysql> show variables like "% slow %"; // check whether the slow query is enabled.
  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. Otherwise, an error is returned.
  21. ERROR 1229 (HY000): Variable 'slow _ query_log 'is a GLOBAL variable and shocould be 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 %"; // check whether Enabled
  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

Add the following content to [mysqld ]:

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

Restart
/Usr/local/mysql/libexec/mysqld restart

Iii. Analysis Tools

What is the analysis tool, in fact, is the data recorded in the mysql-slow.log, analysis shows. In fact, you can write a shell script to obtain the required information. Let's take a look at what's in the mysql-slow.log.

View copy print?

  1. [Root @ BlackGhost mysql] # cat mysql-slow.log // view command
  2. /Usr/local/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started:
  3. Tcp port: 3306 Unix socket:/tmp/mysql. sock
  4. Time Id Command Argument
  5. # Time: 100814 13:28:30
  6. # User @ Host: 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 media_id = 15 );
  10. # Time: 100814 13:37:02
  11. # User @ Host: 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 media_id = 15 );
  15. # Time: 100814 13:37:16
  16. # User @ Host: 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;

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.

View copy print?

  1. [Root @ BlackGhost 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
  7. -- Debug
  8. -- Help write this text to standard output
  9. -V verbose
  10. -D debug // check the error
  11. -S ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default // Number of query times in sorting mode, time, sort the lock time and the number of returned records.
  12. -R reverse the sort order (largest last instead of first) // reverse sorting
  13. -T NUM just show the top n queries // display the first N
  14. -A don't abstract all numbers to N and strings to's'
  15. -N NUM abstract numbers with at least n digits within names // abstract number, with a minimum of n-bit names
  16. -G PATTERN grep: only consider into ts that include this string // Configuration Mode
  17. -H HOSTNAME hostname of db server for *-slow. log filename (can be wildcard), // mysql is the machine name or IP address
  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 // do not subtract the lock time in the total time

Example:

[Root @ BlackGhost bin] #./mysqldumpslow-s r-t 20/usr/local/mysql/mysql-slow.log.

[Root @ BlackGhost bin] #./mysqldumpslow-s r-t 20-g 'Count'/usr/local/mysql/mysql-slow.log


----- Organize from the Internet

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.