[Greylist-users] Database performance
Wayne Walker
wwalker at bybent.com
Thu Jan 5 10:24:18 PST 2006
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
> >>>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
> >
> >
--
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
More information about the Greylist-users
mailing list