MySQL Forums
Forum List  »  Partitioning

Re: Internals - what are the stages of partition creation?
Posted by: Mikael Ronström
Date: November 28, 2007 03:37PM

Hi,
An ALTER TABLE like the below uses the normal ALTER TABLE
algorithm. In this case this means.

1) Create the new table with a temporary name (actually
6 partitions are created forming one table)
2) Copy the data from the original table to the new table.
This will take some time for a large table...
3) Rename table from temporary name to original table name.
4) Drops the old table.

Rgrds Mikael


Shlomo Priymak Wrote:
-------------------------------------------------------
> Hi,
>
> I'm running an ALTER TABLE on a very large table
> which looks like this:
>
> CREATE TABLE t(id bigint, data text).
>
> The alter looks like this:
>
> ALTER TABLE t PARTITION BY RANGE (id) (
> PARTITION p0 VALUES LESS THAN (10000000),
> PARTITION p1 VALUES LESS THAN (20000000),
> PARTITION p2 VALUES LESS THAN (30000000),
> PARTITION p3 VALUES LESS THAN (40000000),
> PARTITION p4 VALUES LESS THAN (50000000),
> PARTITION pother VALUES LESS THAN MAXVALUE;
>
> I ran this overnight, and assumed it would be
> done, since it created 6 files for the partitions,
> and filled them up one after another.
> When I came back in the morning, to my surprise
> the query was still working with a state of "copy
> to tmp table" in the process list. It created
> another 6 files, and filled them up again.
> The original file t.ibd was deleted though, so i
> didn't need x3 the space to perform this, only
> x2.
>
> The question is, What is it doing now? What is
> this extra stage? Shouldn't going over the
> original table and spilling the data into the
> partition buckets be enough? Is there a way to
> avoid it?
>
> Thanks in advance!
> Shlomo

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Internals - what are the stages of partition creation?
2617
November 28, 2007 03:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.