Le jeudi 13 août 2009 18:15:22, Nathan M a écrit :
> 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/
Just a little precision,
What day is the increment of the week, sunday or monday?
:)
LD
------------------------------------------------------------------------------
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
Please visit http://www.ijs.si/software/amavisd/ regularly
For administrativa requests please send email to rainer at openantivirus dot
org
