Shulou(Shulou.com)06/02 Report--

This article is about MySQL how to open slow query logs. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

mysql optimization scheme: open slow query log (query sql execution more than one second sql, etc.)

Open slow query log: MySQL can record queries that exceed a specified time. By locating performance bottlenecks, the performance of the database system can be better optimized.

Parameter Description:

slow_query_log Slow query ON, ON, OFF

slow_query_log_file Slow query the location where logs are stored (this directory requires writeable permissions for MySQL running accounts, and is generally set to MySQL data storage directory)

long_query_time How many seconds does it take to record a query

Key note: Open slow log version to high, low version can not support, this version is: 5.7.20

SELECT VERSION(); Query version number

This version has slow logging enabled by default

mysql> show databases;mysql> use test; //specify a database mysql> 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 |+-----------------

Method 1: global variable setting (this method database restart all invalid, have to reconfigure)

Set the slow_query_log global variable to the ON state

mysql> set global slow_query_log='ON';

Set the location of the slow query log

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

Set the query to record more than 1 second (if sometimes the command does not work, then you can close and open)

mysql> set global long_query_time=1;

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

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

[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.log //linuxlong_query_time = 1

3. Restart MySQL service

service mysqld restart test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check if slow query logs are generated

Here you can see which sql and query time

If the log exists, MySQL enables slow query setting successfully!

Attachment: 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.

Check out mysqldumpslow's help info:

[root@DB-Server ~]# mysqldumpslow --helpUsage: 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), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time

-s, is to indicate how to sort,

c: Access count

l: Lock time

r: Return records

t: query time

al: Average lock time

ar: Average number of records returned

at: Average query time

-t, is the meaning of top n, that is, how many pieces of data are returned before;

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

such as

Get the 10 SQL that returned the most recordsets.

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

Get the 10 most visited SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

Get the first 10 query statements with left join sorted by time.

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

It is also recommended to combine these commands when using them| And more use, otherwise there may be a brush screen situation.

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

The article introduced to this, more related MySQL open slow query log content, please search for previous articles or continue to browse the following related articles hope that you will support more in the future!

Thank you for reading! About "MySQL how to open slow query log" this article is shared here, I hope the above content can have some help for everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.