Mysql optimization: enabling slow queries and analyzing causes

Method 1: modify the configuration file Windows: the configuration file my. ini, usually in the MySQL installation directory or c: Windows. Linux: the configuration file for Linux is my. cnf, usually in my. add several lines to ini: [mysqlld] long_query_time2 # log-slow-que

Method 1: modify the configuration file Windows: Windows to my. ini, which is usually in the MySQL installation directory or c: \ Windows. Linux: the configuration file for Linux is my. cnf, usually in my. add several lines to ini: [mysqlld] long_query_time = 2 # log-slow-que

Method 1: modify the configuration file

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

Linux: the configuration file of Linux is my. cnf, which is usually under/etc.

Add several lines in my. ini:

[Mysqlld] long_query_time = 2 # log-slow-queries = "mysql_slow_query.log" #5.0 and later versions: slow-query-log = Onslow_query_log_file = "mysql_slow_query.log" log-query-not-using-indexes

The first clause defines how many seconds the query has been queried as a slow query. Here I define 2 seconds.

The second sentence defines the path for slow query logs (because it is windows, no permission issues are involved)

The third sentence is to record the query without an index.

Step 2: view the slow query status

Method 2: Enable slow query through MySQL database

The preceding configuration takes effect only when the mysql server process mysqld is restarted. However, in many cases, especially in the product operation environment, you do not want to restart the mysql server every time you modify it, or you want to record it at a specific time. MySQL5.1 provides more flexible runtime control, so that you do not have to restart the mysql server, you can also selectively record or not record some slow queries.

In MySQL5.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 record
# Note: If the slow_query_log global variable is set, log_slow_queries will also be implicitly changed.
Mysql> set global slow_query_log = ON

Unfortunately, similar global variables are not provided for flexible control in MySQL5.0, but we can set long_query_time to be large enough to avoid recording certain query statements. For example
Mysql> set global long_query_time = 3600;

Mysql> set global log_querise_not_using_indexes = ON;

If MySQL5.0 is not a service, you can convert the log file into a symbolic link (/dev/null) without logging ). Note: you only need to run flush logs after the change to determine that MYSQL releases the current log file descriptor and record the log to/dev/null.

Different from MySQL5.0, MySQL5.1 can change the log behavior at runtime and record logs to database tables. Set the mysql global variable log_output to TABLE. MySQL records the logs to the mysql. gengera_log and mysql. slow_log tables respectively. However, we recommend that you record logs in the log file.
Mysql> show variables like 'Log _ output' \ G
Variable_name: log_output
Value: FILE
Mysql> set global log_output = 'table ';
Defects and review

Although slow query is recorded, it can help you optimize the product. However, the current MySQL version has a few advantages.
1. For MySQL, the time granularity of long_query_time is not small enough. The minimum value is 1 second. 1 second is of little significance for web scripts with high concurrency performance. That is, the number of queries per second is relatively small. More fine-grained long_query_time setting is not provided until mysql5.1.21.
2. All queries executed by the server cannot be recorded in slow logs. Although MySQL common logs record all queries, they are recorded before resolution query. This means that common logs cannot contain information such as execution time, table lock time, and number of checked rows.
3. if the log_queries_not_using_indexes option is enabled, the slow query log will be filled with too many junk log records. These fast and efficient full table scan queries (small tables) will erase the useful slow queries records. For example, queries such as select * from category will also be recorded.

With the microslow-patch, you can use a finer time granularity and record all executed SQL statements. However, you have to compile MySQL by yourself when using this subscription. For stability test, we recommend that you install this patch in the development and testing environment to enjoy the convenience of this patch. Try not to do this in the operating environment...

Step 2. Check whether the slow query is enabled and run the following SQL statement to check the mysql slow query status.
The execution result prints information such as whether to enable slow query, slow query seconds, and slow query logs on the screen.