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

This article mainly introduces "the process of Mysql slow query log". In daily operation, I believe many people have doubts about the process of Mysql slow query log. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Mysql slow query log process". Next, please follow the editor to study!

The mysql slow query log is very useful for tracking problematic queries, and can analyze the resource-consuming sql statements in the code implementation, which has a high reference for the optimization of our program. This article focuses on the opening of slow query logs and log analysis, which is also a crucial step in the general steps of optimizing SQL programs.

1.mysql slow query log

It is easy to open the slow log of mysql. You only need to add it under [mysqld] in the configuration file of mysql (windows system is my.ini,linux system is my.cnf).

Java code

Log-slow-queries=mysql_slow.log

Long_query_time=3

Where log-slow-queries is the file name of the log, you can specify a directory. For example, log-slow-queries = D:\ mysql_slow.log; long_query_time defines the length of the query as slow query and records it in the file specified by log-slow-queries. Here, we define the query for more than 3 seconds to record.

Restart the Mysql server after the configuration is completed, and execute show variables like'% slow%'; to check whether the slow query log is enabled. If slow_query_log and log_slow_queries are displayed as on, it means that the server's slow query log has been

It's open. As shown below:

Java code

Mysql > show variables like'% slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_slow_queries | ON |

| | slow_launch_time | 2 | |

| | slow_query_log | ON |

| | slow_query_log_file | mysql_slow.log |

+-+ +

4 rows in set (0.00 sec)

Slow_launch_time has nothing to do with slow log. It represents a threshold of thread create. If you want to see long_query_time, you can use

Java code

Mysql > show variables like'% long%'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 3.000000 | |

+-+ +

1 row in set (0.00 sec)

Analysis of 2.mysql slow query Log

Create the table as shown in the following figure. Do not index the text when building the table, otherwise the query may occur in less than 3 seconds, and the data table is filled with 4194304 pieces of data.

Let's execute a query that takes more than 3 seconds, as follows:

Java code

Mysql > select * from wei where text='orange'

+-+ +

| | id | text |

+-+ +

| | 4103519 | orange |

+-+ +

1 row in set (3.79 sec)

Execute one more than 3 seconds and one that does not exceed 3 seconds:

Java code

Mysql > select * from wei where text='xishizhaohua'

Empty set (3.82 sec)

Java code

[mysql > select * from wei where id=4564

+-+ +

| | id | text |

+-+ +

| | 4564 | yyyyyyyyyyyyyyyyyy |

+-+ +

1 row in set (0.02 sec)

You can see how many slow queries there are in this session with the following command:

Now we can check mysql_slow.log (win7 defaults to C:\ ProgramData\ MySQL\ MySQL Server 5.1\ data). The content is as follows, which is relatively clear, including the statement and time spent on the query, as well as the timestamp of the query. Where Rows_examined is the number of rows checked, it is also very helpful for us to optimize.

# Time: 121017 17:38:54

# User@Host: root [root] @ localhost [127.0.0.1]

# Query_time: 3.794217 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 4194304

SET timestamp=1350466734

Select * from wei where text='orange'

# Time: 121017 17:46:22

# User@Host: root [root] @ localhost [127.0.0.1]

# Query_time: 3.819219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4194304

SET timestamp=1350467182

Select * from wei where text='xishizhaohua'

At this point, the study of "the process of Mysql slow query 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.