-------- Original-Nachricht -------- Datum: Fri, 17 Aug 2007 17:42:55 -0400 Von: Dave McGuire <[EMAIL PROTECTED]> An: amavis-user@lists.sourceforge.net Betreff: Re: [AMaViS-user] excessive time spent in sql-update?
> 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); > On my setup mail_id is the primary key and MySQL automatically adds a unique index for the primary key. Adding another index for mail_id will not do anything bad except: - You added a redundant index - You need more space on the database (for the redundant index) - Inserting data to the database will take more time (maintaining indices is time intensive) > ...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 > 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/ -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger ------------------------------------------------------------------------- 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 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/
