MySQL Bugs: #72009: Poor performance using subpartions in MySQL 5.6. Good in 5.5

Bug #72009	Poor performance using subpartions in MySQL 5.6. Good in 5.5
Submitted:	11 Mar 2014 11:53	Modified:	26 Apr 2014 10:52
Reporter:	Jesus Marquez	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partitioning, performance, subpartitions

Description:
It seems that using SUBPARTITION clause in table creation makes queries to such tables around 4 times slower.
Removing that SUPARTITION clause (just using the PARTITION clause), query performance is back to normal.

Due to bug (already fixed in 5.6.x) http://bugs.mysql.com/bug.php?id=62505 partition schema for our tables where limited to 16 entries per table (using partition type LIST).

As we migrated to 5.6.14 from 5.5, current database tables partitioning policy is still limited to 16 entries per partition (plus 10 KEY subpartitions).

Following figures are for one of such tables:
---------------------------------------------
Engine: MyISAM
Number of records in table: 238114421
Number of columns in table: 50
column type used for keys and partitioning: int(11)

Number of different values for column 'timeslice_id' used in LIST partitioning: 674
Number of different values for column 'int_id' used in KEY subpartitioning: 12
Number of different values for pair (timeslice_id ,int_id): 7683

Number of files created in filesystem: 882

Table definition (some information omitted):

CREATE TABLE `table_example` (
  `int_id` int(11) NOT NULL DEFAULT '0',
  `field1` int(11) DEFAULT NULL,
  `field2` int(11) NOT NULL DEFAULT '0',
  `timeslice_id` int(11) NOT NULL DEFAULT '0',

50 more columns.

  PRIMARY KEY (`int_id`,`field2`,`timeslice_id`),
  KEY `key_index` (`field1`),
  KEY `timeslice_index` (`timeslice_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (timeslice_id)
SUBPARTITION BY KEY (int_id)
SUBPARTITIONS 10
(PARTITION part_DUMMY VALUES IN (0) ENGINE = MyISAM,
 PARTITION part_12345678_1 VALUES IN ( <16 different values> ) ENGINE = MyISAM,
 PARTITION part_12345678_2 VALUES IN ( <16 different values> ) ENGINE = MyISAM,
 PARTITION part_12345678_3 VALUES IN ( <16 different values> ) ENGINE = MyISAM,
 PARTITION part_12345678_4 VALUES IN ( <16 different values> ) ENGINE = MyISAM,

44 more partitions

) */;

How to repeat:
1.- Create a table big enough to need a partitioning policy so a partitions are created.

2.- This table must be PARTITIONED by LIST (an_int_column) and SUPARTITIONED by KEY (some_other_int_column).

3.- Query that table (which is dead slow).

4.- Recreate table without any SUPARTITIONED policy and benchmark performance.

Hello Marquez,

Thank you for the report.
I can not repeat described behavior with dummy data on 5.6.16/5.5.36. 
Please could you provide the complete repeatable test case(exact table DDL, and all data, and the select query that you are using - please make it private when you upload).

Thanks,
Umesh

Hi,

To be honest with you I cannot longer reproduce this issue.
We are still experiencing this problem, but removing the subpartition part does not make a difference.

It is really weird because everything was fine until we migrated from MySQL 5.5 to 5.6.

We are now testing new indexes in order to overcome this performance issue.
Indexes are been reworked, which is really strange as they used to work perfectly in MySQL 5.5

Hello Marquez,

Thank you for the update.
Once it is repeatable at your end then please reopen the bug with the complete reproducible test case(with the configuration details).

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".