How to optimize MySQL performance through MySQL slow query

As the number of visits increases, the pressure on the MySQL database increases. Almost all web applications using the MySQL architecture will have performance problems on the database. It is very useful to track problematic queries through the MySQL slow query log, which can be used to analyze the SQL statements in the current program that consume a lot of resources.

We can enable the slow query log through the my.cnf file. Let's take a look at the meaning of the relevant parameters.

log-slow-queries <slow_query_log_file>

File that stores slow query logs. You must ensure that the mysql server process mysqld_safe process user has w permissions for 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. 10 seconds is too long for most applications. We recommend starting from 3 seconds and reducing it gradually, each time finding the 10 most "expensive" SQL statements and optimizing them. Day by day, optimizing step by step. Finding many SQL statements at one time does not make much sense for optimization.

log-queries-not-using-indexes

MySQL records queries that do not use indexes in the slow query log. No matter how fast it executes, a query that does not use an index will be logged. Sometimes, queries that do not use indexes are very fast (such as scanning a small table), but they may 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. For example, OPTIMEZE TABLE , ALTER TABLE , etc.

Enable slow query

Method 1: Find the MySQL configuration file my.cnf on the server, and then add the following content to the mysqld module

log_slow_queries = NO
log-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. This is to check the slow query log through the following command:

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

Method 2: Modify the global variables of mysql. The advantage of this is that you don’t need to restart the mysql server. You can just log in to mysql and execute the sql script.

set global slow_query_log=ON;

set global long_query_time=3;

Then run the following command to check if it is successful

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 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)

Analyzing slow query logs

Method 1: By viewing the slow query log analysis of MySQL, for example, we can tail -f slow_query.log to view the content and the meaning of the fields

# 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 line, the time when the SQL query was executed
• The second line shows the connection information for executing SQL queries
• The third line records some useful information
• Query_time SQL execution time, the longer it is, the slower it is.
• Lock_time Waiting time for table locks at the MySQL server stage (not at the storage engine stage)
• Rows_sent The number of rows returned by the query
• Rows_examined The number of rows examined by the query

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

mysqldumpslow -sc -t 10 /tmp/slow-log

This will output the top 10 SQL statements with the most records, where:

-s, indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of records returned, respectively. ac, at, al, and ar indicate the corresponding reverse sorting. -t, means top n, which means the number of records returned. -g, can be followed by a regular expression matching pattern, which is case-insensitive.

for example

/path/mysqldumpslow -sr -t 10 /tmp/slow-log

Get the top 10 queries that return the most records.

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

Get the first 10 query statements containing left joins sorted by time.

Shortcomings of slow query logs

Although recording slow queries can help you optimize your product. However, the current version of MySQL still has several shortcomings.

1. In MySQL 5.0, 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, appearing in 1 second is of little significance. That is, there are relatively few queries that take 1 second. It was not until MySQL 5.1.21 that a more fine-grained long_query_time setting was provided.

2. Not all queries executed by the server can be recorded in the slow log. Although MySQL normal log records all queries, they are recorded before parsing the query. This means that normal logs cannot contain information such as execution time, table lock time, number of rows checked, etc.

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. These fast and efficient full table scan queries (small tables) will wash out the truly useful slow query records. Queries such as select * from category will also be recorded. With the log_queries_not_using_indexes option turned on, the slow query log will be filled with too many junk log records. These fast and efficient full table scan queries (small tables) will wash out the truly useful slow query records. Queries such as select * from category will also be recorded.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links