[Greylist-users] Database performance

[Wayne Walker]

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