6

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.

Alex Pliutau
  • 21,392
  • 27
  • 113
  • 143

3 Answers3

11

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).

Seph
  • 8,472
  • 10
  • 63
  • 94
  • Enveloping a bulk `insert` loop within the body of a transaction dramatically reduces the amount of disk I/O and speeds up the operation on my database with InnoDB and cheap PATA disks. I'm interested to know if this 'trick' is reliable and portable. This is on MySQL 5.6 & Linux 2.6.32. – davide May 05 '15 at 16:43
  • 1
    This method is quite reliable and portable as internally all insert operations without transactions create and commit individual transactions which causes significant I/O overhead. This method noticeably improves performance on almost every relational database and the only difference to not using a transaction is that the whole insert will roll back or commit rather than allowing for some of the inserts to commit while others fail. – Seph May 06 '15 at 10:55
  • 4
    This helped me BIG TIME. I was doing chunks of 100 (max api records per GET request) INSERT INTO... ON DUPLICATE KEY UPDATE... It was gonna take 2.30 hours for 123k records using $mysqli->multi_query(), wrapped each chunk with transaction and it took 20 mins for all 123k records. I went to lunch very happy. – angelcool.net Sep 09 '16 at 21:03
  • What about combining transactions (innoDB) and bulk insert? Is that not fastest option? – lubosdz Dec 08 '16 at 20:00
7

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.

Ravi Bhatt
  • 3,147
  • 19
  • 21
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.

Toby Allen
  • 10,997
  • 11
  • 73
  • 124