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

Slow log query action

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

Slow query is mainly for us to do sql statement optimization function.

Slow Query Configuration Item Description

Log in to the mysql service using 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 configured value is ON or OFF.

2.slow_query_log_file

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

3.long_query_time

This configuration item is to set the time threshold for slow log query. When this threshold is exceeded, slow log will be recorded. The configured value is 0(any sql statement is 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 are not used to index.

How to configure slow queries

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

1. profile configuration

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

2. Use Command Configuration

//Here is a simple configuration item on the line, the other configuration items are configured according to this method mysql> set slow_query_log=ON;

After configuration, check whether 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 logging

Before configuring slow queries, we have imported the sample data file, so we won't do a separate demonstration here. MySQL official database example. Next, we will start to do 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 found that the file/usr/local/mysql/var/localhost-slow.log records the above command operations according to the configured slow query log file.

# 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: 600SET timestamp=1547712747;select * from city where city='Salala';

Description of appeal file configuration content

1.Time

Time logged by this log

2.User@Host

MySQL Logged-in user and logged-in host address

3. Query_time line

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

4.SET timestamp

This one is MySQL query time

5.sql statement

This line is obvious, indicating the SQL statement we executed.

summary

Since we configured long_query_time=0, all sql statements will be logged, assuming, just assuming. We set long_query_time=5, but the Query_time in the third item above is greater than 5. If it does not belong to the normal range in the actual project, it needs to be optimized. Of course, there are many ways to optimize it. Below we use a simple index method to optimize it.

tuning SQL statements

1. First look at 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 600 filtered: 10.00 Extra: Using where1 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 is clear that the sql statement was scanned completely. Let's simply optimize it with indexes.

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 once with explain

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

By creating the index, we found that only one row was scanned at this time, and it was the index scan used, thus greatly improving the efficiency of a mysql query.

MySQL Slow Query Usage Summary

In normal development, slow queries are useful as a way to optimize MySQL. It will record some of our SQL statements with long query time, and we will analyze their statements to optimize SQL query statements. However, after the slow log query is enabled, the sql query will write the relevant records to the disk file through disk I/O, adding a disk I/O read and write. Therefore, we use this functionality in development and test environments rather than in production environments.

Slow Log Query Tool

Because of the slow log query file to the later, the more content. The more pressure we put on it to analyze, the more tools we need to do it quickly. These tools are not fully familiar with the use of late to write a separate article on the type of tool, here is just a list of tool names.

1.mysqldumpslow

2.pt-query-digest

3. mysqltop (Lepus)

The above is all the content of this article, I hope to help everyone's study, but also hope that everyone a lot of support.

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.