This is the 5th day of my participation in Gwen Challenge

MySQL > slow query log

(1) slow query log is a log provided by MySQL. It is used to record statements in MySQL whose query time exceeds (long_query_time) the set threshold and records them in slow query log.

(2) Long_query_time defaults to 10.

2, how to enable MySQL slow query log

By default, slow query log is disabled in MySQL. You need to manually enable this function. If this function is not required for tuning, you are not advised to enable this function because this function may adversely affect performance. Slow log query supports writing log records to files.

(1) Enable Settings

-- Check whether slow query logs are enabled
show variables like '%slow_query_log%';
Copy the code

- Slow log query takes effect only for the current database and becomes invalid after the database is restarted
set global slow_query_log = 1;
Copy the code

-- Specifies the threshold for querying slow logs. The default value is 10 seconds
show variables like '%long_query_time%';
Copy the code

-- Set the threshold
set long_query_time = 3;
Copy the code

(2) Modify the configuration file my.cnf to make it permanent

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE
Copy the code

(3) Run the SLOW QUERY SQL and view the slow query logs

select sleep(4);
Copy the code

(4) Query the number of slow query records in the current system

show global status like '%Slow_queries%';
Copy the code

3. Log analysis tool mysqlDumpslow

The slow log query is not conducive to our analysis. Mysqldumpslow categorizes the same slow SQL and counts the number of times the same SQL is executed, how long each execution takes, the total time, the number of rows returned each time, the total number of rows, and client connection information.

How do you use Mysqlsumpslow with the –help command

  • -s Indicates the sorting mode.
  • C Number of access times.
  • L Lock time.
  • R returns the record.
  • T Query time.
  • Al average locking time.
  • Ar Average number of returned records.
  • At Average query time.
  • -t Returns the number of previous entries.
  • -g is followed by a regular matching mode, which is case-insensitive.
#Get the 10 SQL that return the most recordsets
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#Get the 10 most frequently accessed SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#Get the top 10 queries in chronological order that contain the left join
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#Additional Suggestions when using these commands | and more use, otherwise likely explodes the screen
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
Copy the code