I'm open to suggestions on this front from anybody actively using
mysql and partitions. I am using the following config:
$sql_partition_tag = sub { my($msginfo)=...@_; iso8601_week($msginfo->rx_time)
};
We maintain quarantine for 3 weeks maximum, therefore my suggested
partitioning looks like this:
ALTER TABLE quarantine PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (1,5,9,13,17,21,25,29,33,37,41,45,49),
PARTITION p1 VALUES IN (2,6,10,14,18,22,26,30,34,38,42,46,50),
PARTITION p2 VALUES IN (3,7,11,15,19,23,27,31,35,39,43,47),
PARTITION p3 VALUES IN (4,8,12,16,20,24,28,32,36,40,44,48,52));
Reasoning. Since i maintain a maximum 3 weeks mail data, I can drop
whichever partition is out of range. For instance, if it's week 31, I
want to retain weeks 31, 30, and 29 of email. I drop p3 in this case
and it wipes out week 28. The methodology seems a little clunky
though because it limits what I can purge later on if we change our
retention policy so I may rethink this. Another possibility would be
to create 52 partitions, one for each week. Thoughts? Better ideas?
I've seen some other suggestions which all seem to follow this similar
theme both in LESS THAN and LIST formats accomplishing similar things:
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (12),
PARTITION p2 VALUES LESS THAN (18),
PARTITION p3 VALUES LESS THAN (24),
PARTITION p4 VALUES LESS THAN (30),
PARTITION p5 VALUES LESS THAN (36),
PARTITION p6 VALUES LESS THAN (42),
PARTITION p7 VALUES LESS THAN (48),
PARTITION p8 VALUES LESS THAN (54)
It would seem to me this further limits options as it requires
maintaining a minimum of 6 weeks of data. Open to ideas others have
implemented with success. I'm actually kinda leaning towards 52
partitions at this point for maximum speed and flexibility while using
iso8601 week as the value.
Next up is the cleanup scripts, but they seem fairly simple. Given my
first example, something along the lines of the 2 queries below would
probably work. Totally untested as of yet.
if ($week == 31)
ALTER TABLE quarantine DROP PARTITION p3;
ALTER TABLE quarantine ADD PARTITION (PARTITION p3 VALUES IN
(4,8,12,16,20,24,28,32,36,40,44,48,52));
- N
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
