Try an index on (proxid, time). I would look at the EXPLAIN from the MYISAM table for why.

Have you tuned your config for innodb at all? i.e. innodb cache, open files etc.

Rhys

From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy
Sent: 10 December 2018 17:05
To: maria-discuss@lists.launchpad.net
Subject: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB

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