MySQL Slow query collation

open MySQL slow query under Linux
1. Introduction to Slow Queries
As the name implies, the slow query log is a long-running query SQL statement, the default file name is Hostname-slow.log, the default directory is also the data directory.
The slow query log is a simple text format that allows you to view the content in a variety of text editors.
It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information.
MySQL provides a dedicated tool mysqlslowdump to analyze slow query logs, which DBAs can use to locate possible performance issues with the database.
2. Opening method
Step 1: Turn on settings for the slow query feature
Method 1: Modify the global parameters to open

Check if slow query is on {default is off}
mysql> Show variables like "%slow%";
+---------------------+----------------------------------+
| variable_name | Value |
+---------------------+----------------------------------+
| log_slow_queries | OFF |
| Slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file |/mnt/mydata/tkudb-slow.log |
+---------------------+----------------------------------+
4 rows in Set (0.00 sec)
Turn on Slow query
mysql> set global slow_query_log= ' on ';
Query OK, 0 rows affected (0.01 sec)
Verifying slow queries
Mysql> Show variables like "%slow%";
+---------------------+----------------------------------+
| variable_name | Value |
+---------------------+----------------------------------+
| log_slow_queries | On |
| slow_launch_time | 2 |
| Slow_query_log | On |
| slow_query_log_file |/mnt/mydata/tkudb-slow.log |
+---------------------+----------------------------------+
4 rows in Set (0.00 sec)

Method 2: Modify my.cnf{Add the following command}

#cat my.cnf
Long_query_time = 3
Log-slow-queries =/mnt/mydata/tkudb-slow.log
After restarting the MySQL service, the setting takes effect;

Step 2: Set the time for slow queries

system default View {Default Slow query time is 10s}
mysql> Show variables like "%long%";
+---------------------------------------------------+-----------+
| variable_name | Value |
+---------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| Max_long_data_size | 1048576 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+-----------+
3 rows in Set (0.00 sec)
mysql> set global long_query_time=3;
Query OK, 0 rows Affected (0.00 sec)

3. Log analysis and processing

1. Use text processing tools such as notepad+ to process Tkudb-slow.log
2. Using the Mysqldumpslow tool
[Email protected] mydata]# Mysqldumpslow--help
Usage:mysqldumpslow [OPTS ...] [LOGS ...]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug Debug
--help Write this text to standard output
- v Verbose
- D Debug
- s ORDER what to sort by (Al, at, AR, C, L, R, T), ' on ' is default
Al:average Lock Time
ar:average rows Sent
at:average Query Time
C:count
L:lock Time
R:rows Sent
t:query Time
-R Reverse the sort order (largest last instead of first)
- T NUM just show the top n queries
-a don ' t abstract all numbers-N and strings to ' S '
- n NUM abstract numbers with at least n digits within names
- G PATTERN grep:only Consider stmts that include the This string #匹配模式
- H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),
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
Example:
query last 10 times log
[email protected] mydata]# mysqldumpslow-s r-t 10/mnt/mydata/tkudb-slow.log
Query the last 10 logs with a SELECT keyword
[email protected] mydata]# mysqldumpslow-s r-t 10-g ' select '/mnt/mydata/tkudb-slow.log
Method 3: The cluster database, the slow log of all hosts write to a monitoring database of the table, the unified call by PHP display!

4. How to regularly safely clean up slow.log

1. Perform the backup first, then compress the backup files, and finally clear the Slow.log

MySQL Slow query collation