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

This article mainly introduces "what is the meaning of MySQL slow log". In daily operation, I believe many people have doubts about what MySQL slow log means. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what does MySQL slow log mean?" Next, please follow the editor to study!

Foreword:

Slow logs are often used in daily database operation and maintenance. We can get inefficient SQL by looking at slow logs, and then we can optimize SQL. In this article, let's learn some knowledge about slow log.

1. Introduction to slow log

Slow log, known as slow query log (Slow Query Log), is mainly used to record SQL statements that have been executed for more than a specified time in MySQL. Through the slow query log, you can find out which statements are inefficient for optimization.

By default, slow logs are not enabled in MySQL. You can open slow logs by modifying the slow_query_log parameter. The parameters related to slow logs are described as follows:

Slow_query_log: whether to enable slow query log. Default is 0, which can be set to 0 and 1 to enable.

Slow_query_log_file: specify the location and name of the slow log. The default value is host_name-slow.log. You can specify an absolute path.

Long_query_time: slow query execution time threshold, after which it will be recorded. Default is 10, and unit is s.

Log_output: slow log output target, which defaults to file, that is, output to a file.

Log_timestamps: mainly controls the display time zone in error log, slow log and genera log log files. UTC time zone is used by default. It is recommended to change it to SYSTEM system time zone.

Log_queries_not_using_indexes: whether to record all query statements that do not use an index. The default is off.

Min_examined_row_limit: SQL with less than this parameter will not be recorded in the slow query log. Default is 0.

Log_slow_admin_statements: whether slow management statements are written to slow logs. Management statements include alter table, create index, and so on. Default is off, that is, no write.

In general, we only need to enable slow logging, configure the threshold time, and configure the remaining parameters by default. For the threshold time, it can be adjusted flexibly, for example, it can be set to 1s or 3s.

two。 Slow log actual combat

In the configuration file, we can set the following slow log related parameters:

# configuration related to slow log. You can modify vim / etc/my.cnf [mysqld] slow_query_log = 1 slow_query_log_file = / data/mysql/logs/slow.log long_query_time = 1 log_timestamps = SYSTEM log_output = FILE according to the actual situation.

Let's take a look at what will be recorded in the slow log. Let's execute a slow query SQL to see how it is reflected in the slow log.

# the SQL execution time exceeds the threshold # Time: 2021-05-13T17:38:03.687811+08:00 # User@Host: root [root] @ [192.168.85.0] Id: 2604943 # Query_time: 1.099889 Lock_time: 0.000144 Rows_sent: 39 Rows_examined: 45305 SET timestamp=1620898683; select * from test_table where col_name like'% Test%'

If slow query logging is enabled and FILE is selected as the output target, each statement written to the log begins with a # character. For each group of slow SQL, the first line records the execution time of the SQL (if the log_timestamps parameter is UTC, the time change will display the UTC time zone time). The second line records the user and IP who executed the statement, as well as the link id. The fields in the third line are explained as follows:

Query_time: the execution time of the duration statement, in seconds.

Lock_time: the time in seconds that duration acquired the lock.

Rows_sent: the number of rows sent to the Client side by N.

Rows_examined: the number of rows checked by the N server layer (no processing within the storage engine is counted).

The following two lines are the timestamp and specific slow SQL when this statement is executed, respectively.

In the actual environment, it is not recommended to enable the log_queries_not_using_indexes parameter, which may lead to the rapid growth of slow logs. For the screening and analysis of slow logs, we can use mysqldumpslow, pt-query-digest and other tools to analyze. Slow log files should be archived regularly, for example, you can temporarily close the slow log, rename the old file, and then open the slow log, which will be written to the new log file and effectively reduce the log size.

At this point, the study of "what is the meaning of MySQL slow log" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.