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?
>
OK, everything I've tried has failed as far as failing quicker on a
locked table. It seems all the settings just quietly go ignored.
Quite aggravating. I've tried wait_timeout, table_lock_wait_timeout,
innodb_lock_wait_timeout both global and local. Still I've waited up
to 10 minutes for these to timeout with amavis and they don't. I'm
not sure when they eventually do.
Here's a pretty ugly, but seemingly effective solution I believe will
work, which I may try out this weekend.
Instead of attempting to modify the timeout so it times out sooner and
goes back to the deferred queue, if I monkey with the SQL insert
statement itself and invalidate it within amavisd. Something like:
'ins_quar' =>
'INSERT INTO quarantine (partition_tagBREAKME, mail_id,
chunk_ind, mail_text)'.
' VALUES (?,?,?,?)',
That produces:
451-4.5.0 Error in processing, id=12287-01, quar+notif FAILED:
temporarily unable to quarantine: 451 4.5.0 Storing to sql db as
mail_id qMlq3hbU0so2 failed: writing mail text to SQL failed: Error
closing, flush: sql inserting text failed, sql exec: err=1054, S1000,
DBD::mysql::st execute failed: Unknown column 'partition_tagBREAKME'
in 'field list' at (eval 98) line 166, <GEN1474> line 218
Which throws only messages destined for the quarantine back into the
deferred queue.
Once maintenance is complete, I can repair my "wrench" in amavisd, and
just postfix flush, and they all should be picked up from deferred
queue, and re-processed through amavisd with the unbroken query.
This took me way too long to figure out. I'm hoping my theory is solid.
Anybody more familiar with amavisd than I have any notes of caution
about this plan before I attempt it on live data?
Thanks. If this works, I'd like to throw in a feature request within
amavisd to include a config setting that handles this type of scenario
more gracefully; something like:
$defer_quarantine = 0; // Set to 1 to automatically defer anything
destined for quarantine (useful for db maint)
- N
------------------------------------------------------------------------------
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/
