Enable mysql slow query and analyze query statements in linux

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)
9. Mysql> set global long_query_time = 2; // set to 2 seconds. The next mysql entry takes effect when global is added.
10. Query OK, 0 rows affected (0.00 sec)
12. Mysql> show variables like "% slow %"; // check whether the slow query is enabled.
13. + --------------------- + ----------------------------------- +
14. | Variable_name | Value |
15. + --------------------- + ----------------------------------- +
16. | Log_slow_queries | OFF |
17. | Slow_launch_time | 2 |
18. | Slow_query_log | OFF |
19. | Slow_query_log_file |/usr/local/mysql/mysql-slow.log |
20. + --------------------- + ----------------------------------- +
21. 4 rows in set (0.00 sec)
23. Mysql> set slow_query_log = 'on'; // Add global. Otherwise, an error is returned.
24. ERROR 1229 (HY000): Variable 'slow _ query_log 'is a GLOBAL variable and shocould be set with SET GLOBAL
25. Mysql> set global slow_query_log = 'on'; // enable slow Query
26. Query OK, 0 rows affected (0.28 sec)
28. Mysql> show variables like "% slow %"; // check whether Enabled
29. + --------------------- + ----------------------------------- +
30. | Variable_name | Value |
31. + --------------------- + ----------------------------------- +
32. | Log_slow_queries | ON |
33. | Slow_launch_time | 2 |
34. | Slow_query_log | ON |
35. | Slow_query_log_file |/usr/local/mysql/mysql-slow.log |
36. + --------------------- + ----------------------------------- +
37. 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
5. Usage: mysqldumpslow [OPTS...] [LOGS...]
7. Parse and summarize the MySQL slow query log. Options are
9. -- Verbose
10. -- Debug
11. -- Help write this text to standard output
13. -V verbose
14. -D debug // check the error
15. -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.
16. -R reverse the sort order (largest last instead of first) // reverse sorting
17. -T NUM just show the top n queries // display the first N
18. -A don't abstract all numbers to N and strings to's'
19. -N NUM abstract numbers with at least n digits within names // abstract number, with a minimum of n-bit names
20. -G PATTERN grep: only consider into ts that include this string // Configuration Mode
21. -H HOSTNAME hostname of db server for *-slow. log filename (can be wildcard), // mysql is the machine name or IP address
22. Default is '*', I. e. match all
23. -I NAME name of server instance (if using mysql. server startup script)
24. -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