List:General Discussion« Previous MessageNext Message »
From:Bennett Haselton Date:September 25 2009 7:44am
Subject:many-inserts go faster the second time
View as plain text  
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.

Is this a familiar phenomenon to anyone?  Know why it's 
happening?  And especially, is there any way I can tell MySQL to 
optimize that table before the first script run, so that the first 
run goes fast as well?

In general, does anybody have familiarity with the strategies for 
speeding up the process of inserting a lot of rows at a time, and 
knows which ones really do work and which ones don't?  This page:
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
says that in the case of a single client doing lots of inserts, these 
would apply:
- using insert statements with multiple values lists
- change the "bulk_insert_buffer_size" variable
- writing the data to be inserted into a temporary file, and then 
using the LOAD DATA INFILE syntax

	-Bennett

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