partrotate_unixtimestamp.pl

partrotate_unixtimestamp.pl

$maxparts = 6; #6 days How long keep the data in the DB
$newparts = 2; #new partitions for 2 days. Anyway, start this script daily!
@stepsvalues = (86400, 3600, 1800, 900);
$partstep = 0; # 0 - Day, 1 - Hour, 2 - 30 Minutes, 3 - 15 Minutes

Mysql partitioning allows a large table to be segmented, storing each segment in a different file. This allows multiple disks to be used for a single table to increase efficiency. This also allows for increased query times when a WHERE statement including the partitioning index includes, allowing mysql to only have to search the records in the relevant partitions and not all the data in the table.

The Mysql database table storing the captures is partitioned using range partitioning by the unix timestamp of the date column. By default, $partstep is defined as 1 day, and so each partition contains one days worth of captures. Every night when the cron job is run, the oldest partition is deleted, and a new partition is created. The "VALUES LESS THAN MAX VALUE" partition always exists as a catchall in case an INSERT is performed with a date before the last specifically defined partition so an error will not be returned.

The partitions are named using the format pYearMonthDayHour with minutes being included in the name if the $partstep is more frequent than every hour ( > 1). p2013081620

PARTITION BY RANGE ( UNIX_TIMESTAMP(`date`))
(PARTITION p2013081620 VALUES LESS THAN (1376784000) ENGINE = MyISAM,
 PARTITION p2013081720 VALUES LESS THAN (1376870400) ENGINE = MyISAM,
 PARTITION p2013081820 VALUES LESS THAN (1376956800) ENGINE = MyISAM,
 PARTITION p2013081920 VALUES LESS THAN (1377043200) ENGINE = MyISAM,
 PARTITION p2013082020 VALUES LESS THAN (1377129600) ENGINE = MyISAM,
 PARTITION p2013082120 VALUES LESS THAN (1377216000) ENGINE = MyISAM,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)


$maxparts - defines how many partitions will be created in the database. This is a bit confusing because $newparts defines the number of the partitions in the future. So really there are ($maxparts - $newparts) partitions of historical data, plus the partial current day, and ($newparts - 1) partitions covering future days.

$newparts - defines how many of the defined partitions will be in the future. For example when $newparts = 2,