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, <[email protected]> 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= > [email protected]] *On Behalf Of *Conor Murphy > *Sent:* 10 December 2018 17:05 > *To:* [email protected] > *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 : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > -- Peter McLarty Leader and Technologist 0402094238 http://petermclarty.setmore.com/
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
