expire-quarentine/process-quarentine table locking.

Michael Osten wrote:
> I'm using v1.0
> 
> Database is Innodb around 17GB with 11k users.  Around
250 queries per
> second.
> 
> Load is handled fine, but when we run expire-quarentine
or
> process-quarentine, these scripts lock 100+ tables, and
the database
> becomes unresponsive.  Any ideas on what I can do so we
can run our
> housekeeping?

I assume you mean 100+ /rows/, as Maia doesn't have that
many tables.
InnoDB tables offer row-level locking, so it should still be
possible
for Maia (and other database processes) to write to
unaffected rows of a
table that's being processed/expired.

You might first of all upgrade to Maia 1.0.1, which made a
lot of
improvements to most of the maintenance scripts (including
process-quarantine and expire-quarantine-cache).

The second thing to recognize is that the longer you go
without running
these maintenance scripts, the more you're going to swamp
your database
when you /do/ finally get those scripts running.  The
process-quarantine
script, for instance, is designed to be run every hour or
two, so if you
go for a week or two without running it, you're going to be
asking it to
do a huge amount of work to deal with the backlog.  The
expire-quarantine-cache script is designed to run once a
day, preferably
at an off-peak time (e.g. the middle of the night), and
doesn't
generally take more than a few minutes to run, but again, if
you don't
run it for a few weeks, obviously it's going to take longer
and run up
your system load.

If you've got a huge amount of backlog to take care of,
your best bet
may simply be to purge it, by telling the process-quarantine
script not
to --learn or --report, and to use a small batch size, e.g.
--limit=5.
If you upgrade to 1.0.1, set $learning_options=0 and
$report_options=0
in the maia.conf file (see
<https://secure.renaissoft.com/maia/wiki/process
-quarantine.pl> for more
details).  You can do something similar with the
expire-quarantine-cache
script, as of 1.0.1, by setting $mail_types=127 in your
maia.conf file
(see <https://secure.renaissoft.com/maia/wiki/ex
pire-quarantine-cache.pl>).

Once you've dealt with your backlog, you can then begin
running the
maintenance scripts on a scheduled basis to keep it from
building up to
those levels again.  Adjust the scheduling intervals to
account for the
load you receive; if process/expiry is taking too long, run
it more often.

Coming in 1.0.2 (likely within the next month or so), the
database
queries throughout Maia (including amavisd-maia, the
maintenance
scripts, and the PHP scripts) are being optimized to put
less strain on
the database, which should also help.

-- 
Robert LeBlanc <rjlrenaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamail
guard.com/>

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users