Re: Database performance
Wayne Walker <[email protected]>
| Newsgroups | gmane.mail.spam.greylist.user |
|---|---|
| Message-ID | <[email protected]> |
I will do some benchmarks (informal) with a few different solutions on a spare server I have (with a 2.5 million line relayreport I think). On Thu, Jan 05, 2006 at 09:53:58AM -0800, Brian Ross wrote: > 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 > >>>[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 > >> > >> > > > > > > > > > >------------------------------------------------------------------------ > > > >_______________________________________________ > >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