Mysql – mariadb (thesql) innodb not optimizing SELECT for partitions

debugginginnodbmariadbMySQLpartitioning

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

Best Answer

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.

Related Question