Skip to content
ELECTRONICS ENGINEERINGelectronics-engineering.com
Information Systems MySQL PHP

Tracing and Optimising Slow Queries in PHP and MySQL

Improving the handling of slow queries by monitoring log files and optimising times You may have to manage databases with tables of several tens of megabytes…

https://www.electronics-engineering.com/en/tracing-and-optimising-slow-queries-in-php-and-mysql/

Was this page useful? Rate it:
average 5.0 from 1 rating

Improving the handling of slow queries by monitoring log files and optimising times

You may have to manage databases with tables of several tens of megabytes (MB) and run into slow execution of search queries in MySQL. In this article we look at strategies to optimise search queries in order to balance the workload of our dedicated server or VPS. In particular, when we notice that our system is bogged down and that the mysqld process is consuming a lot of resources, it is important to consider some corrective actions. What should you do in such situations?

Here are some valuable tips:

  1. Trace the slow queries.
  2. Check that the error log of our site contains no errors caused by programming defects.
  3. Avoid persistent connections to the database.
  4. Reduce the timeout.
  5. Increase the maximum number of connections in MySQL.
  6. Increase the number of MaxClients in the web server (for example, Apache).
  7. Optimise the database and the slow search queries.

Tracing slow queries is essential to identify inefficiencies. To enable tracing, just edit the MySQL configuration file (usually /etc/my.cnf) and add the following lines in recent versions:

long_query_time = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/log-slow-queries.log

After making these changes and creating the folder /var/log/mysql/ (with the command mkdir /var/log/mysql/) and the log file (with the command touch /var/log/mysql/log-slow-queries.log), assign the correct permissions (with the command chown mysql:mysql /var/log/mysql/log-slow-queries.log). Then restart the mysqld service (with the command /etc/init.d/mysqld restart) and start examining the slow search queries (with the command tail /var/log/mysql/log-slow-queries.log -f).

If you notice that there are a lot of queries, you can stop the log output with CTRL+C and raise the value of long_query_time from 0 to, for example, 0.3 to see only the slowest queries.

An analysis of the error log is essential to check for unhandled programming errors (for example, mysql_query($nomeQuery); should be handled as $result = @mysql_query($nomeQuery); if ($result) ...) or for warning messages that are not displayed in production, so that users do not see minor errors that do not prevent the use of the site or product.

With the new PHP 5.3.x, many functions have become deprecated (for example ereg, session_register, etc.) and will no longer be available from version 6. It is therefore essential to examine the error log to find where changes are needed.

One of the things that can make a database almost unusable is persistent connections, which should be avoided unless strictly necessary. Instead of the persistent MySQL connection (mysql_pconnect), it is better to use mysql_connect and, if possible, disconnect when connections are no longer needed. You can also reduce the timeout and the maximum number of connections by editing the MySQL configuration file (usually /etc/my.cnf):

max_connections = 500
wait_timeout = 25

After making these changes, restart the mysqld service (with the command service mysqld restart).

If these changes are not enough, you can increase the number of MaxClients in the web server configuration file (for example /etc/httpd/conf/httpd.conf). The optimal amount varies with the version of the operating system, but you can raise MaxClients in the prefork MPM and worker MPM modules, for example to 256 in the prefork MPM and 300 in the worker MPM if the machine is powerful enough.

If the situation does not improve despite all these changes, you need to examine the slowest queries carefully. Often, in large tables, the slowest queries involve the use of the LIKE operator on CHAR, VARCHAR or TEXT fields. In that case it is advisable to use index-based full-text search. To do so, you must alter the table by adding the necessary full-text indexes. For example:

ALTER TABLE tabellona ADD FULLTEXT(comune);

After creating the full-text index, you can rewrite the search query like this:

$query = "SELECT * FROM tabellona WHERE MATCH(comune) AGAINST('$comune')";

You will notice that this change greatly reduces the execution time of the query and lets your server handle a higher workload.

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
1
0
Would love your thoughts, please comment.x
()
x