Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is recorded in mysql's slow query log". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what is recorded in the slow query log of mysql.

In mysql, the slow log records statements whose response time exceeds the threshold; the response time threshold is the value where the running time exceeds the "long_query_time" value, and the default value is 10, that is, slow log records run SQL statements for more than ten seconds. Slow log writes log records to log files and database tables.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is recorded in mysql's slow query log?

The slow query of MySQL, whose full name is the slow query log, is a kind of logging provided by MySQL, which is used to record statements whose response time exceeds the threshold in MySQL.

In a specific environment, SQL statements that run longer than the long_query_ time value are recorded in the slow query log.

The default value for long_query_time is 10, which means to record statements that run for more than 10 seconds.

By default, the MySQL database does not start slow query logs, and you 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 turning on the slow query log will have a more or less performance impact.

Slow log support for writing log records to log files and database tables.

Parameter description

Slow_query_log: whether to enable slow query log. 1: enable, 0: disable.

Log-slow-queries: the storage path of slow query logs in MySQL database in the old version (version 5.6 or later). If you do not set this parameter, the system will default to a default file host_name-slow.log

Slow-query-log-file: the storage path of slow query logs in the new version (version 5.6 and above) of MySQL database. If you do not set this parameter, the system will default to a default file host_name-slow.log

Long_query_time: slow query threshold. Log is recorded when the query time exceeds the set threshold.

Log_queries_not_using_indexes: queries that do not use indexes are also recorded in the slow query log (optional).

Log_output: log storage method. Log_output='FILE' means to save the log to a file, and the default value is' FILE'. Log_output='TABLE' means to store the log in the database.

Expand knowledge:

1. Check the status of the slow log feature. The slow log feature defaults to show variables like'% slow_query_log%' which is disabled.

OFF indicates that slow logging is turned off, slow logging is stored in the DESKTOP-2331B4V-slow.log file, and DESKTOP is my computer name.

2. Enable slow log function set global slow_query_log=1

Then query the slow log status

The status is ON, which indicates that the slow log feature is enabled.

3. View the default long_query_time time

The long_query_time is 10 seconds, which means that only if the SQ statement exceeds 10 seconds will it be recorded in the slow log.

4. Set the long_query_time duration. The default 10 seconds is too long for set global long_query_time=3.

Re-open a session, and if it is a window system, you can clock in another cmd (note that if you are still querying the long_query_time length in the session that sets the long_query_time, it is still 10 seconds by default, so restart a session), and query the long_query_time duration again

At this point, I believe you have a deeper understanding of "what is recorded in the slow query log of mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.