MySQL Speed Testing

[HairyMonkeyMan]

We have 2 seperate database servers, development and live. They both run MySQL with the InnoDB engine. The settings are identical.

I have tested how many concurrent inserts each server could handle per second.

Development machine could insert approx 1000 records per second (4 fields in table).

Live machine could only insert approx 100 records per second (4 fields in table).

The Live server is approximately 10 times slower than the development server. Both test were run locally on each server. The structure of both databases is identical. The live server has more data.

The machine specs are as follows:
Development server
Processors: 4 x Intel Xeon 2.8 ghz
Memory: 2gb RAM
Drive: Dell PERC raid array

Live server
Processors: 8 x Intel Xeon 1.6 ghz
Memory: 4gb RAM
Drive: LSI Logic Megaraid and Adaptec SCSI

Anyone have a suggestion as to why the live machine might be slower?

[PeejAvery]

Have you tried benchmarking the actual machine for write speeds? There are plenty of free tools to do that.

[hspc]

The live server has more data
To what extent ? This can make a big difference.

[HairyMonkeyMan]

To what extent ? This can make a big difference.

The live server has 15.6gb of datafiles and the development machine has 10.8gb

@PeejAvery

I'll look into getting some benchmarking software when I get a chance.

Thanks guys

[compavalanche]

The internal index and table structures over time can become inefficient.

If your dev server was created from a mysqldump or similar the database had a chance to optimize the tables and that could be why your seeing better performance.

If you have downtime you could try mysqldump from the production server than loading the database again.