-------- Original-Nachricht -------- Datum: Fri, 17 Aug 2007 20:12:19 -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 7:01 PM, [EMAIL PROTECTED] wrote: > >> 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) > > Yup, that makes sense to me. > > A "show index ..." didn't list an index on that field, which > suggests that it wasn't defined as a primary key in the original > CREATE TABLE statement. I double-checked the CREATE TABLE statements > that I used to set up this database, and sure enough, there was no > primary key defined. > Strange. You should have there a primary key. Anway... can you check http://www.ijs.si/software/amavisd/README.sql-mysql and fix/adjust your table to match the one described in the document (assuming you run version 2.5.2. If not then search for the file on your local system). If speed is an issue for you, then don't create the foreign keys since they slow down MySQL when purging old data from the AMaViS database. > The flood has died down and my mail server is now keeping up. > I've also tuned the MySQL server a bit (adjusted some buffer sizes in > my.cnf) and reduced the query latency a bit further; most queries are > now executing in less than 10ms. > > -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/
