Hi, 
If I am reading this right, you have partitioned on time. You are using the procid column to filter on in your select. That will require your query to still search all partitions, so with the number of rows you have and although it is using an index, it is still having to open and work through each one to gather the data. If you have a read of this the first section explains it better. https://mariadb.com/kb/en/library/partition-maintenance/
Do you have a reason for using the time partition? If not and procid is the primary search key then you might have better luck with a different partition key


Cheers
Peter

On Tue, 11 Dec 2018 at 02:29, <Rhys.Campbell@swisscom.com> wrote:

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

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


--
Peter McLarty
Leader and Technologist
0402094238