maria-discuss team mailing list archive

[Conor Murphy <conor_mark_murphy@xxxxxxxxxxx>]

Hi,

I'm trying to covert an existing database from MyISAM to InnoDB. The conversion completed okay, but I'm seeing one particular query causing problems, with MyISAM it was taking ~ 6 minutes to complete, with InnoDB it's taking nearly 14 hours

The query is as follows
SELECT procid, UNIX_TIMESTAMP(MAX(time)), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100

EXPLAIN SELECT procid, UNIX_TIMESTAMP(MAX(time)), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+
| id   | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows      | Extra |
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+
|    1 | SIMPLE      | proc_stats | index | NULL          | pidIdx | 2       | NULL | 956928549 |       |
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+

| proc_stats | CREATE TABLE `proc_stats` (
  `time` datetime NOT NULL,
  `siteid` smallint(5) unsigned DEFAULT NULL,
  `serverid` smallint(5) unsigned NOT NULL,
  `procid` smallint(5) unsigned NOT NULL,
  `cpu` smallint(5) unsigned DEFAULT NULL,
  `mem` mediumint(8) unsigned NOT NULL,
  `thr` smallint(5) unsigned NOT NULL,
  `fd` smallint(5) unsigned DEFAULT NULL,
  `rss` mediumint(8) unsigned DEFAULT NULL,
  `nproc` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sample_interval` smallint(5) unsigned DEFAULT NULL,
  KEY `pidIdx` (`procid`),
  KEY `serverTimeIdx` (`serverid`,`time`),
  KEY `siteIdIdx` (`siteid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(time))
(PARTITION P20120101 VALUES LESS THAN (734868) ENGINE = InnoDB,
 PARTITION P20151201 VALUES LESS THAN (736298) ENGINE = InnoDB,
 PARTITION P20160101 VALUES LESS THAN (736329) ENGINE = InnoDB,
 PARTITION P20160201 VALUES LESS THAN (736360) ENGINE = InnoDB,
 PARTITION P20160301 VALUES LESS THAN (736389) ENGINE = InnoDB,
 PARTITION P20160401 VALUES LESS THAN (736420) ENGINE = InnoDB,
 PARTITION P20160501 VALUES LESS THAN (736450) ENGINE = InnoDB,
 PARTITION P20160601 VALUES LESS THAN (736481) ENGINE = InnoDB,
 PARTITION P20160701 VALUES LESS THAN (736511) ENGINE = InnoDB,
 PARTITION P20160801 VALUES LESS THAN (736542) ENGINE = InnoDB,
 PARTITION P20160901 VALUES LESS THAN (736573) ENGINE = InnoDB,
 PARTITION P20161001 VALUES LESS THAN (736603) ENGINE = InnoDB,
 PARTITION P20161101 VALUES LESS THAN (736634) ENGINE = InnoDB,
 PARTITION P20161201 VALUES LESS THAN (736664) ENGINE = InnoDB,
 PARTITION P20170101 VALUES LESS THAN (736695) ENGINE = InnoDB,
 PARTITION P20170201 VALUES LESS THAN (736726) ENGINE = InnoDB,
 PARTITION P20170301 VALUES LESS THAN (736754) ENGINE = InnoDB,
 PARTITION P20170401 VALUES LESS THAN (736785) ENGINE = InnoDB,
 PARTITION P20170501 VALUES LESS THAN (736815) ENGINE = InnoDB,
 PARTITION P20170601 VALUES LESS THAN (736846) ENGINE = InnoDB,
 PARTITION P20170701 VALUES LESS THAN (736876) ENGINE = InnoDB,
 PARTITION P20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION P20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION P20171001 VALUES LESS THAN (736968) ENGINE = InnoDB,
 PARTITION P20171101 VALUES LESS THAN (736999) ENGINE = InnoDB,
 PARTITION P20171201 VALUES LESS THAN (737029) ENGINE = InnoDB,
 PARTITION P20180101 VALUES LESS THAN (737060) ENGINE = InnoDB,
 PARTITION Q201804 VALUES LESS THAN (737150) ENGINE = InnoDB,
 PARTITION Q201807 VALUES LESS THAN (737241) ENGINE = InnoDB,
 PARTITION Q201810 VALUES LESS THAN (737333) ENGINE = InnoDB,
 PARTITION Q201901 VALUES LESS THAN (737425) ENGINE = InnoDB,
 PARTITION QMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

While the query is running, all I can see in the processlist is
| 64313 | statsadm| 64313 | statsadm | priv:41254   | statsdb | Query       |  13001 | Sending data                                                          | SELECT procid, MAX(time), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100                      |    0.000 |

Version is
5.5.60-MariaDB

Any suggestions on how to localize why the query is taking so long with InnoDB?

Thanks,
Conor