MySQL Forums
Forum List  »  Partitioning

Re: Huge Dataset (perhaps?) made for Range Partitioning (Long)
Posted by: Mikael Ronström
Date: September 23, 2005 10:01AM


JinNo Kim wrote:
> Great, the script is running in the office to drop
> the 'A' column (16G is 16G) :). I'm up to 6/17 on
> the unpack, check, alter, pack, check cycle
> @11:13P KST - should be done by Monday:)
> I'll sync my sources Sunday and build 5.1 on the
> new server Sunday night, KST. As far as speeding
> up the push process, I work on your schedule
> (well, within a couple of days, barring crisis
> management) :). I'll start playing with 10k rows
> from each of the existing daily tables sometime
> next week(hopefully). That should yield ~1M rows
> in the table to play with (10000 per daily
> partition). I'll start out slow with a few days
> and when I'm comfortable, pull the rest in.

Sounds like a good start.

> One thing I'm not totally clear about, will there
> be a way for me to autogenerate a new parititon
> for a new days data in the definition, or will I
> need to be doing that by hand (or script)?

This is what you will do with the SQL command (the syntax here is for RANGE partitioning)


In the current version this will be slow since it recreates the entire table, but the optimised
version runs on my desktop and is soon ready for intro into the 5.1 tree. The optimised version
will simply create a new partition and not move any of the existing data.

There is also commands to drop partitions
ALTER TABLE LARGE_TABLE DROP PARTITION (list of partition names to drop)

There is also a command to reorganise the partitions (e.g. if you want the first 50 days of
data to be stored in weekly partitions instead you can do that by using
ALTER TABLE LARGE_TABLE REORGANISE PARTITION (list of partition names to reorganise)
INTO (list of partition definitions)

All lists are comma separated.

In the code on my desktop there are also commands to
REBUILD PARTITION (list of partition names) that will copy the partition to a new partition with
the same name and characteristics to get data less fragmented e.g.
OPTIMIZE PARTITION (list of partition names) more or similar as REBUILD but done by the
storage engine internally, e.g. only rebuild indexes.
also ANALYZE, CHECK and REPAIR PARTITION will be there.

Documentation is also in the writings so I'll ensure that you get early access to that if you give
me an email address to send it to.

> This is one of those times where I'm really
> motivated to help and excited about the prospect
> of contributing anything (even just a testbed) to
> something great.

Sounds great, it's also of immense value to us to check that we have developed the right tools
for partitioning since obviously in an early version you can't do everything at once.

Rgrds Mikael

> Thank you,
> -JNK

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:

Options: ReplyQuote

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.