[AMaViS-user] Disk usage UP during nightly cleanup

Hi.
Running here amavisd-new 2.3.3 with quarantine to MySQL 4.1.14 InnoDB
under Linux. Postfix, SA and ClamAV complete the setup.

Every night the quarantine cleanup script is run. A "du -ks" of MySQL
quarantine DB says 13.5GB are occupied.

During the nightly cleanup I see from MRTG that the disk occupation
grows from 20GB to 31GB, that is +11GB.

This is what the "slow-queries" log reports (queries are taken from
amavis docs):

# Time: 060113 4:07:00
# User@Host: root[root] @ localhost []
# Query_time: 130 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET timestamp=1137121620;
DELETE FROM msgs WHERE (time_num < UNIX_TIMESTAMP() - 30*24*60*60) OR
(time_num < UNIX_TIMESTAMP() - 15*24*60*60 AND (content
='V' OR (content='S' AND spam_level>20))) OR (time_num <
UNIX_TIMESTAMP() - 60*60 AND content IS NULL);

# Time: 060113 4:24:22
# User@Host: root[root] @ localhost []
# Query_time: 1042 Lock_time: 0 Rows_sent: 0 Rows_examined: 2676122
DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;

# Time: 060113 4:46:11
# User@Host: root[root] @ localhost []
# Query_time: 1309 Lock_time: 0 Rows_sent: 0 Rows_examined: 4693355
DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;

# Time: 060113 4:54:38
# User@Host: root[root] @ localhost []
# Query_time: 506 Lock_time: 0 Rows_sent: 0 Rows_examined: 710813
DELETE FROM maddr
WHERE NOT EXISTS (SELECT sid FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT rid FROM msgrcpt WHERE rid=id);

So, the last query ends at 04:54:38, but then something else happens and
eats up the disk space.

I've monitored throughout the weekend processes and disk space at 5
minutes intervals, and noticed that the cron.daily runs much longer.

The mysql process forked by cron.daily at 04:07 goes on and on until
05:35, long after the last DELETE has officially ended. The disk
occupation peak occurs at 05:35. At the next check (05:40), cron.daily
is gone and disk back to the normal value.

This is probably something related to the MySQL transactional engine,
but I'm not an expert there. Should I turn off transactions when
cleaning quarantine? (How?) Anybody else noticed this behaviour?

I cannot really double the disk space just to have room for the cleanup,
whose action should free space instead!

I'm willing to run other tests you might suggest, but only overnight and
without human monitor. I cannot run the quarantine cleanup script during
working hours because DB timeouts on amavisd cause deferred messages.

Paolo



-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/...fo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/