MySQL slow query and php-fpm slow log

MySQL Slow query

In web development, we often write some SQL statements, a bad SQL statement may make your entire program very slow, more than 10 seconds the average user will choose to close the Web page, how to optimize the SQL statement to run those long-running SQL statements to find out? MySQL provides us with a very good feature, that is slow query! The so-called slow query is set to record more than a certain amount of time SQL statement!

PHP-FPM Slow Log

php-fpm Slow log slowlog settings allow developers to find out which PHP processes are slow and cause problems for the site, making it easy for developers to find the problem. This method is also suitable for troubleshooting Nginx 500, 502 problem source, when Nginx received the error code, you can determine the backend php-fpm parsing php out of some kind of problem, such as execution error, execution timeout.

Lack of MySQL slow query

1.mysql5.0 version, Long_query_time time granularity is not fine enough, the minimum value is 1 seconds. For web scripting with high concurrency, the 1-second appearance is not very significant. That is, fewer queries appear for 1 seconds. Until mysql5.1.21 provides finer-grained long_query_time settings.

2. All queries performed by the server cannot be logged to the slow log. Although the MySQL normal log records all queries, they are recorded before parsing the query. This means that the normal log does not contain information such as execution time, lock table time, check row count, and so on.

3. If the log_queries_not_using_indexes option is turned on, the slow query log will be filled with excessive spam logging, and these fast and efficient full-table scan queries (small tables) will flush out really useful slow queries records. Queries such as SELECT * from category are also recorded.

1, how to open the slow query?　　

First we check if the MySQL server's slow query status is turned on.

Mysql>show variables like '%quer% ';

It is very simple to turn on slow queries, as follows:

　　　　Method One

VI/ETC/MY.CNF Note: my.cnf is a MySQL configuration file

Add a configuration statement for the slow query below mysqld ( Be sure to add it below [mysqld] )

　　　　　　　　[Mysqld]

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

Long_query_time = 1

Save exit restart MySQL

　　　　Log-slow-queries: The log storage directory that represents MySQL slow query, this directory file must have write permission.

Long_query_time:sql the maximum execution time.

　　　　Method Two

On the MySQL command line, do the following:

　　　　　　　　Set global slow_query_log=on;

　　　　　　　　　　

　　　　　　　　Set global long_query_time=1;

　　　　　　　　　　

　　Test

1. View slow log records generated at this time is empty no records

　　　　　　Cat Mysql-slow.log　

　　　　　　　　

2. Execute a SQL statement that exceeds the set time to see if it is logged

　　　　　　such as: Select Sleep (1); Go back to the slow day record.

To this MySQL slow query is OK.

2, how to open php-fpm slow log?

One of the parameters in the php-fpm.conf configuration file is described in Request_slowlog_timeout:

　　　　　　　　

When Request_slowlog_timeout is set to a specific second, Request_slowlog_timeout = 5, indicating which script execution time is greater than 5 seconds, the script is logged to the slow log file

Request_slowlog_timeout =0 indicates that slow log output is turned off.

The slow log file location defaults to the log folder in the PHP installation directory and can be specified by modifying the Slowlog = log/$pool. Log.slow parameter.

PHP-FPM Slow Log example, the slow log will record the process number, the script name, which file which line of code which function execution time is too long.

Request_slowlog_timeout and Slowlog need to be set at the same time, open request_slowlog_timeout and need to turn on Slowlog

　　　　Slow log paths need to be created manually (Slowlog)

Here are the steps to start:

　　　　　　

End.

MySQL slow query and php-fpm slow log