Re: Database performance

Brian,

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.

Wayne

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:
> >
> >>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
> >
> >
> >Turning off your mail relays for 1.5 hours every day?
> >
> >You're swamped. Get professional help. Fast.
> >
> >Bill
> >_______________________________________________
> >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