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.
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/
