MySQL multiple insert performance

Question

I have data containing about 30 000 records. And I need to insert this data into MySQL table. I group this data in packages by 1000 and create multiple inserts like this:

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

How can I optimize performance of this inserting? Can I insert more than 1000 records per time? Each row contains data with size about 1KB. Thanks.

Answer 1

Try wrapping your bulk insert inside a transaction.

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

That might improve performance, I'm not sure if mySQL can partially commit a bulk insert though (if it can't then this likely won't really help much)

Remember that even at 1.5 seconds, for 30,000 records each at ~1k in size, you're doing 20MB/s commit speed you could actually be drive limited depending on your hardware setup.

Advice then would be to investigate a SSD or changing your Raid setup or get faster mechanical drives (there's plenty of online articles on the pros and cons of using a SQL db mounted on a SSD).

Answer 2

You need to check mysql server configurations and specifically check buffer size etc.

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

Look here, you should get all you need.

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

One insert statement with multiple values, it says, is much faster than multiple insert statements.

Answer 3

Is this a once off operation?

If so, just generate a single sql statement per data element and execute them all on the server. 30,000 really shouldnt take very long and you will have the simplest means of inputting your data.