Re: Database performance
Wayne Walker <[email protected]>
| Newsgroups | gmane.mail.spam.greylist.user |
|---|---|
| Message-ID | <[email protected]> |
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