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

This article mainly introduces a kind of log record provided by MySQL-slow log query, which involves things, learned from the theoretical knowledge, there are many books and documents for your reference, from a practical point of view, accumulated years of practical experience can be shared with you.

Slow log query

MySQL's slow log query is a kind of logging provided by MySQL, which uses statements recorded in MySql that the response time exceeds the threshold. SQL whose specific running time exceeds the long_query_ time value will be recorded in the slow log. The default time for long_query_time is 10, which means to run statements above 10.

For example, if a sql executes for more than 5 seconds, even if the slow SQL,MySQL records the sql for more than 5 seconds, we can conduct a comprehensive analysis with explain.

By default, the slow query log is not enabled in the MySQL database, so we need to set this parameter manually. Of course, it is generally not recommended to enable this parameter if it is not needed for tuning, because slow logging will have a more or less performance impact.

Whether or not to enable and set

# check whether it is enabled or not

Show variables like'% slow_query_log%'

# enable

Set global slow_query_log = 1

Use set global slow_query_log=1 to enable the slow query log, which only works for the current database. If MySQL is restarted, it will become invalid. If you want to take effect permanently, you must modify the configuration file my.cnf.

Note that after setting the slow query threshold time, you may not see that the value has changed, that is, it does not take effect, and you need to reconnect or open a new session to see the modified value.

Show variables like'% long_query_time%'

Or you can use the following command without reopening the connection:

Show variables like'% long_query_time%'

Which sql will be slow logged is controlled by the parameter long_query_time. By default, the value of long_query_time is 10 seconds. Command:

Show variables like'% long_query_time%'

If the elapsed time is exactly equal to long_query_time, it will not be recorded. That is, in mysql, it is judged that it is greater than long_query_time, not greater than or equal to.

Check the number of slow logs

Show global status like'% slow_queries%'

Log analysis tool mysqldumpslow

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

For example:

# get the 10 SQL with the most returned recordset

Mysqldumpslow-s r-t 10 D:\ Program Files\ mysql\ data\ DESKTOP-VN2D5OU-slow.log

# get the 10 most visited SQL

Mysqldumpslow-s c-t 10 D:\ Program Files\ mysql\ data\ DESKTOP-VN2D5OU-slow.log

# get the first 10 queries sorted by time that contain left joins

Mysqldumpslow-s t-t 10-g "left join" D:\ Program Files\ mysql\ data\ DESKTOP-VN2D5OU-slow.log

# in addition, it is recommended to use these commands in combination with more, otherwise blasting may occur.

Mysqldumpslow-s r-t 10 D:\ Program Files\ mysql\ data\ DESKTOP-VN2D5OU-slow.log | more

Parameter meaning

S: indicates how it is sorted

C: number of visits

L: lock time

R: returns the record

T: query time

Al: average lock time

T: returns the data of the previous number

G: followed by a regular expression

Sql Analysis with show profile

The show profile command analyzes the resource consumption of statement execution in the current session. Used to find SQL time-consuming bottlenecks. It is off by default, and the results of the last 15 runs are saved.

Check whether it is turned on (show variables like 'profiling';)

Enable the function (set profiling = on;)

When enabled, you can record the operation of the next sql. Then use show profiles to view the results:

Further analyze the execution of a SQL statement through the command (show profile cpu, block io for query 3;), such as the case of SQL 3 below.

Some parameters after Show profile:

All: displays all the cost information

Block io: display block IO related overhead

Context switches: context switching related overhead

Cpu: displaying cpu-related overhead

Memory: displays memory-related overhead

Source: displays cost information related to source_function,source_file,source_line

Global query log

(never open it in a production environment, view all executed SQL statements)

Setup commands:

Set global general_log = 1

# output in table form

Set global log_output = 'TABLE'

After that, the SQL statements executed by mysql will be logged to the mysql.genearl_log table, which can be viewed with the following command:

Select * from mysql.general_log

It can also be configured in the configuration file, with the following settings:

# enable

General_log = 1

# record the path of the log file

General_log_file = D://path/logfile

# output format

Log_output=file

Read the above MySQL provides a kind of log record-slow log query introduction, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.