maria-discuss team mailing list archive

[Peter McLarty <peter.mclarty63@xxxxxxxxx>]

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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx] *On Behalf Of *Conor Murphy
> *Sent:* 10 December 2018 17:05
> *To:* maria-discuss@xxxxxxxxxxxxxxxxxxx
> *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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>


-- 
Peter McLarty
Leader and Technologist
0402094238
http://petermclarty.setmore.com/