maria-discuss team mailing list archive

[Conor Murphy <conor_mark_murphy@xxxxxxxxxxx>]

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