MySQL starts slow SQL and analyzes the causes

Step 1. Enable MySQL slow query

Method 1: Modify the configuration file

Windows: The Windows configuration file is my.ini, which is usually in the MySQL installation directory or c:\Windows.

Linux: The Linux configuration file is my.cnf, usually in /etc

Add a few lines to my.ini:

[mysqlld] 
long_query_time=2 
#5.0, 5.1 and other versions have the following configuration options log-slow-queries="mysql_slow_query.log" 
#5.5 and above versions configure the following option slow-query-log=On 
slow_query_log_file="mysql_slow_query.log" 
log-query-not-using-indexes

The first sentence is used to define how many seconds it takes to be considered a slow query. I define it as 2 seconds here.

The second sentence is used to define the path of the slow query log (because it is Windows, there is no permission issue involved)

The third sentence is to record the query that does not use the index

Step 2: Check the status of slow queries

Method 2: Enable slow query through MySQL database

The above configuration needs to restart the mysql server process mysqld to take effect. But many times, especially in product operating environments, you don't want to restart the MySQL server every time you make a change, and you also want to record it at certain specific times. MySQL 5.1 provides us with more flexible runtime control, so that you do not have to restart the MySQL server, you can selectively record or not record certain slow queries.

In MySQL 5.1, the global variables slow_query_log and slow_query_log_file are provided to flexibly control enable/disable slow queries. You can also set the time through long_query_time

#//Enable slow query logging#Note: If the slow_query_log global variable is set, log_slow_queries will also change implicitlymysql>set global slow_query_log=ON

Unfortunately, MySQL 5.0 does not provide similar global variables for flexible control, but we can avoid recording certain query statements by setting long_query_time to a large enough value. for example

mysql>set global long_query_time = 3600;

mysql>set global log_querise_not_using_indexes = ON;

In MySQL 5.0, if you do not want to record logs when the service is not shut down, you can make the log file a symbolic link to /dev/null. Note: You only need to run FLUSH LOGS after the change to make sure MySQL releases the current log file descriptor and re-logs to /dev/null

Unlike MySQL 5.0, MySQL 5.1 can change the journaling behavior at runtime and record logs in database tables. Just set the mysql global variable log_output to TABLE. MySQL will record logs in two tables: mysql.gengera_log and mysql.slow_log. However, we recommend logging in a journal file.

mysql> show variables like 'log_output'\G
Variable_name: log_output
Value: FILE
mysql>set global log_output='table';

Defects and audits

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.

The microslow-patch patch allows you to use finer time granularity and record all executed SQL statements. However, to use this patch you have to compile MySQL yourself. For stability reasons, we recommend that you apply this patch in a development and testing environment to enjoy the convenience it brings. Try not to do this in an operational environment...

Step 2. Verify that slow query is enabled

Execute the following SQL statement to view the status of MySQL slow query

The execution result will print on the screen information such as whether slow query is enabled, the number of seconds for slow query, slow query log, etc.

/* Check slow query time */ 
show variables like "long_query_time"; default 10s 
/* Check the slow query configuration */ 
show status like "%slow_queries%"; 
/*View slow query log path*/ 
 show variables like "%slow%";

Step 3: Execute a slow query operation

In fact, it is difficult to execute a meaningful slow query, because when I tested it myself, even if I queried a massive table with 200,000 data records, it only took a few seconds. We can replace it with the following statement:

SELECT SLEEP(10);

Step 4: Check the number of slow queries

Use the following SQL statement to view how many slow queries have been executed:

show global status like '%slow%';

Configuration of mysql log:

Note: These daily files will only be generated when mysql is restarted #Record all sql statements log=E:/mysqllog/mysql.log 
#Record database startup and shutdown information, as well as error information generated during operation log-error=E:/mysqllog/myerror.log 
# Record all SQL statements except select statements into the log, which can be used to restore the data file log-bin=E:/mysqllog/bin 
#Record slow query sql statements log-slow-queries=E:/mysqllog/slow.log  
#Slow query time long_query_time=2

Step 4: Analyze the slow query log

Method 1: Analyze through tools

MySQL comes with the mysqldumpslow tool for analyzing slow query logs. In addition, there are some useful open source tools. For example, MyProfi (download address: http://sourceforge.net/projects/myprofi/), mysql-log-filter, and of course mysqlsla

The following is a description of the common parameters of mysqldumpslow. For detailed information, you can use mysqldumpslow -help to query.

• -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 (from large to small). ac, at, al, and ar are sorted in reverse order.
• -t means top n, which means how many records are returned.
• -g, followed by a regular expression matching pattern, is case insensitive.

The next step is to use mysqldumpslow, a slow query tool that comes with mysql, to analyze it (in the bin directory of mysql). The name of my log file is host-slow.log.

List the top 10 SQL statements with the most records

mysqldumpslow -sc -t 10 host-slow.log

List the top 10 SQL statements that return the most records

mysqldumpslow -sr -t 10 host-slow.log

Return the first 10 SQL statements containing left joins by time

mysqldumpslow -st -t 10 -g "left join" host-slow.log

Using the mysqldumpslow command, we can clearly get the various query statements we need, which is of great help in monitoring, analyzing, and optimizing MySQL query statements.

Method 2: Directly analyze MySQL slow query logs

Part of the log is as follows:

# 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';

In fact, locating the slow query statement is enough. Run the explain or desc command to view the slow query statement, as shown below:

The problem is obvious, and the solution is also obvious: create an index.

mysql> create index text_index on wei(text);  
Query OK, 4194304 rows affected (1 min 58.07 sec)  
Records: 4194304 Duplicates: 0 Warnings: 0

Then when executing the query operation, it takes significantly less time.

mysql> select * from wei where text='orange'; 
+---------+--------+ 
| id | text | 
+---------+--------+ 
| 4103519 | orange | 
+---------+--------+ 
1 row in set (0.33 sec)

Slow Query log helps you record the SQL statements that have been executed. But it’s not a panacea and may not mean as much as you think. It only tells you which statements are slow, but why are they slow? You still need to analyze the specific reasons yourself and debug them continuously. Maybe you just need to change a more efficient SQL statement, maybe you just need to simply add an index, but it is also possible that you need to adjust the design of your application. For example, the statement above is very obvious, it checks more than 6 million rows of data. Unfortunately, not every statement is so obvious. There may be other reasons, such as:

• *The table is locked, causing the query to be in an isochronous state. lock_time shows the time the query waited for the lock to be translated
• *Data or indexes are not cached. This is common when the server is started for the first time or the server is not tuned.
• *Backup database, I/O slows down
• * Maybe other queries are running at the same time, reducing the current query

Therefore, don't be too nervous about a certain record in the log file, but review it rationally to find out the real reason. If slow queries appear frequently, special attention should be paid. If it occurs individually, just do some routine checks. We recommend that statistics and benchmark reports be compiled for comparison and elimination, which is more useful than blindly trying. I hope you don’t waste too much time and energy on this part.

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