insert speed issue

[markjoe]

I had a script on a crappy server (800 Mhz) that did about 10,000 insert queries. It ran in about 25 seconds, I put it on a server that is way faster (dual 3Ghz) and it ran in about 145 seconds.

I figured out that if I group the values in 500 record chunks it will insert all 10,000 in about 2 seconds.

Seems the access is the bottle neck not the data. OK, but why did it run fast on other server?

I would think this is some buffer size issue in MySQL, but I don't know where to look.

I will be developing an application that will be more demanding down the road, so I want to get my optimization figured out before then.

 

I did read through some query optimization info, but can not seem to find anything on inserts.

 

[fenway]

Impossible to say, might have been difference in indexing / delayed inserts / buffer sizes / etc.

[markjoe]

Actually the 2-3 seconds turned out to be 300 record chunks, 500 chunks runs in 1 second.

 

The only difference I can find between the DBs is:

new server: innodb_buffer_pool_size = 15728640

old server: innodb_buffer_pool_size =  26214400

 

The table I am working with is InnoDB, the database was transfered by backup and restore, so the structure should be identical.

 

It is only a single column that was not set as primary key, I changed that (key) and it made no difference.

 

It it just better practice to make less bigger queries than more smaller ones?

I suppose it depends on the specific setup, like you said, buffer sizes, delayed insets, etc.

 

Now I need to go find out what delayed inserts are (besides the obvious).