List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 25 2009 3:24pm
Subject:Re: many-inserts go faster the second time
View as plain text  
In the last episode (Sep 25), Bennett Haselton said:
> I have a script that runs several times in the evening, and on each run it
> adds several thousand entries to a table.
> 
> On the first run, it adds the entries rather slowly.  But then on all
> subsequent runs (usually about a minute or two later), the many inserts go
> a lot faster.  This is true regardless of how many entries are added by
> each run -- whether the first and second run both add 50,000 or the first
> and second run both add 10,000, the first run goes slowly and the second
> one goes fast.  But by the following evening, the first run is back to
> going slowly again.
> 
> It's as if in the minute or two following the first run of the script,
> MySQL catches its breath and realizes, hey, that table is getting a lot of
> entries added to it, so it waves some magic dust so that the next time I
> add a lot of entries, it goes a lot faster.  (Hope I'm not losing anybody
> with the technical terminology here.) Then by the next evening the
> optimization parameter has exp^W^W^W^W the fairy dust has worn off.

More likely, this is a relatively unused table, and the first batch of
inserts pulls most of the index and some of the table data into RAM, which
makes for much faster lookups on the next run.  What do top and iostat stats
show on both runs?  I'd expect heavy disk usage and little CPU on the first
run, and light disk and heavier CPU usage on the second.
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
many-inserts go faster the second timeBennett Haselton25 Sep
Re: many-inserts go faster the second timeDan Nelson25 Sep
  • Re: many-inserts go faster the second timeBennett Haselton25 Sep
Re: many-inserts go faster the second timeJoerg Bruehe2 Oct
  • Re: many-inserts go faster the second timeBennett Haselton2 Oct
Re: many-inserts go faster the second timeWm Mussatto2 Oct
Re: many-inserts go faster the second timeJoerg Bruehe6 Oct