mariadb (mysql) innodb not optimizing SELECT for partitions?

Question

I have a table partitioned by on column "level" -- I have logs where level 7 is the verbose info, which is like 20x as many entries as all the other levels.

When purging old logs in level 7, it was taking quite a while. I had assumed it was just the amount of logs. (And possibly also the transaction time).

But when I compared the query-select with a clone in myisam, it seems it's ignoring the simple partition optimization it could make!

Even worse, this select optimization doesn't carry over to the DELETE query, as per EXPLAIN! It takes the partition, but won't use the date index!

Is this a bug, or just my lack of knowledge?

SHOW GLOBAL VARIABLES LIKE '%version%'
innodb_version 5.6.26-74.0
protocol_version 10
version 10.1.9-MariaDB-1~jessie
version_comment mariadb.org binary distribution
version_compile_machine x86_64
version_compile_os debian-linux-gnu
version_malloc_library system jemalloc
version_ssl_library OpenSSL 1.0.1t 3 May 2016
wsrep_patch_version wsrep_25.11

https://gist.github.com/avimar/16824955e21781c7e2c6594ef743bc6f

show create table logs_fs
CREATE TABLE `logs_fs` (
 `uuid` char(36) DEFAULT NULL,
 `host` varchar(4) DEFAULT NULL,
 `date` datetime(6) NOT NULL,
 `hrtime` bigint(20) unsigned NOT NULL,
 `level` tinyint(4) DEFAULT NULL,
 `value` text,
 KEY `uuid` (`uuid`),
 KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB) */

My naive query: sql should know which partition!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` WHERE `date` < date_sub(now(), interval 1 week) and level=7
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE logs_fs ALL date NULL NULL NULL 1,777,576 Using where

I had to specify the partition AND leave out the level=7 to get down to a lower number!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` PARTITION (p1) WHERE `date` < date_sub(now(), interval 1 week)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE logs_fs range date date 8 NULL 423,360 Using where; Using index

Indeed, it works in myisam! (Funny, it says it's querying 196k rows, but it returns the proper count of 210701 which matches innodb)

CREATE TABLE `logs_fs_myisam` (
 `uuid` char(36) DEFAULT NULL,
 `host` varchar(4) DEFAULT NULL,
 `date` datetime(6) NOT NULL,
 `hrtime` bigint(20) unsigned NOT NULL,
 `level` tinyint(4) DEFAULT NULL,
 `value` text,
 KEY `uuid` (`uuid`),
 KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = MyISAM) */
explain select SQL_NO_CACHE count(*) FROM `logs_fs_myisam` WHERE `date` < date_sub(now(), interval 1 week) and level=7`
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE logs_fs_myisam range date date 8 NULL 195,890 Using where

• Solarflare: Use explain partitions select ... and check if your partition really isn't used, it should work correctly. The value in rows can just be a (wild) guess. Try to optimize table logs_fs (can take some time), it might give you better statistics afterwards.

Answer 1

PARTITIONing with fewer than 10 partitions is rarely useful.

Perhaps PARTITION BY RANGE(TO_DAYS(date)) would be better? Perhaps daily partitions is best for your application.

Is your goal to purge only "level 7 AND old"? The gets rather messy because you would be purging only part of a partition, not being able to DROP PARTITION.

On the other hand, maybe this is a valid application for subpartitioning?? That would be exciting (to me) since I have not yet seen a use for subpartitioning. Beware of the limitations of what you can put in the BY clause.

I think you would have to go with

 PARTITION BY RANGE(date)
 SUBPARTITION BY KEY(level)

And use ALTER TABLE ... TRUNCATE PARTITION ... This would leave an empty partition behind, but it would be quick. Check the ramifications of innodb_file_per_table=1.