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

Editor to share with you mysql start slow query method, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

The default value for long_query_time is 10, which means to run a statement above 10s.

Settings of slow query log

1. Check whether the slow query log command is enabled:

Show variables like'% slow_query_log%'

2. Set the command to open slow query

Set global slow_query_log=1

Note:

Slow_query_log ON is on, OFF is closed

Slow_query_log_file is the address where slow log is stored.

3. Query and modify the time of slow query definition

Show variables like 'long_query_time%'set global long_query_time=4

4. Queries that do not use indexes are recorded in the slow query log. If tuning, it is recommended to turn on this option. If this parameter is enabled, the sql of full index scan will also be recorded in the slow query log.

Show variables like 'log_queries_not_using_indexes'set global log_queries_not_using_indexes=1

5. Query how many slow query records there are

Show global status like'% Slow_queries%'

Mysqldumpslow slow log analysis tool

Command:

-s sort in that way c: access count l: lock time r: return records al: average lock time ar: average number of access records at: average query time-t means top n, how many pieces of data are returned. -g can keep up with the regular matching pattern and is case-insensitive.

Get the 20 sql with the most returned records

Mysqldumpslow-s r-t 20 sqlslow.log

Get the 20 sql with the highest average number of visits

Mysqldumpslow-s ar-t 20 sqlslow.log

Get 20 sql that have the most average visits and contain ttt characters

Mysqldumpslow-s ar-t 20-g "ttt" sqldlow.log

Note:

1. If a-bash: mysqldumpslow: command not found error occurs, execute

Ln-s / usr/local/mysql/bin/mysqldumpslow / usr/bin

2. If the following error occurs, Died at / usr/bin/mysqldumpslow line 161, chunk 405659. Indicates that the sql log you want to analyze is too large. Please split it and then analyze it.

The command to split is:

Tail-100000 mysql-slow.log > mysql-slow.20180725.log are all the contents of mysql's method of opening slow query. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.