amavis-user March 2012 archive
Main Archive Page > Month Archives  > amavis-user archives
amavis-user: Re: SQL Paritioning, and expiring "in the futu

Re: SQL Paritioning, and expiring "in the future"....

From: Mark Martinec <Mark.Martinec+amavis_at_nospam>
Date: Fri Mar 23 2012 - 15:57:41 GMT
To: amavis-users@amavis.org

Bill,

> I want to give my users the ability to set a custom retention period for
> quarantined mail. Some will want it for 2 weeks, some for 4, some for a
> year.
>
> But of course, that makes using partitions more difficult, right? Because I
> can't just drop the partition for data that's x weeks old.
>
> So I was thinking of something, and I can't figure out if I'm missing
> something here. What if I store each message into a partition that is
> based on the week it is to expire. In other words, the partition isn't
> chosen based on today's date, but on the future date of when it should
> expire....
>
> Right now we are in ISO week 12. If I had a customer with a 2 week
> retention period, I could store the message in the partition for ISO week
> 14. If it was for a customer with a 4 week retention period, I could store
> it in the partition for ISO week 16. Or course, I wrap around at the end
> of the year.
>
> And then, instead of expiring past dates, at the end of each week, I expire
> that current week. At the end of week 12, I expire week 12. That way, I'm
> deleting messages when they are supposed to expire.
>
> Anybody see any issues with this? Too many partitions, perhaps? Poor
> database performance because of messages being written to all sorts of
> different partitions all the time? Any mysql experts want to weigh in?

I think it would work.

You could use something like the following in amavisd.conf,
assuming you would add a field 'retention' into a policy SQL table,
where the field would be a numeric retention time interval in seconds,
or a NULL for a built-in default.

BEGIN { import Amavis::Util qw(min max) }

$partition_tag = sub {
  my($msginfo) = @_;
  my $ret_dflt = 2 * 7*24*3600; # two weeks by default
  # retention time interval in seconds, max across all recepients of a message
  my $retention =
    max( map { my($ret,$mk) = Amavis::Lookup::lookup2(0, $_->recip_addr,
                                [ q_sql_n('retention'), $ret_dflt ],
                                Label => 'retention');
               $ret;
             } @{$msginfo->per_recip_data});
  $retention = min(90*24*3600, max(24*3600, $retention)); # clip to 1..90 days
  sprintf("%02d", iso8601_week($msginfo->rx_time + $retention));
};

Mark