[Greylist-users] Database performance
wwalker at bybent.com
Thu Jan 5 11:16:13 PST 2006
I did some experimenting. If instead of :
REPLACE INTO relayreport SELECT * FROM relaytofrom;
# which for me on 500,000 rows going into 2.5 M rows, takes 3.5 minutes
We do a little more work:
ONCE - create a table (temp_archive) idenitcal to relayreport, BUT with NO indexes
Then in db_maintenance.pl we :
INSERT INTO temp_archive SELECT * FROM relaytofrom;
# which for me on 500,000 rows take 3.5 SECONDS
REPLACE INTO relayreport SELECT * FROM temp_archive;
DELETE FROM temp_archive;
Then the whole process takes about 2% longer, But the relaytofrom table
is only locked for 2% of said runtime instead of 100%.
I've attached the experimental benchmarking I did.
On Thu, Jan 05, 2006 at 09:43:39AM -0800, Brian Ross wrote:
> The maintenance interval *exploded* with the latest Sober worm.
> Originally this was taking about 20 minutes. :(
> We're still seeing ~13,000 copies of Sober.Z making it past the greylist
> - you can only imagine what is getting blocked.
> But yes, help does seem to be in order and unfortunately our in-house
> DBAs are Oraclists and don't seem interested in tainting their purity by
> touching MySQL. Perhaps I should just move the greylist DB to Oracle.....
> William Blunn wrote:
> >Brian Ross wrote:
> >>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
> >Turning off your mail relays for 1.5 hours every day?
> >You're swamped. Get professional help. Fast.
> >Greylist-users mailing list
> >Greylist-users at lists.puremagic.com
> Greylist-users mailing list
> Greylist-users at lists.puremagic.com
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