Hi, i have the similar problem like http://marc.info/?l=amavis-user&m=113578220822655&w=2
I user amavis-new 2.4.2 with 100 processes on a server with 8 GB RAM and 4 CPU 3.40GHz. I don't have performance problems. I use Red Hat Enterprise Linux ES release 4 with perl-BerkeleyDB-0.27-1.2 and db4-4.2.52-7.1. The daily mailtraffic is 1.000.000 incoming (95 % Spammails) on 2 server. When I use the daily quarantine cleanup script every night the DB are always locked for this (long) time. The ceanup script is: (README.sql) 1. DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60; 2. DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content IS NULL; 3. DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id); 4. DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id); 5. DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); 6. OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr; The time for the script is 3-4 ours and in this time the DB is always locked and so i have a lot of mail in mailq and in the TMP directory of amavis I have a lot of new directorys. My problem is, that in the night I could not receive mails for some ours. The mails goes in the mailq and then ours after I receive the mails. So I have alwas to delete the new direcotries in the TMP of Amavis. Here's the log excerpt from one of the messages that got deferred: Jul 9 02:02:55 mailserver postfix/smtpd[3937]: A89B423405D: client=unknown[60.212.142.152] Jul 9 02:03:01 mailserver postfix/cleanup[4964]: A89B423405D: message-id=<[EMAIL PROTECTED]> Jul 9 02:03:08 mailserver postfix/qmgr[18226]: A89B423405D: from=<[EMAIL PROTECTED]>, size=16124, nrcpt=1 (queue active) Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) SEND via SQL (DBI:mysql:database=amavisdb;host=localhost;port=3306): <[EMAIL PROTECTED]> -> <[EMAIL PROTECTED]>, mail_id qb6tlabrf46l Jul 9 02:16:38 mailserver postfix/smtpd[7492]: disconnect from nobelium.inotronic.de[83.136.129.229] Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) writing mail text to SQL failed: Error closing, flush: sql inserting text failed, sql exec: err=1216, S1000, DBD::mysql::st execute failed: Cannot add or update a ch ild row: a foreign key constraint fails at (eval 39) line 153, <GEN259> line 542. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line 68, <GEN259> line 542. Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) mail_via_sql: rollback done Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!!) TROUBLE in check_mail: quar+notif FAILED: temporarily unable to quarantine: 451 4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed: writing mail text to SQL failed: Error closing, flush: sql inserting text failed, sql exec: err=1216, S1000, DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails at (eval 39) line 153, <GEN259> line 54 2. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line 68, <GEN259> line 542. at (eval 43) line 293, <GEN259> line 542., id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542. Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!) PRESERVING EVIDENCE in /var/amavis/tmp/amavis-20070709T021207-24584 Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) TIMING [total 270244 ms] - mkdir tempdir: 0 (0%)0, create email.txt: 0 (0%)0, SMTP pre-DATA-flush: 2 (0%)0, SMTP DATA: 84 (0%)0, body_digest: 1 (0%)0, sql-enter: 266 092 (98%)98, mkdir parts: 10 (0%)98, mime_decode: 40 (0%)99, get-file-type3: 19 (0%)99, decompose_part: 1 (0%)99, parts_decode: 0 (0%)99, AV-scan-1: 29 (0%)99, spam-wb-list: 2 (0%)99, SA msg read: 1 (0%)99, SA parse: 3 ( 0%)99, SA check: 3930 (1%)100, SA finish: 7 (0%)100, update_cache: 1 (0%)100, decide_mail_destiny: 1 (0%)100, write-header: 5 (0%)100, fwd-sql: 13 (0%)100, rundown: 1 (0%)100 Jul 9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D: to=<[EMAIL PROTECTED]>, relay=127.0.0.1[127.0.0.1], delay=826, status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0 Error in processing, id= 24584-09-9, quar+notif FAILED: temporarily unable to quarantine: 451 4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed: writing mail text to SQL failed: Error closing, flush: sql inserting text failed, sql exec: err =1216, S1000, DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails at (eval 39) line 153, <GEN259> line 542. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line 68, <GEN 259> line 542. at (eval 43) line 293 451 4.5.0 , <GEN259> line 542., id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542. (in reply to end of DATA command)) Jul 9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D: to=<[EMAIL PROTECTED]>, relay=127.0.0.1[127.0.0.1], delay=826, status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0 Error in processing, id=24584-09-9, quar+notif FAILED: temporarily unable to quarantine: 451 4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed: writing mail text to SQL failed: Error closing, flush: sql inserting text failed, sql exec: err=1216, S1000, DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails at (eval 39) line 153, <GEN259> line 542. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line 68, <GEN259> line 542. at (eval 43) line 293 451 4.5.0 , <GEN259> line 542., id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542. (in reply to end of DATA command)) Jul 9 02:42:53 mailserver postfix/qmgr[18226]: A89B423405D: from=<[EMAIL PROTECTED]>, size=16124, nrcpt=1 (queue active) Jul 9 02:43:00 mailserver postfix/smtp[14831]: A89B423405D: to=<[EMAIL PROTECTED]>, relay=127.0.0.1[127.0.0.1], delay=2408, status=sent (254 2.7.1 Ok, discarded, id=11361-05-3 - SPAM) Jul 9 02:43:00 mailserver postfix/qmgr[18226]: A89B423405D: removed My 3 questaons are: 1. Exist some solution to cleanup the DB without locking long time. 2. It is vetter to delete first the INDEX DROP INDEX msgs_idx_sid ON msgs; DROP INDEX msgrcpt_idx_rid ON msgrcpt; DROP INDEX msgrcpt_idx_mail_id ON msgrcpt; and after cleaning reindex the INDEX? CREATE INDEX msgs_idx_sid ON msgs (sid); CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid); CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id); 3. Some other people has the same problem? I would use a TMP in the RAM, but if I have this problem I could use it. Thanks! Egon README.sql: BRIEF MySQL EXAMPLE of a log/report/quarantine database housekeeping ==================================================================== DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP() - 14*24*60*60; DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP() - 60*60 AND content IS NULL; DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); BRIEF MySQL EQUIVALENT EXAMPLE based on time_iso if its data type is TIMESTAMPS =============================================================================== (don't forget to set: $timestamp_fmt_mysql=1 in amavisd.conf) DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 14 day; DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 1 hour AND content IS NULL; DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); BRIEF PostgreSQL EXAMPLE of a log/report/quarantine database housekeeping ========================================================================= DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14 days'; DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS NULL; DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); COMMENTED LONGER EXAMPLE of a log/report/quarantine database housekeeping ========================================================================= -- discarding indexes makes deletion faster; if we expect a large proportion -- of records to be deleted it may be quicker to discard index, do deletions, -- and re-create index (not necessary with PostgreSQL, may benefit MySQL); -- for daily maintenance this does not pay off --DROP INDEX msgs_idx_sid ON msgs; --DROP INDEX msgrcpt_idx_rid ON msgrcpt; --DROP INDEX msgrcpt_idx_mail_id ON msgrcpt; -- delete old msgs records based on timestamps only (for time_iso see next), -- and delete leftover msgs records from aborted mail checking operations DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60; DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content IS NULL; -- provided the time_iso field was created as type TIMESTAMP DEFAULT 0 (MySQL) -- or TIMESTAMP WITH TIME ZONE (PostgreSQL), instead of purging based on -- numerical Unix timestamp as above, one may select records based on ISO 8601 -- UTC timestamps. This is particularly suitable for PostgreSQL: --DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14 days'; --DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS NULL; and is also possible with MySQL, using slightly different format: --DELETE FROM msgs -- WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 14 day; --DELETE FROM msgs -- WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 1 hour AND content IS NULL; -- optionally certain content types may be given shorter lifetime --DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-7*24*60*60 -- AND (content='V' OR (content='S' AND spam_level>20)); -- (optional) just in case the ON DELETE CASCADE did not do its job, we may -- explicitly delete orphaned records (with no corresponding msgs entry); -- if ON DELETE CASCADE did work, there should be no deletions at this step DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id); DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id); -- re-create indexes (if they were removed in the first step): --CREATE INDEX msgs_idx_sid ON msgs (sid); --CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid); --CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id); -- delete unreferenced e-mail addresses DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id); -- (optional) optimize tables once in a while --OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr; ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ 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/