Creating partitions on a production database

Question

I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.

I have two tables that involve a composite key on columns:

Bill_Num Bill_Date

I want to create Partition on Bill_Date. The table consists records of four years. I want to know how a new partitioned table will accommodate future years? I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name?

Answer 1

I want to know how a new partitioned table will accommodate future years?

I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years.

I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name

Are you needing to use the 'old' table after you get this set up? If not, I would follow these steps:

1. Create partitioned table as you want it, under table name 'mytable_new'
2. INSERT INTO mytable_new SELECT * FROM mytable
3. RENAME TABLE mytable TO mytable_old
4. RENAME TABLE mytable_new TO mytable

This should avoid the need to alter your queries. If your table is extremely write-heavy (I assume it must be if you have the need to partition it!), you will probably want to have a maintenance timeframe where it's offline until the operation is complete.

Hope this helps.

Answer 2

I have recently worked on a MySQL database system which had numerous tables running into the billions of rows of data, which was fairly write heavy in usage. A slight advantage I had was the tables only needed to contain about 5 years worth of data - this got me thinking. Could I create a partitioning scheme which allowed the years to wrap nicely at the end of the partition? I also had the advantage that many of the queries performed were on either quarterly or yearly data - be that calendar or financial.

What I can up with was the following partitioning function against the date field - this did require having the date field in the primary key, but that wasn't much of a hardship for the benefits it produced...

PARTITION BY HASH (( YEAR(`Period`) + MONTH(`Period`)*4 )) PARTITIONS 24

This create a series of 24 partitions, each containing 3 consecutive months of data, spanning a six year period. Any query accessing a single quarters data (Jan to Mar) would only access one partition. The majority of writes were also against a single partition.

As the years rolled on, and wrapped back to the beginning, the early partitions contain more data than the rest, until the earliest year of data is culled from the table, causing them to drop back to the same rough row-counts as their siblings.

Hope this helps someone?

Dave