MySQL Forums
Forum List  »  Partitioning

Archiving Partition .MYD files
Posted by: Dominic Edwards
Date: December 11, 2009 05:41AM

I am using a MySQL 5.1 database with tables that will get very large over time and therefore I am looking to utilise partitions with a MyISAM storage engine as a means to improve performance and manage the archiving of the older partitions in order to save disk space. I am using Windows OS for my initial investigations currently but the eventual solution will be deployed on Linux.

I am proposing to partition on a monthly date range in order to take advantage of partition pruning creating tables with syntax such as:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `anomalyreportfact`
-- ----------------------------
DROP TABLE IF EXISTS `anomalyreportfact`;
CREATE TABLE `anomalyreportfact` (
`TimeIdentifier` bigint(20) DEFAULT NULL,
`DateIdentifier` bigint(20) DEFAULT NULL,
`ProjectIdentifier` bigint(20) DEFAULT NULL,
`Title` varchar(255) DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`Resolution` varchar(255) DEFAULT NULL,
`FactDate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( TO_DAYS(FactDate))
(PARTITION p20090801 VALUES LESS THAN (733985)
DATA DIRECTORY = 'C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data/test/partition1/',
PARTITION pMax VALUES LESS THAN MAXVALUE
DATA DIRECTORY = 'C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data/test/partition2/'
ENGINE = MyISAM);

On a monthly basis a job will then run to manage partitions using syntax such as:

ALTER TABLE anomalyreportfact DROP PARTITION pMax;
ALTER TABLE anomalyreportfact ADD PARTITION (PARTITION p20091210 VALUES LESS THAN (TO_DAYS('2009-12-10')) ENGINE = MyISAM);
ALTER TABLE anomalyreportfact ADD PARTITION (PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM);

Over time therefore, for each table, I can expect to see an incrementing set of .MYD files corresponding to my partitions in my MySQL data directory, along with the relevant .par and .frm files for the table.

My question is how to go about archiving SOME older partitions to free up disk space whilst not archiving the table as a whole. I was proposing simply to shut down the MySQL server maybe once a year and to copy the relevant .MYD, frm, MYI and .par files to another disk and delete the files I had copied. One preliminary question would be - will that method work?

Assuming it would, the main problem as I see it is how to archive partitions as .MYD files along with relevant .par and .frm files to read them since the .MYD files relate to the partition and the other two relate to the table as a whole. In other words can partition .MYD files be read using .frm and .par files that also reference OTHER .MYD files?

I have a feeling I may have overlooked a simple solution to this issue so perhaps someone could point me at a good article somewhere, I have scratched around but not been able to find anything thus far.

Thanks in advance.

Dominic Edwards.

Options: ReplyQuote


Subject
Views
Written By
Posted
Archiving Partition .MYD files
5603
December 11, 2009 05:41AM
2723
December 11, 2009 05:48AM
2668
December 12, 2009 03:48PM
2899
December 22, 2009 03:00AM


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.