MySQL optimization solution: enable slow query log

Preface

This solution is only suitable for small projects, projects that are not online, or in emergency situations. Once the slow log query is turned on, it will increase the pressure on the database. Therefore, the background is generally used to write the data operation time into the log file, and the log is cleared regularly every week.

MySQL optimization plan: Enable slow query log (query SQL execution takes more than one second, etc.)

Enable slow query log: MySQL can record query statements that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

Parameter Description:

slow_query_log slow query enable status, ON to enable, OFF to disable

slow_query_log_file The location where the slow query log is stored (this directory requires writable permissions for the MySQL running account, and is generally set to the MySQL data storage directory)

long_query_time How many seconds does it take for a query to be recorded?

Key points: The slow log version must be higher, lower versions cannot support it. This version is: 5.7.20

SELECT VERSION(); query version number

This version has slow log enabled by default.

mysql> show databases;

mysql> use test; //Specify a databasemysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+-----------------+-----------+
| slow_query_log | ON |
+-----------------+-----------+
| slow_query_log_file | YH-20161209QIZC-slow.log |
+-----------------+-----------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

//By default, the query will be recorded only if it exceeds 10 seconds

Setting up slow query logging

Method 1: Global variable settings (this method will fail if the database is restarted and must be reconfigured)

Set the slow_query_log global variable to the "ON" state

mysql> set global slow_query_log='ON';

Set the location where the slow query log is stored

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linux
mysql> set global slow_query_log_file='D:\\mysq\data\slow.log'; //windows

Set the query to be logged if it exceeds 1 second (if the command does not work sometimes, you can turn it off and on again)

mysql> set global long_query_time=1;

Method 2: Configuration file settings (server restart will not affect)

Modify the configuration file my.cnf and add the following under [mysqld]

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
long_query_time = 1

3. Restart MySQL service

service mysqld restart

test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether slow query logs are generated

Here you can see the sql and query time

If the log exists, MySQL slow query setting is enabled successfully!

Appendix: Log analysis tool mysqldumpslow

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a manual job. MySQL provides a log analysis tool mysqldumpslow

View the help information for mysqldumpslow:

[root@DB-Server ~]# 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), 'at' 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 to N and strings to 'S'

  -n NUM abstract numbers with at least n digits within names

  -g PATTERN grep: only consider stmts that include this string

  -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),

               The default is '*', ie match all

  -i NAME name of server instance (if using mysql.server startup script)

  -l don't subtract lock time from total time

-s, indicates the sorting method.

• c: Visit count
• l: Lock time
• r: return records
• t: query time
• al: Average lock time
• ar: average number of returned records
• at: average query time

-t, which means top n, means how many records are returned.

-g, followed by a regular expression matching pattern, case-insensitive;

for example

Get the top 10 SQL statements that return the most records.

mysqldumpslow -sr -t 10 /database/mysql/mysql06_slow.log

Get the top 10 most visited SQLs

mysqldumpslow -sc -t 10 /database/mysql/mysql06_slow.log

Get the first 10 query statements containing left joins sorted by time.

mysqldumpslow -st -t 10 -g "left join" /database/mysql/mysql06_slow.log

It is also recommended to use these commands in combination with | and more, otherwise the screen may be refreshed.

mysqldumpslow -sr -t 20 /mysqldata/mysql/mysql06-slow.log | more

Summarize

This is the end of this article about MySQL optimization solution to enable slow query log. For more relevant content about enabling MySQL slow query log, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!