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, <[email protected]> > <[email protected]> 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....@lists.launchpad.net] > On Behalf Of Conor Murphy > Sent: 11 December 2018 01:27 > To: [email protected] > 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 : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
