October 20, 2015

5 Sure-Fire MySQL Tips To Help Speed Up Your Queries

Written by Gen

keyword icon

Quick changes to your config file and other settings can make a big difference in the speed and efficiency of MySQL query processing.

Performance bottlenecks can sneak up on you, especially when you consider how little time it takes for a MySQL database to grow in size and complexity. By adjusting these five parameters you can avoid the slowdowns that frequently occur as databases age. In fact, you may be able to reach new performance heights, particularly in virtualized environments.

Time. There never seems to be enough of it in a workday. Think of all you could accomplish with those precious minutes you currently spend waiting for your MySQL queries to run. Thankfully, it doesn't take much time to tweak MySQL so that your queries are processed in a jiffy. Here are five of the handiest time-saving tips for MySQL queries.

Tip #1: Enable MySQL's slow-query log

How do you know which queries are running slowly, other than through your own observation? MySQL's slow-query log is disabled by default. To enable slow-query logging, open the config file my.cfg, and under mysqld: run these two commands:

  1. $long_query_time = 1
  2. $log-slow-queries = /var/log/mysql/mysql-slow-query.log

In the above example, the "1" value means all queries requiring one second or longer to process get logged; the default setting is "0". The changes take effect after the server restarts. Now whenever you encounter a slow query, simply run the EXPLAIN command to view information about the problem. (Source: Aidan Hwang, One Extra Pixel)

Tip #2: The key to efficient self-joins is proper indexing

Multiple self-joins are commonly used to speed queries when a single column contains a great deal of data. However, stitching together too many self-joins can bring processing to a standstill. That was the scenario presented in a Stack Exchange post from June 2013. One option in such situations is to denormalize the table, use wide tables, and place variables in different columns in lieu of self-joins.

The solution proposed in the post avoids denormalizing and focuses on improved indexing. By adding an index of four variables, there's no need to use derived tables. (The four values are shown in the screen below: year, dataID, countryID, and data.)

Adding four index terms makes queries of multiple self-join columns much more efficient. Source: Stack Exchange

Such an index streamlines the query itself, as shown in the example below.

A nontrivial side-benefit of careful indexing is the ability to shorten the queries themselves. Source: Stack Exchange

Tip #3: Make sure your query cache is working

One of MySQL's top time savers is the query cache. To ensure that the cache is operating, open a mysql> prompt and enter the following command:

  1. mysql> show variables like 'query%';

While the query cache is enabled by default on nearly all MySQL servers, the function is disabled for "CURDATE" (current date). You can enable query cache for CURDATE by adding a line of PHP in front of the query, such as in the example below:

  1. //CURDATE is not deterministic
  2. $query = 'SELECT id FROM table WHERE publish_date = CURDATE()';

Tip #4: Avoid using MySQL as a queue

If the status of an item in your app is set in a way that allows a specific worker process to claim it, you've created a MySQL queue. And that can be a real performance sinkhole. When you serialize your workload in this manner, you prevent tasks from running in parallel. Your table may mix work in process with old, historical data. Either way, the app has to deal with more latency, and MySQL's overall load increases. (Source: Baron Schwartz, InfoWorld)

Tip #5: Change your config file to minimize disk thrashing

Whenever the number of concurrent database queries overwhelms the server's available RAM, you get a lot of disk I/O, which can lead to thrashing. One way to determine whether your disk I/O is being throttled is to compare the server's load value to its CPU utilization: When the load is higher than CPU use, tasks queuing for execution are likely causing long waits for disk I/O.

To address disk thrashing in virtualized environments, make two changes to your config file. The default setting for the innodb-flush-log-at-trx-commit parameter is "1", which allows InnoDB to be ACID-compliant. For databases that aren't transaction-intensive, you can change this setting to "2", or disable it by entering a value of "0". This may cause you to lose a second or so of data in the event of a crash, but it will save a considerable amount of I/O. (The MySQL Reference Manual explains why you may not want to change this parameter.)

The second config file parameter is tmp_table_size, which sets the size of the temporary tables MySQL creates whenever it performs a query that requires a new combination of data to be held in reference, such as for a JOIN operation. When tmp_table_size and max_heap_table_size are set too low, these temp tables will use disk memory instead of faster RAM. To avoid this, increase these values based on a ratio of total available RAM and max connections. (Source: Matthew Mombrea, IT World)

A fast, simple, and efficient approach to monitoring the performance of your apps, databases, and servers is by using the Happy Apps service. Happy Apps lets you create checks and alerts for MySQL as well as for the web, MongoDB, Redis, Riak, and ElasticSearch. The robust app-management solution supports SSH and agent-based connectivity to all your apps on public, private, and hybrid clouds. It offers dependency maps that help you determine the impact IT systems have on other apps.

All Happy Apps server checks are collected in easy-to-read reports that can be analyzed to identify repeating patterns and performance glitches over time. If you would like to save time, trouble, and money when managing your servers, visit the Happy Apps site for pricing information and to sign up for a free trial.