All Linux HowTo's

Find and Fix MySQL Performance Problems with Indexes

So you have a MySQL database and it’s running like a dog. A slow dog, not one of those fast ones. This article is for you. Actually, this article is more of a story about a recent problem a client had with a large Magento database that had become too slow for staff to handle.

The problem presented its self when an application server started timing out. It wouldn’t wait longer than 60 seconds for a response which suggested that something upstream had a problem. We enabled PHP-FPM low-log logging and identified that some queries were running for minutes which is unacceptable. So we checked and the query was not supported by an Index. We added an Index, verified it was being used and checked the slow-log logs. The logs showed great query times with only a few exceeding a second.

Here’s what we did.

First we need to enable the slow-log feature in MySQL. I’ve noticed that different versions of MySQL use slightly different configuration options in the “/etc/my.cnf” file where some need “_” and others need “-” between option words. use the right form for your version:

slow-query-log                 = 1
slow-query-log-file            = /mnt/fast-disks/mysql-slow.log

Put the logs on fast disks so you don’t make the problem worse. I’m putting them on SSD’s in “/mnt/fast-disks/mysql-slow.log”.

MySQL needs a reload for those configuration settings to take effect. Restart or reload MySQL however your distribution of Linux (or dare i say it, Windows) requires. In my case i am working with a Redhat 6 server.

service mysql reload

Now monitor the slow-log file for new content:

tail -f /mnt/fast-disks/mysql-slow.log

Here’s a sample of what you might see:

# Time: 170224  8:49:28
# User@Host: cantsay @  [10.1.2.3]  Id: 947400
# Schema: my_magento  Last_errno: 0  Killed: 0
# Query_time: 10.552383  Lock_time: 0.000152  Rows_sent: 1  Rows_examined: 240448  Rows_affected: 0
# Bytes_sent: 26357
SET timestamp=1487886568;
SELECT `main_table`.* FROM `table` AS `main_table` WHERE (`customer_email` = 'me@there.com');

As you can see the above query was running for more than 10 seconds. See the “Query_time” line above. Also note the query “SELECT `main_table`.* FROM `table` AS `main_table` WHERE (`customer_email` = ‘me@there.com’);”. We need to find out if that query is using an Index. If not, it’s likely the reason it’s taking so long to complete. In my case it was the causes.

Here’s how i checked if the above query was using an Index. Just put the word “EXPLAIN” in-front of the query:

mysql> EXPLAIN SELECT `main_table`.* FROM `mytable` AS `main_table` WHERE (`customer_email` = 'me@there.com')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main_table
         type: ref
possible_keys: NULL
          key: NULL
      key_len: 4
          ref: const
         rows: 18767
        Extra: NULL
1 row in set (0.00 sec)

In the above output we can see two lines that indicate an Index was not used:

possible_keys: NULL
          key: NULL

Now i’ll add the Index. Make sure you have a backup before you start messing with the insides of your database. Having said that, adding an Index is considered safe. My Index in the following command is “index_order_id”. You can call it whatever you want. The important part is that we need the “order_id” column to be indexed.

This deserves a little more explanation. The query we’ve identified as being slow is working on a single column called “order_id”. So we need to Index that column.

ALTER TABLE mytable ADD INDEX index_order_id (order_id);

Now we have our Index. Let’s run the “EXPLAIN” command again and see if we get different results:

mysql> EXPLAIN SELECT `main_table`.* FROM `mytable` AS `main_table` WHERE (`customer_email` = 'me@there.com')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main_table
         type: ref
possible_keys: index_order_id
          key: index_order_id
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

We can see the Index does exist and was (or would be) used. We’re done here. Watch the slow-log file and see if things have improved.

Share This:

Leave a Reply

Your email address will not be published. Required fields are marked *