Nathan M wrote:
> 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) };
Me too.
> We maintain quarantine for 3 weeks maximum, therefore my suggested
> partitioning looks like this:
Here: 4-6 weeks, sometimes even more, customers have direct access to
the last two weeks.
> 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 instaexpectednce, 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?
Interesting variant. I'm doing as follows:
PARTITION BY LIST (partition_tag) (
PARTITION p0 VALUES IN (0,1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10,11),
PARTITION p2 VALUES IN (12,13,14,15,16,17),
PARTITION p3 VALUES IN (18,19,20,21,22,23),
PARTITION p4 VALUES IN (24,25,26,27,28,29),
PARTITION p5 VALUES IN (30,31,32,33,34,35),
PARTITION p6 VALUES IN (36,37,38,39,40,41),
PARTITION p7 VALUES IN (42,43,44,45,46,47),
PARTITION p8 VALUES IN (48,49,50,51,52,53)
);
Reasoning: If your application asserts that no more than two weeks
(three in your case) will be accessible, you do not need to be very
precise with your garbage collection. Advantage of my variant: I do
not have to touch my partitions if tomorrow I decide to preserve twice
as many weeks. I just drop them later and let my DB grow farther.
> 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)
Before setting up partitioning I did a lot of testing (May 2008, I've
been one of the "early adaptors") and also asked for feedback on this
mailing list. LIST partitioning was the only variant that left, please
see
http://www.mail-archive.com/amavis-user@lists.sourceforge.net/msg11479.html
for my reasoning.
> 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.
52 partitions -> I've also been evaluating this option, but as disk
space is cheep I do not care whether there are too many weeks of
quarantine in my DB or not. 53(!) partitions are possible, but seemed
to be overkill for my needs.
> 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));
Will work as expected. Cleanup took 4 entire days 'til being finished
on the last slave before using partitions. Now it takes just some
seconds :-)
Best regards,
Thomas Gelf
------------------------------------------------------------------------------
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/
