[Greylist-users] Database performance

[Brian Ross]

I had considered adding indexes, but was unsure of the performance 
ramifications - there was a note in the MySQL documentation about 
indexes possibly degrading performance in write-intensive dbs (which the 
greylist certainly is).  Given your positive experience with indexes it 
seems like this may be useful.

As for additional defenses before the greylist - yes, high on my 
priority list.  I'll also pursue Evan's suggestion about changing some 
of the Perl variables and look further into changing to InnoDB.

Thanks to everyone who has responded with suggestions and insight.

-Brian Ross


Wayne Walker wrote:

>Offlist Evan prodded me and I realize I mis spoke greatly.
>
>I did add some indexes, but that improved the performance of the
>relaydelay.pl milter under heavy inbound mail load rather than 
>fixing a problem with the db_maintenance.pl performance.
>
>Although, if one runs db_maintenance.pl daily, the indexing that I did
>to improve relaydelay.pl performance is probably not necessary.
>
>Wayne
>
>I added these indexes to fix relaydelay.pl performance:
>
>alter table relaytofrom add index (origin_type);
>alter table relaytofrom add index (block_expires);
>alter table relaytofrom add index (record_expires);
>
>That dropped my heavy inbound mail load from peaking in the 30s and 40s
>to peaking around 5.
>
>But at the time I had over 4 million rows in relaytofrom.  I now have
>only 400,000.
>
>
>
>
>On Thu, Jan 05, 2006 at 09:27:19AM -0600, Wayne Walker wrote:
>  
>
>>Follow Evan's advice, but also add indexes to the tables.  This greatly
>>improved the maintenance script run time for me.
>>
>>BE WARNED!  Adding the indexes will slam your mysql server for a LONG
>>TIME (I had 4 million rows in each table (maintenance db hadn't been
>>run in a long time) and it took about 15 minutes PER INDEX).  
>>
>>Once I added the indexes, it still took a long time to run, but the
>>system was usable during the maintenance db run (it was NOT usable
>>before (load would hit 30's and 40's).
>>
>>
>>On Wed, Jan 04, 2006 at 11:05:06PM -0800, Brian Ross wrote:
>>    
>>
>>>Hello,
>>>
>>>I was wondering if anyone has any performance tuning tips for the MySQL 
>>>db as implemented in relaydelay.
>>>
>>>We're running the db_maintenance script regularly, expiring 
>>>approximately 1.25 million rows on a nightly basis with the maintenace 
>>>taking approximately 1.5 hours.
>>>
>>>During the maintenance we're turning off our mail relays because the 
>>>maintenance is so disk intensive that it causes relaydelay to slow down 
>>>to the point of hitting the milter time out values (yes - I suppose I 
>>>could look at altering those in the sendmail.cf).
>>>
>>>Any thoughts on optimization would be appreciated.
>>>
>>>
>>>-Brian
>>>
>>>
>>>
>>>
>>>
>>>+-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
>>>| Name        | Type   | Row_format | Rows     | Avg_row_length | 
>>>Data_length | Max_data_length | Index_length | Data_free | 
>>>Auto_increment | Create_time         | Update_time         | 
>>>Check_time          | Create_options | Comment |
>>>+-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
>>>| dns_name    | MyISAM | Dynamic    | 11463906 |             57 |   
>>>654696476 |      4294967295 |    260299776 |         0 |           NULL 
>>>| 2003-07-09 16:00:24 | 2006-01-04 22:44:01 | NULL                
>>>|                |         |
>>>| mail_log    | MyISAM | Dynamic    |        0 |              0 
>>>|           0 |      4294967295 |         1024 |         0 
>>>|              1 | 2003-07-09 16:00:24 | 2003-07-09 16:00:24 | 
>>>NULL                |                |         |
>>>| relayreport | MyISAM | Dynamic    | 21424674 |            118 |  
>>>2535895056 |      4294967295 |    994676736 |         0 |      372929009 
>>>| 2003-12-07 01:05:17 | 2006-01-04 21:07:35 | NULL                
>>>|                |         |
>>>| relaytofrom | MyISAM | Dynamic    |  3248361 |            131 |   
>>>426743244 |      4294967295 |    126875648 |         0 |      372993269 
>>>| 2003-07-09 16:00:24 | 2006-01-04 22:44:15 | 2006-01-04 21:34:26 
>>>|                |         |
>>>+-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
>>>4 rows in set (0.01 sec)
>>>_______________________________________________
>>>Greylist-users mailing list
>>>Greylist-users at lists.puremagic.com
>>>http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users
>>>      
>>>
>>-- 
>>
>>Wayne Walker
>>
>>www.unwiredbuyer.com - when you just can't be by the computer
>>
>>wwalker at bybent.com                    Do you use Linux?!
>>http://www.bybent.com                 Get Counted!  http://counter.li.org/
>>Perl - http://www.perl.org/           Perl User Groups - http://www.pm.org/
>>Jabber:  wwalker at jabber.gnumber.com   AIM:     lwwalkerbybent
>>IRC:     wwalker on freenode.net
>>    
>>
>
>  
>
>>_______________________________________________
>>Greylist-users mailing list
>>Greylist-users at lists.puremagic.com
>>http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users
>>    
>>
>
>
>  
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Greylist-users mailing list
>Greylist-users at lists.puremagic.com
>http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users
>  
>