MySQL multiple insertion performance

Question

I have data containing about 30,000 records. And I need to insert this data into a MySQL table. I group this data in 1000 packages and create several attachments like this:

INSERT INTO `table_name` VALUES (data1), (data2), ..., (data1000); 

How can I optimize the performance of this insert? Can I insert more than 1000 records at a time? Each row contains data about 1 KB in size. Thanks.

Answer 1

You need to check the mysql server configurations and, in particular, check the buffer size, etc.

You can remove indexes from the table, if any, to make it faster. Create onces index data.

Look here, you should get everything you need.

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

A single insert statement with multiple values, he says, is much faster than multiple insert statements.

Answer 2

Try wrapping a bulk insert inside a transaction.

 START TRANSACTION INSERT INTO `table_name` VALUES (data1), (data2), ..., (data1000); COMMIT 

This can improve performance, I'm not sure if mySQL can partially perform bulk insertion though (if it cannot, then this is unlikely to help much)

Remember that even after 1.5 seconds for 30,000 records each ~ 1k in size you make a fix speed of 20 MB / s, you can actually limit the disk depending on the configuration of your equipment.

Then we advise you to research SSDs or change the Raid setting or get faster mechanical disks (there are many online articles about the pros and cons of using SQL db installed on SSDs).

Answer 3

Is this a one-time operation?

If so, just generate one sql statement for each data item and execute them all on the server. 30,000 really should not take a very long time, and you will have the easiest way to enter your data.