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?
- Mysql> show variables like "% long %"; // check that the default slow query time is 10 seconds.
- + ----------------- + ----------- +
- | Variable_name | Value |
- + ----------------- + ----------- +
- | Long_query_time | 10.000000 |
- + ----------------- + ----------- +
- 1 row in set (0.00 sec)
-
- Mysql> set global long_query_time = 2; // set to 2 seconds. The next mysql entry takes effect when global is added.
- Query OK, 0 rows affected (0.00 sec)
-
- Mysql> show variables like "% slow %"; // check whether the slow query is enabled.
- + --------------------- + ----------------------------------- +
- | Variable_name | Value |
- + --------------------- + ----------------------------------- +
- | 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'; // Add global. Otherwise, an error is returned.
- 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'; // enable slow Query
- Query OK, 0 rows affected (0.28 sec)
-
- Mysql> show variables like "% slow %"; // check whether Enabled
- + --------------------- + ----------------------------------- +
- | Variable_name | Value |
- + --------------------- + ----------------------------------- +
- | 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
Add the following content to [mysqld ]:
- Long_query_time = 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?
- [Root @ BlackGhost mysql] # cat mysql-slow.log // view command
- /Usr/local/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started:
- 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.
View copy print?
- [Root @ BlackGhost bin] # 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 the error
- -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.
- -R reverse the sort order (largest last instead of first) // reverse sorting
- -T NUM just show the top n queries // display the first N
- -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, with a minimum of n-bit names
- -G PATTERN grep: only consider into ts that include this string // Configuration Mode
- -H HOSTNAME hostname of db server for *-slow. log filename (can be wildcard), // mysql is the machine name or IP address
- 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
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