Michael,

> I think I read that the 'partition_tag' can be anything, char(), int,
> anything really, as long as you can define it in the $sql_partition tag
> in amavisd.conf.

Yes, as far as amavisd is concerned.

> however, on the mysql 5.1 site taking about partitions, it says they
> must be integer, or resolve to integer.
>
> Data type of partitioning key.   A partitioning key must be either an
> integer column or an expression that resolves to an integer. The column
> or expression value may also be NULL. (See Section 18.2.6, "How MySQL
> Partitioning Handles NULL".)
>
> http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html
>
> so, our idea of using 'char(6)' and something like yyyy-ww  (so, this
> week would be '2008-47') won't work, because I don't think mysql would
> do something intelligent converting that to an integer.

So why not use an integer week number, as suggested.
Or combine it with a year number into a larger integer.

$sql_partition_tag =
  sub { my($msginfo)[EMAIL PROTECTED]; iso8601_week($msginfo->rx_time) };

or:  (if using %P in quarantine name template and a 2-char field width
is desired):

$sql_partition_tag =
  sub { my($msginfo)[EMAIL PROTECTED]; 
sprintf("%02d",iso8601_week($msginfo->rx_time)) };

> also, the create tables don't take into account partition schemes.
> like: [...]
> and, also the suggestion to delete email by partition didn't include the
> drop partition table type queries.

Right, I didn't provide any howto-with-mysql.

> so, while amavisd-new supports a partition_tag, (which again, deleting
> by partition TAG might be faster), in order to support real mysql
> partitions (including innodb_file_unique.. or whatever that is called),
> you will need to do a lot of work.

Someone would need to provide a guide for each SQL server type.

> upgrading systems in place might be problematic, unless somehow you
> leave 'partition_tag 0' as mysql partition 0, and consider deleting it
> (dropping partition) next year.

Well, yes, or delete it manually in few weeks.

Btw, even without partitioned tables, deleting by partition_tag value is
often faster than a traditional approach with joins, e.g.:

DELETE FROM msgs       WHERE partition_tag >= 30 AND partition_tag <= 46;
DELETE FROM msgrcpt    WHERE partition_tag >= 30 AND partition_tag <= 46;
DELETE FROM quarantine WHERE partition_tag >= 30 AND partition_tag <= 46;
DELETE FROM maddr      WHERE partition_tag >= 30 AND partition_tag <= 46;


  Mark

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
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/ 

Reply via email to