Re: Why the limit that every partition must contain the same number of subpartitions ?
Hi,
Matthias Leich wrote:
> Hi Mikael,
>
> I just detected during test development that I
> cannot have different number of subpartitions
> per partition. I got the error message
> ERROR HY000: Trying to Add partition(s) with
> wrong number of subpartitions
> on ALTER TABLE ADD PARTITION .....
>
> Is there any special reason for this limitation ?
>
Mostly a lazy programmer :) who didn't want to handle all the
extra work with supporting variable number of subpartitions.
Also subpartitions is only possible at the moment to be various
variants of hash-partitioned and mainly there to spread data on
many disks.
But in general a normal cut-the-corner to ensure that the 5.1
release come out in time.
> I assume that there is a high probability, that
> many tables with different number
> of subpartitions per partition will not show the
> properties expected by the table creator.
> Especially if disks have the same performance, the
> access frequency per record
> does not significant differ between the records,
> ....
>
> Couldn't there be "exotic" situations where this
> limitation becomes a disadvantage ?
I'm sure you're right about that. However 5.1 is the first
partitioning release so I betted for the most common cases
and exotics should be fairly straightforward in future
releases.
> I hope my example is not too academic or far away
> from reality :-)
> Generic example:
> - Huge table PARTITION BY RANGE -- SUBPARTITION
> BY HASH or KEY
> - Two subpartitions per partition , one disk
> per subpartition
> - Every day many rows with higher PRIMARY KEY
> values like the existing one
> will be inserted. Table growth per day is
> constant.
> The upper value limit of the partitions is
> nearly reached and the DBA decides
> to add a partition. He installs an additional
> new disk which is twice as big as
> the other ones. So he can put the whole stuff
> of the new partition on this disk.
> But why shoud he use two subpartitions (->
> two fat files) instead of one
> partition without subpartitions (one fat file)
> ? I guess the second variant is
> faster when SELECTING via KEY range and
> physical order of records =
> logical order with ascending KEY.
>
> Of course a rearrangement of the partitioning
> scheme of the table
> -- redefine partitions and do not use
> subpartitions - by
> ALTER TABLE <whatever> is an option, but
> I fear that such operations on
> huge tables might need hours and maybe huge
> temporary disc space.
>
Agree that this slight disadvantage but can at the moment be handled either
by taking the small hit or by adding two larger disks.
One can also use REORGANIZE partitions to change the partitioning to a large
extent without requiring a full table reorg.
Rgrds Mikael
> Thanks in advance
>
> Regards,
>
> Matthias
>
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com