MySQL Forums
Forum List  »  Partitioning

Re: Partition Management over rolling 180 days
Posted by: Benjamin Burleson
Date: November 01, 2007 06:10PM

Well, nothing too slick, but it works..

I created this Perl script and invoke it daily at 23:59 with a cron job.

-------------------------START SCRIPT----------------------

#!/usr/bin/perl
#
use strict;
use CGI qw(param header);
use DBI;
use Date::Calc;

my ($sec, $min, $hour, $curmday, $curmon, $curyear,
$wday, $yday, $isdst) = localtime time;
$curyear = $curyear + 1900;
$curmon = $curmon + 1;
my $dateToday = $curyear.sprintf("%02d",$curmon).sprintf("%02d",$curmday);
my ($year,$mon,$mday) = Date::Calc::Add_Delta_Days($curyear,$curmon,$curmday,1);
my $pAdd = "p".$year.sprintf("%02d",$mon).sprintf("%02d",$mday);
my $dateTomorrow = $year."-".sprintf("%02d",$mon)."-".sprintf("%02d",$mday);
my $pReorg = "p".$dateToday;
($year,$mon,$mday) = Date::Calc::Add_Delta_Days($curyear,$curmon,$curmday,-7);
my $dateWeekAgo = $year.sprintf("%02d",$mon).sprintf("%02d",$mday);
my $pDrop = "p".$dateWeekAgo;

# For unknown reason, multiple alters
# do not work!
#my $query = "ALTER TABLE RecentDataP\n";
#$query .= " DROP PARTITION ".$pDrop.",\n";
#$query .= " REORGANIZE PARTITION ".$pReorg." INTO\n";
#$query .= " (PARTITION ".$pReorg." VALUES ";
#$query .= "LESS THAN (TO_DAYS('".$dateTomorrow."')),\n";
#$query .= " PARTITION ".$pAdd." VALUES LESS THAN MAXVALUE);\n";

my $drop_query = "ALTER TABLE RecentDataP\n";
$drop_query .= " DROP PARTITION ".$pDrop;

my $reorg_query = "ALTER TABLE RecentDataP\n";
$reorg_query .= " REORGANIZE PARTITION ".$pReorg." INTO\n";
$reorg_query .= " (PARTITION ".$pReorg." VALUES ";
$reorg_query .= "LESS THAN (TO_DAYS('".$dateTomorrow."')),\n";
$reorg_query .= " PARTITION ".$pAdd." VALUES LESS THAN MAXVALUE)";

#print $drop_query;
#print "\n";
#print $reorg_query;

my $dsn = 'DBI:mysql:schema:host:port';
my $user = 'user';
my $pass = 'pass';
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can't connect to the DB: $DBI::errstr\n";

my $sth = $dbh->prepare($drop_query);
$sth->execute();

my $sth = $dbh->prepare($reorg_query);
$sth->execute();

$dbh->disconnect();

-------------------------END SCRIPT----------------------

I initially created the table by hand. I'm keeping only
1 week worth of data partitioned by day.

CREATE TABLE `RecentDataP` (
`SourceId` int(11) NOT NULL DEFAULT '0',
`DatumId` int(11) NOT NULL DEFAULT '0',
`Value` varbinary(64) DEFAULT NULL,
`TimeSec` int(11) NOT NULL DEFAULT '0',
`TimeNsec` int(11) NOT NULL DEFAULT '0',
`Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `rd_idx_TimeSec` (`TimeSec`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (TO_DAYS(Date)) (PARTITION p20071025 VALUES LESS THAN (733340) ENGINE = MyISAM, PARTITION p20071026 VALUES LESS THAN (733341) ENGINE = MyISAM, PARTITION p20071027 VALUES LESS THAN (733342) ENGINE = MyISAM, PARTITION p20071028 VALUES LESS THAN (733343) ENGINE = MyISAM, PARTITION p20071029 VALUES LESS THAN (733344) ENGINE = MyISAM, PARTITION p20071030 VALUES LESS THAN (733345) ENGINE = MyISAM, PARTITION p20071031 VALUES LESS THAN (733346) ENGINE = MyISAM, PARTITION p20071101 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */


And here is the result after running just before midnight UTC:

CREATE TABLE `RecentDataP` (
`SourceId` int(11) NOT NULL DEFAULT '0',
`DatumId` int(11) NOT NULL DEFAULT '0',
`Value` varbinary(64) DEFAULT NULL,
`TimeSec` int(11) NOT NULL DEFAULT '0',
`TimeNsec` int(11) NOT NULL DEFAULT '0',
`Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `rd_idx_TimeSec` (`TimeSec`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (TO_DAYS(Date)) (PARTITION p20071026 VALUES LESS THAN (733341) ENGINE = MyISAM, PARTITION p20071027 VALUES LESS THAN (733342) ENGINE = MyISAM, PARTITION p20071028 VALUES LESS THAN (733343) ENGINE = MyISAM, PARTITION p20071029 VALUES LESS THAN (733344) ENGINE = MyISAM, PARTITION p20071030 VALUES LESS THAN (733345) ENGINE = MyISAM, PARTITION p20071031 VALUES LESS THAN (733346) ENGINE = MyISAM, PARTITION p20071101 VALUES LESS THAN (733347) ENGINE = MyISAM, PARTITION p20071102 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Enjoy!
-Ben

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partition Management over rolling 180 days
4877
November 01, 2007 06:10PM


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.