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

In this issue, the editor will bring you about how to record the time-consuming sql with mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Record time-consuming sql

Mysql can record time-consuming sql or unindexed sql in slow log for optimization analysis.

The 1.mysql slow query log is enabled:

The mysql slow log is very useful for tracking problematic queries. You can analyze the resource-consuming sql statements in the current program, so how to open the mysql slow log record?

Mysql > show variables like 'log_slow_queries'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_slow_queries | OFF |

+-+ +

1 row in set (0.01 sec)

Mysql >

This means that the slow log feature is not enabled. To enable the configuration file that needs to modify mysql, add the following two parameters to the configuration file "[mysqld]":

Long_query_time=1

Log-slow-queries=/var/mysql/logs/slow.log

Description

Long_query_time

This parameter represents the measurement time of slow query (in seconds), the minimum is 1, and the default value is 10. Any sql statement that takes longer than long_query_time to execute will be recorded in the slow query log.

Log-slow-queries [= file_name]

The file_name parameter is optional, and the default value is host_name-slow.log. If the file_name parameter is specified, mysql will log the slow query to the file set by file_name. If file_name provides a relative path, mysql will log the log to mysql's data directory. This parameter can only be added in the configuration file, not executed on the command line.

two。 Configuration that records unused index queries to slow log as well

Add the "log_queries_not_using_indexes" parameter to the startup configuration file or command line parameter of mysql to add unused index query statements to slow log.

Examples are as follows:

[root@localhost mysqlsla-2.03] # more / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Log_bin=/tmp/mysql/bin-log/mysql-bin.log

Log_bin=ON

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

Old_passwords=1

Log_slow_queries=/tmp/127_slow.log

Long_query_time=1

Log_queries_not_using_indexes

.

After restarting mysql, the check result is as follows:

Mysql > show variables like 'log_slow_queries'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_slow_queries | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 2 | |

+-+ +

1 row in set (0.00 sec)

Mysql >

The above is the editor for you to share how to use mysql to record time-consuming sql, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.