Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
> > 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)); Not to forget that some years have 53 weeks (1..53). Mark -- ___ 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
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
> Just a little precision, > > What day is the increment of the week, sunday or monday? > > :) > if you want consistency between amavis, php, perl and mysql, you need to use monday morning as the first day of the new week (iso standards allow either, and mysql can let you use either, but the perl routine amavis uses uses monday) -- Michael Scheidell, CTO Phone: 561-999-5000, x 1259 > *| *SECNAP Network Security Corporation * Certified SNORT Integrator * 2008-9 Hot Company Award Winner, World Executive Alliance * Five-Star Partner Program 2009, VARBusiness * Best Anti-Spam Product 2008, Network Products Guide * King of Spam Filters, SC Magazine 2008 __ This email has been scanned and certified safe by SpammerTrap(r). For Information please see http://www.secnap.com/products/spammertrap/ __ -- ___ 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
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
Le jeudi 13 août 2009 18:15:22, Nathan M a écrit :
> On Fri, Aug 7, 2009 at 5:46 PM, 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) };
> >
> > 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
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
Mark Martinec wrote: >> I'm actually kinda leaning towards 52 >> partitions at this point for maximum speed and flexibility >> while using iso8601 week as the value. > > Not to forget that some years can have 53 weeks (1..53) > according to ISO 8601. >> 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. Dito ;-) -- 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/
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
Nathan, > I'm actually kinda leaning towards 52 > partitions at this point for maximum speed and flexibility > while using iso8601 week as the value. Not to forget that some years can have 53 weeks (1..53) according to ISO 8601. Mark -- 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/
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
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/
Re: [AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
On Fri, Aug 7, 2009 at 5:46 PM, 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) };
>
> 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/
[AMaViS-user] Mysql 5.1 + Partitioning Storage Schema / Cleanup
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/
