MySQL Forums
Forum List  »  Merge Storage Engine

Re: Use of merge tables
Posted by: Robert Freeland
Date: September 03, 2008 12:05AM

We have spent most of this year (2008) trying to implement a partitioned table for an application that analyzes ~100 million rows of data each month. The table has over 1.4 Billion records total. I could write pages on the various issues we've encountered, but in the end, we've concluded that a MySQL partitioned table just isn't suited for this application.

Although our application queries data going back a couple years, most of the "real" activity (INSERTs, UPDATEs, and most SELECTs) is on the very tail end of the dataset. To get acceptable performance from the partitioned table, we had to modify almost all of our queries to include date filters to trigger partition pruning, but this almost always prevented subsequent JOINS on other fields from being indexed. This really dragged down performance.

Then MySQL removed the "REPAIR PARTITION" option from the ALTER TABLE command in 5.1.x, meaning that our entire database is now at risk from a single instance of database corruption. This point was driven home earlier this week when a few entries in the index file on a single monthly partition got corrupted (alarming in itself) and our only solution was to shut the database down and run the myisamchk utility against that individual partition's index file. Using that utility on a single partition is now officially unsupported, so we feared for half a day that the entire database was lost. I guess we could have used CHECK TABLE, but with a 200GB database, the resulting table rewrite would quite literally take weeks.

Given all of these problems, we've decided to try MERGE tables instead. They're admittedly a step back from genuine partitioning, but being able to access the monthly subtables ("partitions") individually is a huge plus. We can REPAIR them individually, run "myisampack" on them individually, and aggregate them in whatever combinations make sense for what we're doing. (It's no mean feat to add a FULL partition back to a partitioned table, BTW, so once you drop a partition of data off the back end, it's gone.)

So anyway, I strongly urge MySQL to keep MERGE tables in the mix -- at least until partitioned tables have a few more years to mature.

Options: ReplyQuote


Subject
Views
Written By
Posted
11282
November 24, 2005 05:42PM
8061
November 25, 2005 02:36AM
7708
December 25, 2005 03:59PM
Re: Use of merge tables
6814
September 03, 2008 12:05AM


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.