MySQL Bugs: #91190: DROP PARTITION and REORGANIZE PARTITION are slow

Bug #91190	DROP PARTITION and REORGANIZE PARTITION are slow
Submitted:	8 Jun 2018 16:51	Modified:	20 Jul 2018 14:28
Reporter:	Carl Letourneau	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
I use partitions for management of time series data.  I use DROP PARTITION to quickly delete old data and REORGANIZE PARTITION to prepare partitions for newer data.  REORGANIZE PARTITION is always used on an empty partition (VALUES LESS THAN MAXVALUE)

In both cases, these operations are now very slow, as if rebuilding the table.

How to repeat:
CREATE TABLE `MyTable` (
	`ElementId` INT(11) NOT NULL,
	`Timestamp` DATETIME NOT NULL,
	`Measures` JSON NOT NULL,
	`JobId` INT(11) NOT NULL,
	PRIMARY KEY (`RecordVariantId`, `ElementId`, `Timestamp`)
)
PARTITION BY RANGE  COLUMNS(`Timestamp`)
(PARTITION p20180607 VALUES LESS THAN ('2018-06-08 00:00:00') ENGINE = InnoDB,
 PARTITION p20180608 VALUES LESS THAN ('2018-06-09 00:00:00') ENGINE = InnoDB,
 PARTITION pDefault VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

Insert a large number of rows in p20180607 and p20180608 (I have about 100G of data in each of these partitions).  pDefault should always be empty.

ALTER TABLE MyTable ALGORITHM=INPLACE, DROP PARTITION p20180607;
ALTER TABLE MyTable ALGORITHM=INPLACE, REORGANIZE PARTITION pDefault INTO (PARTITION p20180609 VALUES LESS THAN ('2018-06-10'), PARTITION pDefault VALUES LESS THAN (MAXVALUE));

Hi,

First of all, with 100 Gb of data, you are expected to wait for the long time to re-organise the partitions. 100 Gb should be respected. Second, what is exactly long time ??

Third of all, for such high amount of data you are expected to have entire server perfectly tuned and that your hardware is very fast, including using SSD for your most used data.

Fourth, have you experienced any slowdown between 8.0.11 and previous versions ???

Fifth, have you considered adding an index on that timestamp ????

DROP partition should be in the same ballpark as deleting a file.  On my server, this is instantaneous for a 10G file.  I realize that MySQL would have some minor overhead.

REORGANIZE PARTITION on an EMPTY partition (as mentioned in my submission) should, likewise, be in the same ballpark as creating an empty table.  Zero rows are read/inserted during the operation.

I've seen these operations take a few minutes.  The table is unavailable for writes during this time.

Indexing the Timestamp as you suggest would be somewhat useful to perform DELETEs instead of DROP PARTITIONs.
Since we're talking about the deletion of billions of rows per day, even with an index, this would result in significant I/O.  I'm using partitions specifically to avoid this scenario.

Finally, yes, I'm using PARTITIONs on a 5.7 server with expected performance.

Hi,

Few questions only ...

How much time does it take to perform any of the DDL statements that you mentioned ???

How many rows are reorganised in the partition and how much time does it take on 5.7 and 8.0 ???

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".

We have managed to resolve this by setting the parameter 
innodb_adaptive_hash_index = 0

see more info in https://bugs.mysql.com/bug.php?id=101900

We are happy to hear about it .