maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxxxx> 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@xxxxxxxxx>
> Sent: Tuesday 11 December 2018 12:01
> To: Rhys.Campbell@xxxxxxxxxxxx
> Cc: conor_mark_murphy@xxxxxxxxxxx; maria-discuss@xxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx> <Rhys.Campbell@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx] On Behalf Of Conor Murphy
>> Sent: 11 December 2018 01:27
>> To: maria-discuss@xxxxxxxxxxxxxxxxxxx
>> 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
> _______________________________________________
> 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