Anybody perhaps with more experience on this topic? I've tried the following, all without success.
I tried adding the following to the mysql server my.cnf file, and restarting mysql table_lock_wait_timeout = 5 innodb_lock_wait_timeout = 5 I believe because the maintenance I am doing is adding an index, the entire table is locked, so the table_lock_wait_timeout is what would be effective, but to be sure I also included the innodb_lock_wait_timeout. These had no effect. The "INSERT INTO quarantine" queries still hang and wait for the lock to end. The wait seems very excessive, maybe 5 minutes or more, I was never patient enough (or wanted to customers to wait for email) to test just how long it waits. I have also tried passing those same two config options within the DSN connect options. ie.. @storage_sql_dsn = ( ['DBI:mysql:database=amavisd_stats;host=mysql.x;port=3306;table_lock_wait_timeout=5;innodb_lock_wait_timeout=5', 'amavisd_stats', 'password'] ); No luck there either, timeouts seem to still wait for a very long time. I'm not sure if what I need to do is within mysql or amavisd at this point. I'd love to just have amavisd give up attempting to insert into quarantine sooner so it goes into the deferred queue and retrys later when postfix reattempts. This would let the particular amavisd instances timeout quickly, and allow non quarantine email to flow smoothly. The goal is to only defer quarantined messages (inserts into quarantine table), and let all other email continue to flow. I'm open to other ways to accomplish this too. Anyone? Thanks, N On Mon, Jul 27, 2009 at 3:58 PM, Nathan M<locu.li...@gmail.com> wrote: > Hello, > > We have roughly a 50GB quarantine table on a MySQL InnoDB database. > Running amavisd-new-2.6.2. I want to do some maintenance on the > quarantine table; however, am concerned it is going to take many many > hours to complete. My goal is to not stop email delivery; however, I > do not mind holding up quarantine emails destined for the SQL > quarantine table. > > I believe if I perform my maintenance as intended the inserts into the > quarantine table will eventually overwhelm the available amavisd > processes on the server while waiting for the SQL lock to finally > timeout and defer the message. > > What's an elegant way to overcome this? > > I'm thinking, if I can tell Amavis to give-up on a locked db after 5 > seconds (instead of whatever the default is, seems like it's maybe > 60s?) those messages would just end up deferred throughout the > maintenance and postfix cleanup will re-present them to amavisd for > quarantine later on. Thoughts on this process? Is there a simple way > to crank down the insert timeout in Amavis? Perhaps an additional > argument in the @storage_sql_dsn config parameter? > > Thank you, > Nathan > ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ 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/
