MySQL Forums
Forum List  »  Partitioning

Re: Week Partitioning
Posted by: Peter Teitelbaum
Date: August 06, 2009 04:02PM

Frank-

I just did something similar. You need to change the partition name to something unique and preferably incremental. I have a table that needed each week's worth of data on a separate partition.

PARTITION BY RANGE (TO_DAYS(date_played)) (
PARTITION y2009w28 VALUES LESS THAN (TO_DAYS('2009-07-13')),
PARTITION y2009w29 VALUES LESS THAN (TO_DAYS('2009-07-20')),
PARTITION y2009w30 VALUES LESS THAN (TO_DAYS('2009-07-27')),
PARTITION y2009w31 VALUES LESS THAN (TO_DAYS('2009-08-03')),
PARTITION y2009w32 VALUES LESS THAN (TO_DAYS('2009-08-10'))
)


Then I wrote the following shell script which would be executed once a week by a cron job to dump the old partition and add a new one. I'm far from an expert when it comes to shell scripting so there may be more efficient ways to do this.

Partition management script - runs weekly:

#!/bin/bash

SCHEMA='YourSchema'
ACTION_TAKEN=0

CURWEEK=y`date +%Y`w`date +%W`;
NEXTWEEK=y`date +%Y -d'+1 week'`w`date +%W -d'+1 week'`;
NEXTWEEKTWO=y`date +%Y -d'+2 week'`w`date +%W -d'+2 week'`;
LASTWEEK=y`date +%Y -d'-1 week'`w`date +%W -d'-1 week'`;
LASTWEEKTWO=y`date +%Y -d'-2 week'`w`date +%W -d'-2 week'`;

# Make temp files
PARTITION_LIST=`mktemp`;
REMOVE_LIST=`mktemp`;

MYSQL='/usr/bin/mysql';

# Get partition list
echo "SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = '$SCHEMA' AND TABLE_NAME = 'YourTable' ORDER BY PARTITION_NAME ASC" | $MYSQL | sed 1d > $PARTITION_LIST

# Get list of partitions to be purged
egrep -v "($LASTWEEKTWO|$LASTWEEK|$CURWEEK|$NEXTWEEK|$NEXTWEEKTWO)" $PARTITION_LIST > $REMOVE_LIST;

echo Actions taken-

# Remove all unecessary partitions
for PARTITION in `cat $REMOVE_LIST`;
do
$MYSQL -D$SCHEMA -e"ALTER TABLE YourTable DROP PARTITION $PARTITION";
echo Removed: $PARTITION
ACTION_TAKEN=1;
done

# Add this week's partition if not present
if [ 0 -eq `grep $CURWEEK $PARTITION_LIST | wc --lines` ]
then
$MYSQL -D$SCHEMA -e"ALTER TABLE YourTable ADD PARTITION (PARTITION $CURWEEK VALUES LESS THAN (TO_DAYS('`date +%Y-%m-%d -d'last monday'`')))";
echo "Added: $CURWEEK (`date +%Y-%m-%d -d'last monday'`)"
ACTION_TAKEN=1;
fi;

# Add next week's partition if not present
if [ 0 -eq `grep $NEXTWEEK $PARTITION_LIST | wc --lines` ]
then
$MYSQL -D$SCHEMA -e"ALTER TABLE YourTable ADD PARTITION (PARTITION $NEXTWEEK VALUES LESS THAN (TO_DAYS('`date +%Y-%m-%d -d'1 monday'`')))";
echo "Added: $NEXTWEEK (`date +%Y-%m-%d -d'1 monday'`)"
ACTION_TAKEN=1;
fi;

# Add +2 weeks partition if not present
if [ 0 -eq `grep $NEXTWEEKTWO $PARTITION_LIST | wc --lines` ]
then
$MYSQL -Dtest -e"ALTER TABLE YourTable ADD PARTITION (PARTITION $NEXTWEEKTWO VALUES LESS THAN (TO_DAYS('`date +%Y-%m-%d -d'2 monday'`')))";
echo "Added: $NEXTWEEKTWO (`date +%Y-%m-%d -d'1 monday'`)"
ACTION_TAKEN=1;
fi;

# Get updated partition list if changes have been made
if [ 1 -eq $ACTION_TAKEN ]
then
echo "SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = '$SCHEMA' AND TABLE_NAME = 'YourTable' ORDER BY PARTITION_NAME ASC" | $MYSQL | sed 1d > $PARTITION_LIST

# No action taken
else
echo None.
fi;

echo
echo Stats-
echo "First: `head -n1 $PARTITION_LIST`"
echo "Last: `tail -n1 $PARTITION_LIST`"
echo "Current: $CURWEEK"
echo "Total: `wc --lines < $PARTITION_LIST`"

# Cleanup
rm -f $PARTITION_LIST
rm -f $REMOVE_LIST

exit 0;


Hope that helps
Peter

Options: ReplyQuote


Subject
Views
Written By
Posted
4692
July 17, 2009 03:25AM
2787
July 18, 2009 12:07PM
2643
July 20, 2009 03:29AM
Re: Week Partitioning
7237
August 06, 2009 04:02PM


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.