MySQL slow log query method

MySQL slow log query method

The Method for Analyzing slow query logs using mysqldumpslow is introduced in the past. As the work progresses, another more useful method is found: To record slow logs to the mysql database.

MySQL slow log query tool-mysqldumpslow

Method Description: by setting the log_output parameter, you can define whether mysql slow query logs are stored in files or data tables;

Mysql> show variables like 'Log _ output ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Log_output | FILE |
+ --------------- + ------- +
1 row in set (0.00 sec)

Note: The Value is the output format. If the current Value is FILE, the slow query log is stored in the OS FILE;

Mysql> set global log_output = 'table ';
Query OK, 0 rows affected (0.00 sec)

Note: set the current output to a table in mysql;

Mysql> show variables like 'Log _ output ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Log_output | TABLE |
+ --------------- + ------- +
1 row in set (0.00 sec)

Note: The table in mysql is now output;

Test:

Suggestion: When slow_log is created, the default database engine is CSV, which has a low query efficiency and can be changed to MyISAM;

Mysql> select * from mysql. slow_log;

Create table 'slow _ log '(
'Start _ time' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
'User _ host' mediumtext not null,
'Query _ time' time not null,
'Lock _ time' time not null,
'Rows _ sent' int (11) not null,
'Rows _ examined' int (11) not null,
'Db' varchar (512) not null,
'Last _ insert_id 'int (11) not null,
'Insert _ id' int (11) not null,
'Server _ id' int (10) unsigned not null,
'SQL _ text' mediumtext NOT NULL
) ENGINE = csv default charset = utf8 COMMENT = 'lowlog ';
 

A commonly used SQL statement is sorted by the number of times.

Mysql> select SQL _text, count (1), avg (query_time), avg (rows_sent), avg (rows_examined)
From slow_log
Group by (SQL _text)
Order by avg (query_time) desc

This article permanently updates the link address: