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` = '[email protected]');
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` = ‘[email protected]’);”. 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` = '[email protected]')\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` = '[email protected]')\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.