On Aug 17, 2007, at 4:48 PM, Dave McGuire wrote:
> Hi folks. I've been handholding one of my mail servers for most
> of today as it deals with a large backlog of messages that are
> seemingly the result of a large flood of spam. In the TIMING log
> entries, amavisd-new (v2.4.5) is spending what I consider to be an
> excessive amount of time in "sql-update"...upwards of nine seconds in
> some cases, when the entire run is taking close to twenty seconds. (!)
>
> In my installation, amavisd-new is logging and quarantining to an
> instance of MySQL running on a dedicated, reasonably quick database
> server located on the local network.
>
> My forte' is C; I'm not much of a Perl hacker, otherwise I'd dig
> in and find out exactly what's happening in that routine. Can
> someone shed a little light on this for me? Specifically, I'd like
> to understand specifically what's going on in that routine (i.e.,
> "it's updating table ABC with information XYZ") and I'd really like
> some suggestions on how to identify the bottleneck so I can address
> it.
>
> Does anyone have any thoughts on this?
Please forgive me for replying to my own message here, but I'd
like to share some information about some significant progress I've
just made. I managed to catch one of amavisd-new's SQL transactions
in MySQL's "show full processlist" output, and it was executing an
update query on the msgs table with a search field (mail_id) that
wasn't indexed. I added a new index on that field thusly:
CREATE INDEX msgs_mail_id ON msgs (mail_id);
...and the average (eyeballed) query times have dropped from 8-15
seconds to less than 100ms, in some cases less than 20ms.
I'm currently digging for the documentation I used to create those
tables to see if I missed the need for that index.
Can anyone share any other tips here? What other indices are
people using to improve query performance on their database-backed
amavisd-new installations?
-Dave
--
Dave McGuire
Port Charlotte, FL
Farewell Ophelia, 9/22/1991 - 7/25/2007
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
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/
