Re: Partition Management over rolling 180 days
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