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 <[email protected]> > 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 <[email protected]> > Sent: Tuesday 11 December 2018 12:01 > To: [email protected] > Cc: [email protected]; [email protected] > 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, <[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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
