MySQL Bugs: #72348: Please allow rebuild partition to work on multiple partitions at once

Bug #72348	Please allow rebuild partition to work on multiple partitions at once
Submitted:	15 Apr 2014 6:13	Modified:	19 Apr 2014 7:46
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I had a recent issue where a partitioned table need to be rebuilt to free up some disk space. There was free space in the .ibd tables (as reported by INFORMATION_SCHEMA.TABLES).

ALTER TABLE could not be used as there was not enough space on the filesystem for the new and old tables to exist (old tables was > 400 GB) so I had to rebuild the table by doing each partition separately.

Doing this does not allow me to saturate the I/O on the system so it's rather inefficient (I guess single threaded) and therefore takes longer than necessary.
I was not able to run several rebuild partitions in parallel as it seems there's a global lock which prevents this, yet I was accessing different partitions so there should be no need to prevent this happening.

How to repeat:

I was doing something like the following:

[Mon Apr 14 11:07:32 2014] ALTER TABLE `mytable` REBUILD PARTITION p0
[Mon Apr 14 12:09:12 2014] ALTER TABLE `mytable` REBUILD PARTITION p1
...

This table has 32 partitions.

Suggested fix:

Session1: ALTER TABLE `mytable` REBUILD PARTITION p0
Session2: ALTER TABLE `mytable` REBUILD PARTITION p1
Session3: ALTER TABLE `mytable` REBUILD PARTITION p2
[ up to n parallel sessions ]
... wait for the first statement to finish, say it's session 2 ...
Session2: ALTER TABLE `mytable` REBUILD PARTITION p3
...

Given the partitions are independent there should be no need for extra locking and even if you hold a table lock in this specific case the REBUILD PARTITION should be able, under the right circumstances, to slip under the table lock and run in parallel to other similar commands which are running.

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

btw, rebuilding of >1 partitions can also be done using:

ALTER TABLE `mytable` REBUILD PARTITION p0,p1,p2;

https://dev.mysql.com/doc/refman/5.6/en/partitioning-maintenance.html

Thanks for the pointer, but given it does not say otherwise I would expect that to work sequentially.  My feature request is to allow this process to happen in parallel which enables I/O to be used better.

Another thing with such partition commands is that it is not clear to me how this process may be buffered, as it may not be convenient to fetch data into the InnoDB buffer pool thus purging out other valuable data prior to writing back out to disk again. Some buffering is no doubt needed but I probably need to see if this process has this potentially undesirable affect of purging out a lot of existing data, or only buffers what is strictly necessary to allow "streaming" of data in from the old partition prior to streaming out to the new one.