Re: Database performance

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
> > [email protected]
> > http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users
> 
> -- 
> 
> Wayne Walker
> 
> www.unwiredbuyer.com - when you just can't be by the computer
> 
> [email protected]                    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:  [email protected]   AIM:     lwwalkerbybent
> IRC:     wwalker on freenode.net

> _______________________________________________
> Greylist-users mailing list
> [email protected]
> http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users


-- 

Wayne Walker

www.unwiredbuyer.com - when you just can't be by the computer

[email protected]                    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:  [email protected]   AIM:     lwwalkerbybent
IRC:     wwalker on freenode.net