Re: Huge Dataset (perhaps?) made for Range Partitioning (Long)

[Mikael Ronström]

Hi,


JinNo Kim wrote:
>
> Thanks for the response, I'm hoping we'll be able
> to provide some
> useful feedback to the community and a lab to test
> some aspects of
> partitioning with a large dataset.
>

Sounds great.

> The primary key was originally only going to used
> to uniquely identify
> rows in the 1 giant table for quick reference when
> we had identified a
> transaction of interest thorugh other queries.
> It's no longer unique
> across the daily MyISAM tables and I've been
> considering dropping it
> to reclaim the 8 bytes/row.
>

Well you can either drop it or add the date used for the partition function to make it
unique.

> One of the members of my team spent part of
> yesterday loading
> another smaller server. We intend to attempt a
> build of 5.1 on it so
> we'll be able to test any of the "what if
> scenarios" with a subset of the
> existing tables. The new server only has (2) 146G
> disks, so we
> striped them, giving about 200G for the DB which
> should allow us to
> get a reasonably large dataset to play with.
>
> I will drop 'A' on the tables created on the test
> server. It seemed
> like a good idea at the time, but we are no longer
> able to use it and
> it may impede progress. Would it be better to
> wait a bit before
> starting or is the partitioning code ready to
> start experimenting
> with say 500 million rows or so?
>

As with all pre-alpha code the stability is obviously not going to be perfect.
All the syntax is there and normal SELECT, INSERT's and so forth should work fine.
I'm still working on the optimised partition management such that you can
add and drop partitions momentarily (currently it uses the standard method of
copying the data to a new table which won't be very fun with 500 million rows).

So my advice would be to try it out first with an even smaller subset to get used to
the commands for partitioning and see if you miss some features. Then in a few weeks
when I push the new optimised management commands to the 5.1 tree you should be
able to work with much larger data sets efficiently. I can probably speed up the push
process if desirable.

Rgrds Mikael

> Thanks again,
>
> -JNK

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com