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 enabled 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?
Thank you in advance.
