How to speed up partition merging
The key to performance with partition management is to
deal with empty partitions. That way all you have is meta data changes and no
data has to be copied.
We recently encountered a situation where we had two partitions and wanted to
merge them. In a merge you have 3 boundaries the left one, the middle one and
the right one. In doing a merge you are going to get rid of the middle one and
have 1 partition that is bounded by the left and right values. The partition on
the left of the middle boundary was empty and the one on the right wasn't. We
were using RIGHT partitioning so the partition value for the middle boundary was
included in the RIGHT hand partition. We expected this to be very
quick. The engine you take the RIGHT hand partition and use it for the new
partition.
But NO it was very slow.
In this situation the new partition has two boundaries the left and the right
ones. Because we are using RIGHT based partitioning the boundary value included
in this partition is the left hand one, this is the closed boundary.
What we found was that when doing a merge the engine takes the partition with
the remaining closed boundary as the base partition and copies the merges the
other partition with it. If doing RIGHT based partitioning this means if you are
trying a sliding window partition when you switch the last partition out and
then merge the last two partitions it moves the data from the right partition
into the left partition, because the left partition has the closed boundary.
This obviously sucks for performance.
To get round this, what you need to do is 1 of two things,
1. Leave the 2 partitions at the end so that both are empty, when these are
merged the operation will be just meta data
2. Switch out the partition with the data, perform the merge on the now two
empty partitions. Switch the partition back in. To do this you have to align the
check constraints with the partition you are switching into. Which is a bit of a
pain.
-