MySQL slow query log and MySQL Log

MySQL slow query log and MySQL Log

MySQL has a log called slow query log, which is mainly used to record time-consuming query operations.

 

. Through this log, we can analyze which operations affect performance, and we need

 

Some optimization measures are carried out.

View the enabled status

 

The above is MySQL5.7 installed in windows. We can find that this version enables slow query. MySQL5.7 installed in yum mode in CentOS6.9 does not enable slow query logs by default. No matter whether the log is enabled or not by default, we need to know how to enable the slow query log. The method of enabling the log is also very simple. Find the MySQL configuration file, my. ini in Windows, and my. cnf in Linux. Perform the following configuration.

Slow-query-log = 1


Slow_query_log_file = "mysql-slow.log"


Long_query_time = 10

The first line is to enable slow query logs.

The second row specifies the Log Path for slow query.

The third row specifies the query time to be greater than the number of records, but the operation is recorded in milliseconds, that is, operations greater than 10 ms.

After the configuration is complete, restart MySQL to take effect.

Next let's take a look at the slow query log Content.

 1 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.16-log (MySQL 2 Community Server (GPL)). started with: 3 TCP Port: 3306, Named Pipe: (null) 4 TimeId CommandArgument 5 #Time: 2017-07-07T06:35:46.995201Z 6 #User@Host: root[root] @ localhost [::1]  Id:10 7 #Query_time: 12.522116 Lock_time: 0.000501 Rows_sent: 0 Rows_examined: 483968 use test; 8 SET timestamp=1499409346; 9 insert into test (id,name) (select uuid() id,name from test);10 #Time: 2017-07-07T06:36:15.258316Z11 #User@Host: root[root] @ localhost [::1]  Id:1012 #Query_time: 24.543267 Lock_time: 0.000501 Rows_sent: 0 Rows_examined: 967936 SET timestamp=1499409375;13 insert into test (id,name) (select uuid() id,name from test);14 #Time: 2017-07-07T06:37:15.021922Z15 #User@Host: root[root] @ localhost [::1]  Id:1016 #Query_time: 56.283040 Lock_time: 0.000499 Rows_sent: 0 Rows_examined: 1935872 SET timestamp=1499409435;17 insert into test (id,name) (select uuid() id,name from test);18 #Time: 2017-07-07T06:40:07.866659Z19 #User@Host: root[root] @ localhost [::1]  Id:1020 #Query_time: 133.866927 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 3871744 SET timestamp=1499409607;21 insert into test (id,name) (select uuid() id,name from test);

 

We can see that on February 7, multiple slow queries were generated. Extract A group separately, as shown below:

1 #Time: 2017-07-07T06:35:46.995201Z2 #User@Host: root[root] @ localhost [::1]  Id:103 #Query_time: 12.522116 Lock_time: 0.000501 Rows_sent: 0 Rows_examined: 483968 use test;4 SET timestamp=1499409346;5 insert into test (id,name) (select uuid() id,name from test);

Query_time indicates the time consumed.

The following are some operations. The main operation is an insert

This is the slow query log.