Same query on same same table, different response time

Question

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is:

* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available;
* OS Debian 9;
* Mysql 5.6.47;

To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed. I had abilited the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE `MALICIOUS_TABLE` (
  `column_1` int(11) NOT NULL AUTO_INCREMENT,
  `column_2` varchar(8) NOT NULL,
  `column_3` datetime NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) DEFAULT NULL,
  `column_6` int(11) DEFAULT NULL,
  `column_7` int(11) DEFAULT NULL,
  `column_8` tinyint(1) DEFAULT NULL,
  `column_9` datetime DEFAULT NULL,
  `column_10` int(11) DEFAULT NULL,
  `column_11` varchar(2048) DEFAULT 'column_11',
  `column_12` tinyint(1) DEFAULT NULL,
  `column_13` datetime DEFAULT NULL,
  `column_14` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`column_1`),
  KEY `fk_ual_aut_idx` (`column_2`),
  KEY `fk_aul_c_idx` (`column_4`),
  KEY `kf_ual_po_idx` (`column_5`),
  KEY `fk_ual_ute_idx` (`column_10`),
  KEY `column_1` (`column_1`),
  KEY `column_2` (`column_2`),
  CONSTRAINT `fk_aul_c` FOREIGN KEY (`column_4`) REFERENCES `t_table2` (`column_4`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_aut` FOREIGN KEY (`column_2`) REFERENCES `t_tabl3` (`column_2`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_po` FOREIGN KEY (`column_5`) REFERENCES `t_table4` (`column_5`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_ute` FOREIGN KEY (`column_10`) REFERENCES `t_table5` (`column_10`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1 

The table has a not so small number of records:

select count(*) from `MALICIOUS_TABLE`;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time. The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no. Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself?

Answer 1

For MySQL to handle your query efficiently, you need to create a compound index. Without an index, MySQL must examine every row in your table to count the ones you want. That means it has to read every row from the SSD. That takes time.

Your filtering criteria are ...

WHERE column_4 = 1 
  AND column_8 is null 
  AND column_3 > CURDATE() - INTERVAL 30 DAY

You need an index that can satisfy your query efficiently. Because two criteria are matches to constant values, those two columns should come first. Then, the last criterion filters a range of values. Starting with some date in the past it filters records.

So, your index should be on (column_4, column_8, column_3). You create it like this:

 CREATE INDEX tocount ON malicious_table (column_4, column_8, column_3)

You can think of these indexes (they use an indexing technique called BTREE) as lists of data in order. So, the query planner can random-access the index to the first eligible row, and then scan the index sequentially until the last eligible row. That's far more efficient than reading the whole table.

You mentioned your query time is unpredictable. You're right that's unpleasant for your users. It's hard to know exactly why, even for an expert with access to your server. But this general observation applies: If others are using the same server for other things, their operations and yours may interfere with each other in unpredictable ways.

Pro tip Notice that the index I've given you is designed to match the query you gave. A different query might need a a different index. For example, if your query were

  SELECT COUNT(*), MAX(column_13), SUM(column_7)
    FROM malicious_table
   WHERE column_4 = 1 
     AND column_8 is null 
     AND column_3 > CURDATE() - INTERVAL 30 DAY

it would be satisfied with this covering index:

CREATE INDEX tosummarize ON malicious_table 
    (column_4, column_8, column_3, column_7, column_13);

The index I first suggested is made redundant by this index because this one contains the same columns in the same order as that one.

In general, you should avoid creating lots of one-column indexes in hopes of making queries faster. As a database grows, it generally needs indexes to be added (or sometimes dropped) to match the data. The cool thing about indexes is you can add and drop them without affecting your data or programs. Here's a good reference: https://use-the-index-luke.com/

Edit: Performance variations between queries can be due to many hard-to-discern factors. Is your MySQL server running on a virtual machine on a busy VM host? Does your MySQL server process serve applications other than yours? How busy is your system? Are lots of other processes INSERTing or UPDATing rows in your table concurrently with your counting queries? If so, the variability may be due to moment-to-moment changes in that concurrent workload. Bluntly, good luck figuring out that one in any detail! You may want to move on to other database performance issues.

There is one thing to try. Precede your counting query with this transaction isolation level setting:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This reduces the chance that your counting query locks up waiting for other operations on the table to complete. Read this for more details: https://stackoverflow.com/questions/7937472/setting-transaction-isolation-level-of-mysql Your query will still return a useful result, but it might not pick up changes to the table that happen concurrently to your counting request. It is probably acceptable for your application to miss a few most-recent rows in this count.

And, version 5.6 of MySQL is quite old. It was first released in February 2013. There have been bug and security fixes since, Still, many dozens of years of programmer effort have been spent on later versions since 5.6 came out. Most of them have been spent improving performance and reliability. Upgrade if you can.