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

[8 Jun 16:51] Carl Letourneau
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));
[20 Jun 12:54] Sinisa Milivojevic
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 ????
[20 Jun 14:16] Carl Letourneau
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.
[20 Jun 14:28] Sinisa Milivojevic
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 ???