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

This article mainly introduces the mysql record time-consuming sql sentence example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Mysql records time-consuming sql

Mysql can record time-consuming sql or unindexed sql in slow log for optimization analysis. This article mainly introduces the mysql record time-consuming sql examples of the relevant information, here provides the implementation method, the need friends can refer to, hope to help you.

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=1log-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/mysqllog_bin=/tmp/mysql/bin-log/mysql-bin.loglog_bin=ONsocket=/var/lib/mysql/mysql.sockuser=mysql# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package). Old_passwords=1log_slow_queries=/tmp/127_slow.loglong_query_time=1log_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 > Thank you for reading this article carefully I hope the article "sample Analysis of sql sentences in mysql record time" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.