partition_tag is a key, yes use it, and DON'T use 'in' (as:
partition_tag in (18,19)
or partition_tag between, etc. some versions of mysql mess optimization up.
(test yourself) Mark Martinec: see below, I mentioned the db
optimization stuff with one of the queries before: maybe we can get this
into next rev?
we use an isoyearweek (due to archiving), and use a 'raid 5 ish' type,
so that like yours, each week is in a different partition (no two weeks
are in one)
(raid 5ish, not in ECC, but in the way raid 5 might balance or strip
data if set up right)
/*!50100 PARTITION BY LIST (partition_tag)
(PARTITION p20101 VALUES IN
(201001,201006,201011,201016,201021,201026,201031,201036,201041,201046,201051),
PARTITION p20102 VALUES IN
(201002,201007,201012,201017,201022,201027,201032,201037,201042,201047,201052),
PARTITION p20103 VALUES IN
(201003,201008,201013,201018,201023,201028,201033,201038,201043,201048,201053),
PARTITION p20104 VALUES IN
(201004,201009,201014,201019,201024,201029,201034,201039,201044,201049),
PARTITION p20105 VALUES IN
(201005,201010,201015,201020,201025,201030,201035,201040,201045,201050)) */;
(we keep 4 weeks of partition which is why we use 5 partitions, for two
weeks, you would need only 3 sets of partitions)
its also important for your weekly cleanup to DROP and recreate the
partitions, not just delete from them (optimization, speed, etc)
and, check to see if this was fixed: (noop, its not:
Mark: this works a LOT FASTER then the default: (and, partition_tag is
NEVER null, by default it will be 0 unless you change it)
$sql_clause{sel_quar}=
"SELECT mail_text FROM quarantine".
" WHERE partition_tag =? AND mail_id=?".
" ORDER BY chunk_ind";
this screws up the optimizer:
'SELECT mail_text FROM quarantine'.
' WHERE coalesce(partition_tag,0)=coalesce(?,0) AND mail_id=?'.
' ORDER BY chunk_ind',
--
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 in Email Security,2010: 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/
______________________________________________________________________
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
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
