On Fri, Aug 7, 2009 at 5:46 PM, Nathan M<locu.li...@gmail.com> 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) };
>
> 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));
Nobody on the list using partition_tags?
- 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/
