MySQL Slow Query Log)

MySQL Slow Query Log)

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, such as error log files, binary logs, common logs, and slow query logs. These logs help us locate the internal events of mysqld, database performance faults, record the change history of data, and recover the database. This document describes general query logs.

1. Composition of the MySQL Log File System
A. Error Log: records the problems that occur when mysqld is started, running, or stopped.
B. General logs: records established client connections and executed statements.
C. Update logs: statements used to record and change data. This log is no longer used in MySQL 5.1.
D. binary log: records all statements for changing data. It is also used for replication.
E. Slow query log: records all queries whose execution time exceeds long_query_time seconds or where no index is used.
F. Innodb log: innodb redo log

By default, all logs are created in the mysqld data directory.
You can force mysqld to close and reopen the log file by refreshing the log (or switch to a new log in some cases ).
When you execute a flush logs statement or mysqladmin flush-logs or mysqladmin refresh, the log is aging.
In the case of MySQL replication, the slave replication server maintains more log files, which are called replacement logs.

2. Slow query logs
Slow query logs record SQL statements that affect the database performance on the mysql server to log files. By analyzing these special SQL statements, you can improve the database performance.
Use the -- slow_query_log [= {0 | 1}] Option to enable slow query logs. All SQL statements whose execution time exceeds long_query_time are recorded in slow query logs.
By default, the hostname-slow.log is the name of the slow query log file, which is stored in the data directory. By default, the slow query log is not enabled.
By default, managed SQL statements (such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE) related to the database are not logged.
For management SQL statements, you can use -- log-slow-admin-statements to enable slow SQL statements for record management.
After the statement is executed and all the locks are released, the mysqld logs are recorded as slow query logs. The record sequence may be different from the execution sequence. Obtaining the initial lock time is not counted as the execution time.

You can use the mysqldumpslow command to obtain the query summary displayed in the log to process slow query logs.
Queries processed using the query cache are not added to the slow query log. queries with zero rows or one row in the table that cannot benefit from the index are not written into the slow query log.
The MySQL server records whether SQL statements are written to slow query logs in the following order.
A. The query must either not be an administrative statement, or -- log-slow-adminstatements must have been specified.
B. The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.
C. The query must have examined at least min_examined_row_limit rows.
D. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.

3. Slow query log demonstration

Long_query_time: sets the threshold for slow queries. SQL statements that exceed the threshold are recorded in slow query logs. The default value is 10 s.
Slow_query_log: Specifies whether to enable slow query logs.
Log_slow_queries: Specifies whether to enable slow query logs (this parameter must be replaced by slow_query_log for compatibility retention)
Slow_query_log_file: Specifies the location where the slow log file is stored. It can be left blank and the system will give a default file host_name-slow.log
Min_examined_row_limit: the query check returns SQL statements that are less than the specified row of this parameter and are not recorded in slow query logs.
Log_queries_not_using_indexes

-- Current version
Root @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.5.39-log |
+ --------------- + ------------ +

Root @ localhost [(none)]> show variables like '% slow % ';
+ --------------------- + ----------------------------------- +
| Variable_name | Value |
+ --------------------- + ----------------------------------- +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/var/lib/mysql/SUSE11b-slow.log |
+ --------------------- + ----------------------------------- +

Root @ localhost [tempdb]> set global log_slow_queries = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Root @ localhost [(none)]> show warnings;
+ --------- + ------ + Response +
| Level | Code | Message |
+ --------- + ------ + Response +
| Warning | 1287 | '@ log_slow_queries' is deprecated and will be removed in a future release. Please use' @ slow_query_log 'instead |
+ --------- + ------ + Response +

-- The following query shows that two system variables, log_slow_queries, and slow_query_log, are set to on at the same time.
Root @ localhost [(none)]> show variables like '% slow % ';
+ --------------------- + ----------------------------------- +
| Variable_name | Value |
+ --------------------- + ----------------------------------- +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/var/lib/mysql/suse11b-slow.log |
+ --------------------- + ----------------------------------- +

Root @ localhost [tempdb]> show variables like '% long_query_time % ';
+ ----------------- + ----------- +
| Variable_name | Value |
+ ----------------- + ----------- +
| Long_query_time | 10.000000 |
+ ----------------- + ----------- +

-- For ease of demonstration, we set global and session level long_query_time to 1
Root @ localhost [tempdb]> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

Root @ localhost [tempdb]> set session long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

-- Author: Leshami
-- Blog: http://blog.csdn.net/leshami

Root @ localhost [tempdb]> create table tb_slow as select * from information_schema.columns;
Query OK, 829 rows affected (0.10 sec)
Records: 829 Duplicates: 0 Warnings: 0

Root @ localhost [tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 829 rows affected (0.05 sec)
Records: 829 Duplicates: 0 Warnings: 0
... For ease of demonstration, we insert some data, and the repeating process is omitted.
Root @ localhost [tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 26528 rows affected (4.40 sec)
Records: 26528 Duplicates: 0 Warnings: 0

Root @ localhost [tempdb]> system tail/var/lib/mysql/suse11b-slow.log
/Usr/sbin/mysqld, Version: 5.5.39-log (MySQL Community Server (GPL). started:
Tcp port: 3306 Unix socket:/var/lib/mysql. sock
Time Id Command Argument
# Time: 141004 22:05:48
# User @ Host: root [root] @ localhost []
# Query_time: 4.396858 Lock_time: 0.000140 Rows_sent: 0 Rows_examined: 53056
Use tempdb;
SET timestamp = 1412431548;
Insert into tb_slow select * from tb_slow;

... Insert some records again ....
Root @ localhost [tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 212224 rows affected (37.51 sec)
Records: 212224 Duplicates: 0 Warnings: 0

Root @ localhost [tempdb]> select table_schema, table_name, count (*) from tb_slow
-> Group by table_schema, table_name order by 3, 2;
+ -------------------- + ---------------------------------------------- + ---------- +
| Table_schema | table_name | count (*) |
+ -------------------- + ---------------------------------------------- + ---------- +
| Information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 1024 |
| Performance_schema | cond_instances | 1024 |
...........
| Mysql | user | 21504 |
+ -------------------- + ---------------------------------------------- + ---------- +
83 rows in set (1.58 sec)

Root @ localhost [tempdb]> system tail/var/lib/mysql/suse11b-slow.log
# User @ Host: root [root] @ localhost []
# Query_time: 37.514172 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 424448
SET timestamp = 1412431806;
Insert into tb_slow select * from tb_slow;
# Time: 141004 22:10:47
# User @ Host: root [root] @ localhost []
# Query_time: 1.573293 Lock_time: 0.000183 Rows_sent: 83 Rows_examined: 424614
SET timestamp = 1412431847;
Select table_schema, table_name, count (*) from tb_slow -- This SQL statement is recorded and its query time is 1.573293 s.
Group by table_schema, table_name order by 3, 2;

Root @ localhost [tempdb]> show variables like '% log_queries_not_using_indexes ';
+ ------------------------------- + ------- +
| Variable_name | Value |
+ ------------------------------- + ------- +
| Log_queries_not_using_indexes | OFF |
+ ------------------------------- + ------- +

Root @ localhost [tempdb]> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected (0.00 sec)

-- View the table tb_slow index information. The table tb_slow does not have any index.
Root @ localhost [tempdb]> show index from tb_slow;
Empty set (0.00 sec)

Root @ localhost [tempdb]> select count (*) from tb_slow;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 424448 |
+ ---------- +
1 row in set (0.20 sec)

Root @ localhost [tempdb]> system tail-n3/var/lib/mysql/suse11b-slow.log
# Query_time: 0.199840 Lock_time: 0.000152 Rows_sent: 1 Rows_examined: 424448
SET timestamp = 1412432188;
Select count (*) from tb_slow; -- the query time is 0.199840. The reason for the record is that the index is not taken because the table itself has no index.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

For more details, please continue to read the highlights on the next page:

1 2 Next Page