[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
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
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 http://petermclarty.setmore.com/
Hi, The explain for the MyISAM table is the same as what's given for the InnoDB version +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12 The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query. So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is. Thanks, Conor
Can you put this is a proc and cycle through the list of partitions? I recall doing this a number of years ago and found this to be much faster than a single query. Perhaps profiling the query can throw up some specifics... https://mariadb.com/kb/en/library/information-schema-profiling-table/ From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy Sent: 11 December 2018 01:27 To: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB Hi, The explain for the MyISAM table is the same as what's given for the InnoDB version +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12 The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query. So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is. Thanks, Conor
You could use Shard-Query (https://mariadb.com/kb/en/library/shard-query/) to process the query in parallel over the partitions. This will improve the performance of OLAP type queries.
On Dec 11, 2018, at 5:27 AM, <Rhys.Campbell@swisscom.com> <Rhys.Campbell@swisscom.com> wrote:
Can you put this is a proc and cycle through the list of partitions? I recall doing this a number of years ago and found this to be much faster than a single query.
Perhaps profiling the query can throw up some specifics…
https://mariadb.com/kb/en/library/information-schema-profiling-table/
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy Sent: 11 December 2018 01:27 To: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
Hi,
The explain for the MyISAM table is the same as what's given for the InnoDB version
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+
I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache
innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12
The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query.
So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is.
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
Hi, I tried the profiling but now the query performance seems to have degraded even further, the query hadn't completed after 24 hours at which point I aborted it. I guess with could look at the Shard-Query stuff. However, given that the performance is okay with MyISAM, I'd like to understand what the problem with InnoDB is. Is there are any way to "trace" what the query execution is doing in the InnoDB engine? /Conor ________________________________ From: Justin Swanhart <greenlion@gmail.com> Sent: Tuesday 11 December 2018 12:01 To: Rhys.Campbell@swisscom.com Cc: conor_mark_murphy@hotmail.com; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB You could use Shard-Query (https://mariadb.com/kb/en/library/shard-query/) to process the query in parallel over the partitions. This will improve the performance of OLAP type queries. On Dec 11, 2018, at 5:27 AM, <Rhys.Campbell@swisscom.com<mailto:Rhys.Campbell@swisscom.com>> <Rhys.Campbell@swisscom.com<mailto:Rhys.Campbell@swisscom.com>> wrote: Can you put this is a proc and cycle through the list of partitions? I recall doing this a number of years ago and found this to be much faster than a single query. Perhaps profiling the query can throw up some specifics… https://mariadb.com/kb/en/library/information-schema-profiling-table/ From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy Sent: 11 December 2018 01:27 To: maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB Hi, The explain for the MyISAM table is the same as what's given for the InnoDB version +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12 The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query. So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is. Thanks, Conor _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
What is the value of “temporary_storage_engine”? Perhaps you are using InnoDB temporary tables which are slower than aria temporary tables. The biggest difference between InnoDB and myisam for this type of simple query with a full table scan is IO because InnoDB tables are larger than myisam for the same data. However this size difference (which should be less than 2x) doesn’t account for the dramatic time difference. You might be able to use PERFORMANCE_SCHEMA to get performance information, but MariaDB lacks a sys schema, making P_S hard to use.
On Dec 12, 2018, at 6:36 AM, Conor Murphy <conor_mark_murphy@hotmail.com> wrote:
Hi,
I tried the profiling but now the query performance seems to have degraded even further, the query hadn't completed after 24 hours at which point I aborted it.
I guess with could look at the Shard-Query stuff. However, given that the performance is okay with MyISAM, I'd like to understand what the problem with InnoDB is.
Is there are any way to "trace" what the query execution is doing in the InnoDB engine?
/Conor From: Justin Swanhart <greenlion@gmail.com> Sent: Tuesday 11 December 2018 12:01 To: Rhys.Campbell@swisscom.com Cc: conor_mark_murphy@hotmail.com; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
You could use Shard-Query (https://mariadb.com/kb/en/library/shard-query/) to process the query in parallel over the partitions. This will improve the performance of OLAP type queries.
On Dec 11, 2018, at 5:27 AM, <Rhys.Campbell@swisscom.com> <Rhys.Campbell@swisscom.com> wrote:
Can you put this is a proc and cycle through the list of partitions? I recall doing this a number of years ago and found this to be much faster than a single query.
Perhaps profiling the query can throw up some specifics…
https://mariadb.com/kb/en/library/information-schema-profiling-table/
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy Sent: 11 December 2018 01:27 To: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
Hi,
The explain for the MyISAM table is the same as what's given for the InnoDB version
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+
I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache
innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12
The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query.
So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is.
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
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
Are these tables in the same database, so that all the other settings, like sort_buffer_size are the same?
On Dec 12, 2018, at 6:36 AM, Conor Murphy <conor_mark_murphy@hotmail.com> wrote:
Hi,
I tried the profiling but now the query performance seems to have degraded even further, the query hadn't completed after 24 hours at which point I aborted it.
I guess with could look at the Shard-Query stuff. However, given that the performance is okay with MyISAM, I'd like to understand what the problem with InnoDB is.
Is there are any way to "trace" what the query execution is doing in the InnoDB engine?
/Conor From: Justin Swanhart <greenlion@gmail.com> Sent: Tuesday 11 December 2018 12:01 To: Rhys.Campbell@swisscom.com Cc: conor_mark_murphy@hotmail.com; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
You could use Shard-Query (https://mariadb.com/kb/en/library/shard-query/) to process the query in parallel over the partitions. This will improve the performance of OLAP type queries.
On Dec 11, 2018, at 5:27 AM, <Rhys.Campbell@swisscom.com> <Rhys.Campbell@swisscom.com> wrote:
Can you put this is a proc and cycle through the list of partitions? I recall doing this a number of years ago and found this to be much faster than a single query.
Perhaps profiling the query can throw up some specifics…
https://mariadb.com/kb/en/library/information-schema-profiling-table/
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Conor Murphy Sent: 11 December 2018 01:27 To: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
Hi,
The explain for the MyISAM table is the same as what's given for the InnoDB version
+------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956763463 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+
I've only done a very basic tuning for InnoDB, i.e. give it a 24GB cache
innodb-file-per-table=1 innodb-buffer-pool-size=24G innodb_buffer_pool_instances=12
The table holds time series data and the majority of queries on the table use the time column which allows the partition pruning to operate.However, this particular query is related to a nightly maintenance activity and ends up performing a full scan of the table. But this is the same for MyISAM which takes ~ 6 minutes to perform the query.
So cause of the ~ 14 hours must be something specific to InnoDB but I've no idea how to drill down to see what the issue with InnoDB is.
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
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
Hi, No, the database server is different. Originally, MariaDB was co-collocated with the application service. As part of of the switch to InnoDB, we've moved MariaDB to it's own server, but the settings are the same with the exception of the the innodb-buffer-pool-size. When we were using MyISAM, this wasn't set. Now we're using InnoDB, we've set this to 24G (server has 32GB RAM). Looking at a pstack taken will the query is running, it looks like the server is spending most of it's time reading Thread 8 (Thread 0x7f74200d9700 (LWP 7957)): #0 0x00007f74244c0013 in pread64 () from /lib64/libpthread.so.0 #1 0x00005582e07e86e1 in os_file_pread () #2 0x00005582e07ec216 in os_file_read_func () #3 0x00005582e07eeb02 in os_aio_func () #4 0x00005582e079f959 in _fil_io () #5 0x00005582e076c6a3 in buf_read_page () #6 0x00005582e0755837 in buf_page_get_gen () #7 0x00005582e0742ca5 in btr_cur_search_to_nth_level () #8 0x00005582e06f3adb in row_search_for_mysql () #9 0x00005582e06c4c50 in ha_innobase::index_next(unsigned char*) () #10 0x00005582e091a6a7 in ha_partition::handle_ordered_next(unsigned char*, bool) () #11 0x00005582e0465417 in join_read_next(READ_RECORD*) () #12 0x00005582e045a0a3 in sub_select(JOIN*, st_join_table*, bool) () #13 0x00005582e046887d in do_select(JOIN*, List<Item>*, TABLE*, Procedure*) () #14 0x00005582e047aa9b in JOIN::exec() () #15 0x00005582e0475c19 in mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) () #16 0x00005582e04766eb in handle_select(THD*, LEX*, select_result*, unsigned long) () Looking at the engine status, I'm starting to think that the O/S is doing a better job of pre-fetching (with MyISAM, the majority of RAM was being used by the O/S filesystem cache) then InnoDB does FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 592947343 OS file reads, 80039464 OS file writes, 35075860 OS fsyncs 1 pending preads, 0 pending pwrites 1355.13 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s .... Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s /Conor
participants (4)
-
Conor Murphy -
Justin Swanhart -
Peter McLarty -
Rhys.Campbell＠swisscom.com