Peter, > A while ago, I changed over to storing my quarantine, etc. in a mysql db. > So far it has worked wonderfully except for one problem. > > The DB has grown to about 5GB (ibdata file), and every night, when I am > using the script included in the README.mysql to get rid of old entries, I > get the following error: > > Sep 9 03:18:54 george.eliot.priv.at /usr/sbin/amavisd[14068]: (14068) > (!!)TROUBLE in check_mail: quar+notif FAILED: temporarily unable to > quarantine: 451 4.5.0 Storing to sql db as mail_id MSQokJpLajBT failed: > writing mail text to SQL failed: Error closing, flush: sql inserting text > failed, sql exec: err=1205, S1000, DBD::mysql::st execute failed: Lock > wait timeout exceeded; try restarting transaction... > > I realise this is a SQL issue, I was just wondering if anyone has > experience with this, or better still a solution.
My solution to this problem was to switch SQL logging to PostgreSQL. An order of magnitude faster purge, and amavisd is able to do its job even during SQL purging. An alternative solution with MySQL is to partition a database e.g. into weekly subtables - then a purge becomes a quick DROP and CREATE operation. Seems to be rarely practiced because of its complexity to a database administrator, but is should definitely be the snappiest solution. > I am using amavisd-new-2.6.1. The machine gets a really high load, when it > does the above, so much so, that sometimes I also get the following > errors: > > Sep 9 03:48:46 george.eliot.priv.at /usr/sbin/amavisd[17556]: (17556) > (!!)TROUBLE in process_request: Can't write response to socket: Broken > pipe, fileno=13 at (eval 82) line 145. This happens when processing a mail by amavisd takes longer than MTA is willing to wait. Try increasing the smtp_data_done_timeout for the Postfix smtp service feeding amavisd. Extremely long processing times are worth investigating, feeding a mail sample to a command line spamassassin. Perhaps there are certain rules or stale DNS/RBL services that can be eliminated. Moving a large Bayes from a file-based database to MySQL also helps greatly. > Sep 9 03:50:31 george.eliot.priv.at /usr/sbin/amavisd[21872]: (21872) > (!!)TROUBLE in check_mail: creating_partsdir FAILED: Can't create > directory /var/amavis/tmp/afm891dgsv019798/parts: No such file or > directory at /usr/sbin/amavisd line 9734. Are you purging /var/amavis/tmp/ with some cron job? It must take care not to delete any temporary directories that are in use by running amavisd child processes. > This unfortunately happens pretty much every night, and I am afraid, > I may be loosing important mails! No mail is lost. When a failure occurs, a message stays in a MTA queue for a retry. If it was a temporary failure it is likely that on the next MTA attempt the message will get through. In the first case (Can't write response to socket) a recipient may receive the same message more than once. When a cause of a catastrophic failure is due to a contents of a mail triggering some programming error, than mail processing will likely cause the same failure on every attempt. After a couple of days MTA gives up and sends a NDN back to a sender. It is worth keeping an eye on the jobs in a MTA queue to see if there are any entries which failed on feeding to amavisd, investigate and fix the cause. Some SARE complex regexp rules or third part SA plugins are likely causes for such rare events. 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 [email protected] https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
