Summary of MySQL slow log practice

Slow log query function

The main function of slow log query is to record the query statements in SQL statements that exceed the set time threshold. For example, for a query SQL statement, we set the threshold to 1s. When the execution time of this query statement exceeds 1s, it will be written to the slow query configuration log.

Slow query is mainly for us to optimize the SQL statement.

Slow query configuration item description

Log in to the mysql service and use the following command

mysql> show variables like '%query%';
+------------------------------+-----------------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+------------------------------+-----------------------------------------+
13 rows in set (0.01 sec)

Here, we only need to focus on three configuration items.

1.slow_query_log

This configuration item determines whether to enable the slow log query function. The configuration value is ON or OFF.

2.slow_query_log_file

This configuration item is a record file for slow log queries and needs to be created manually.

3.long_query_time

This configuration item sets the time threshold for slow log queries. When this threshold is exceeded, the slow log will be recorded. The configuration value is 0 (all SQL statements are recorded) or > 0 (specific threshold). This configuration item is in seconds and can be set to decimals.

4. Log-queries-not-using-indexes

This configuration item is used to record SQL statements that do not use indexes.

How to configure slow queries

There are two ways to configure the slow query function. One is to use the MySQL configuration file, and the other is to use the MySQL command. It is recommended to use the configuration file because in the process of command configuration, it is found that sometimes the configuration item is successful when the set command is used, but the query is still not set.

1. Configuration file configuration

// Find [mysqld] and add the following code below it.
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=0
log-queries-not-using-indexes = 1
// After configuration, restart the mysql service

2. Use command configuration

// Here we only need one configuration item, and other configuration items are configured in this waymysql> set slow_query_log=ON;

After configuration, check whether the MySQL slow query log is configured successfully.

mysql> show variables like '%query%';
+------------------------------+-----------------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 0.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+------------------------------+-----------------------------------------+
13 rows in set (0.01 sec)

How to view slow log records

Before configuring slow query, we have imported the sample data file, so we will not do a separate demonstration here. MySQL official database example. Next, we will start to perform query operations.

mysql> select * from city where city='Salala';
+---------+--------+------------+---------------------+
| city_id | city | country_id | last_update |
+---------+--------+------------+---------------------+
| 444 | Salala | 71 | 2006-02-15 04:45:25 |
+---------+--------+------------+---------------------+
1 row in set (0.01 sec)

At this point, we configured the slow query log file /usr/local/mysql/var/localhost-slow.log and found that the file records the above command operations.

# Time: 2019-01-17T08:12:27.184998Z
# User@Host: root[root] @ localhost [] Id: 4
# Query_time: 0.002773 Lock_time: 0.001208 Rows_sent: 1 Rows_examined: 600
SET timestamp=1547712747;
select * from city where city='Salala';

Appeal file configuration content description

1.Time

The time when the log is recorded

2.User@Host

MySQL login user and login host address

3. Query_time row

The first time is the query time, the second is the table lock time, the third is the number of rows returned, and the fourth is the number of rows scanned.

4.SET timestamp

This one is the time of MySQL query

5.sql statement

This line is very obvious, it represents the SQL statement we executed

Summarize

Since we configure long_query_time=0, all SQL statements will be recorded. Here we assume, just assume. We set long_query_time=5, but in the third item above, Query_time is greater than 5. If it is not within the normal range in the actual project, it needs to be optimized. Of course, there are many ways to optimize it. Let's use a simple index method to optimize it.

Optimize SQL statements

1. First check the original sql statement execution structure

mysql> explain select * from city where city='Salala'\G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: city
  partitions: NULL
     type: ALL
possible_keys: NULL
     key: NULL
   key_len: NULL
     ref: NULL
     rows: 600
   filtered: 10.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> create index idx_cityName on city(`city`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

It can be seen that the SQL statement performs a full scan. We use the index to simply optimize it.

2. Create an index

mysql> create index idx_cityName on city(`city`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. Analyze again with explain

mysql> explain select * from city where city='Salala'\G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: city
  partitions: NULL
     type: ref
possible_keys: idx_cityName
     key: idx_cityName
   key_len: 152
     ref: const
     rows: 1
   filtered: 100.00
    Extra: NULL
1 row in set, 1 warning (0.00 sec)

By creating an index, we found that only one row was scanned at this time, and the index scan was used, which greatly improved the efficiency of a MySQL query.

Summary of MySQL slow query usage

In normal development, slow query is very useful as a way to optimize MySQL. It will record some of our sql statements with long query time, and we will analyze them to optimize the sql query statements. However, after the slow log query is turned on, the relevant records for the SQL query will be written to the disk file through disk I/O, which increases the disk I/O read and write. Therefore, we use this function in development and testing environments, not in production environments.

Slow log query tool

The later the slow log query file is, the more content it contains. The more pressure we have to analyze it, the more we need to use some tools to achieve fast analysis. I am not yet fully familiar with these tools. I will write a separate article to introduce this type of tools later. Here I will just list the tool names.
1.mysqldumpslow
2.pt-query-digest
3.mysqltop (Lepus)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.