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

With the increase of the number of visits, the pressure on the MySQL database becomes greater. Almost most web applications using MySQL architecture will have performance problems on the database. It is very useful to track problematic queries through the mysql slow query log. We can analyze the resource-consuming sql statements in the current program.

Slow log can be enabled by setting the my.cnf file. Let's first take a look at the meaning of the relevant parameters.

Log-slow-queries

The file that holds the slow query log. You must make sure that the mysql server process mysqld_safe process user has w permissions on the file.

Long_query_time

If the query time exceeds this value, it is considered a slow query and is recorded. The unit is seconds, the minimum value is 1, and the default value is 10 seconds. Ten seconds is too long for most applications. We recommend starting at 3 seconds, reducing them in turn, finding the 10 most "expensive" SQL statements each time and optimizing them. Day after day, optimize step by step. Finding many SQL statements at once is of little significance for optimization.

Log-queries-not-using-indexes

MySQL logs queries that do not use indexes in the slow query log. No matter how fast it executes, the query statement is logged without using an index. Sometimes, some queries that do not use indexes are very fast (such as scanning very small tables), but it can also cause the server to slow down and even use a lot of disk space.

Log-slow-admin-statements

Some management instructions will also be recorded. Such as OPTIMEZE TABLE, ALTER TABLE and so on.

Open slow query

Method 1: find the configuration file my.cnf of mysql on the server, and then append the content to the mysqld module

Log_slow_queries = NOlog-slow-queries = / var/run/mysqld/slow_querys.log long_query_time = 3 log-queries-not-using-indexes log-slow-admin-statements

Then restart the mysql server, which is to take a look at the slow log with the following command:

Tail-f / var/run/mysqld/slow_querys.log

Method 2: deal with it by modifying the global variables of myssql. The advantage of this is that you don't have to restart the mysql server and log in to mysql to execute the sql script.

Set global slow_query_log=ON;set global long_query_time=3

Then use the command to see if it is successful.

Mysql > show variables like 'long%' +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (sec) mysql > show variables like 'slow%' +-+-+ | Variable_name | Value | +-+-+ | slow_launch_time | 2 | slow_query_log | ON | | slow_query_log_file | / tmp/slow .log | +-+-+ 3 rows in set (0.00 sec)

Analyze slow query log

Method 1: by looking at the slow query log analysis of mysql, for example, we can tail-f slow_query.log to see the content and field meaning in it.

# Time: 110107 16:22:11 # User@Host: root [root] @ localhost [] # Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774 SET timestamp=1294388531; select count (*) from ep_friends The first row, the second row of SQL query execution time, the third row of connection information that executes SQL query records some useful information about us. The longer the Query_time SQL execution time, the slower the Lock_time waits for the table lock time in the MySQL server phase (not in the storage engine phase). The number of rows returned by the Rows_examined query

Method 2: use the mysqldumpslow command to analyze, for example

Mysqldumpslow-s c-t 10 / tmp/slow-log

This outputs the 10 SQL statements with the most records, of which:

-s indicates how to sort, c, t, l, r are sorted by the number of records, time, query time, and the number of records returned, respectively. Ac, at, al and ar represent the corresponding flashbacks;-t means top n, that is, how many items of data are returned;-g, you can write a regular matching pattern after that, which is case-insensitive.

such as

/ path/mysqldumpslow-s r-t 10 / tmp/slow-log

Get the 10 queries that return the most recordsets.

/ path/mysqldumpslow-s t-t 10-g "left join" / tmp/slow-log

Get the query statements with left links in the first 10 items sorted by time.

The deficiency of slow query log

Although documented slow query can help you optimize the product. But there are still several deficiencies in the current version of MySQL.

In 1.MySQL5.0 version, the time granularity of long_query_time is not fine enough, and the minimum value is 1 second. For web scripts with high concurrency performance, the appearance of 1 second does not make much sense. That is, fewer queries appear for 1 second. Finer-grained long_query_time settings are not available until mysql5.1.21.

two。 All queries executed by the server cannot be logged to a slow log. Although the MySQL normal log records all queries, they are recorded before the query is parsed. This means that ordinary logs cannot contain information such as execution time, table locking time, number of rows checked, and so on.

3. If the log_queries_not_using_indexes option is turned on, the slow query log will be filled with too many junk log records, and these fast and efficient full table scan queries (small tables) will flush out the really useful slow queries records. Queries such as select * from category are also recorded. When the log_queries_not_using_indexes option is turned on, the slow query log will be filled with too many junk log records, and these fast and efficient full table scan queries (small tables) will flush out the really useful slow queries records. Queries such as select * from category are also recorded.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.