What is the most efficient way to add a lot of data to a mysql database?

Question

I have a batch of data that needs to be added to a mysql database every day consisting of about 100K queries.

START TRANSACTION;
INSERT IGNORE INTO info (Created, Modified, MType, Pform, Name, Version, Sig) VALUES ('2013-07-31','2013-07-31','mtype','pform','name','version','signature');
(repeat 49,000 times)

edit: SELECT id INTO @r FROM info WHERE sig = 'signature';

INSERT IGNORE INTO fullname (ID, Name) values (@r,'fullname');
(repeat 49,000 times)
UPDATE info SET dbversion = 2098 WHERE dbversion = 0;
UPDATE version SET ver = 2098;
COMMIT;

I feel like this is not the most efficient way to do this. The process doesn't use much CPU or memory but I'd like it to complete more quickly if possible. It takes ~20 minutes right now. Any advice?

score 4 · Accepted Answer · edited May 23 '17 at 12:41

4

Read up on LOAD DATA and see if it fits your needs and constraints. Of which, the only one you've told us is "faster", but maybe you've got other concerns too :-)

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Alternately, it looks like you can do an INSERT INTO VALUES (1a, 1b),..(50000a, 50000b) but there may be a max query size you'll run into.

https://stackoverflow.com/questions/5529529/mysql-bulk-insert

edited May 23 '17 at 12:41

Community

1

answered Jul 31 '13 at 17:07

mfinni

36,144
4
53
86

I have studied LOAD DATA and I think this will help greatly for the first half of my data import. The second half still requires a lookup from data in the first half so I think I will leave that the same. I updated my question because I forgot a line from the query example. – Daniel Jul 31 '13 at 19:14

score 1 · Answer 2 · edited Jun 11 '20 at 10:02

Looking at the query you posted in the question, I'll assume your tables use InnoDB.

While the answer you accepted is great, I wanted add something else.

InnoDB Architecture

Please take note of the Double Write Buffer. Changes are often written to the Double Write Buffer in ibdata1 and the Log Files (ib_logfile0,ib_logfile1) via the Log Buffer.

You can disable the Double Write Buffer but it requires restarting mysql twice.

STEP 01 : SET GLOBAL innodb_fast_shutdown = 0;

STEP 02 : service mysql restart --innodb_doublewrite='OFF' --innodb_fast_shutdown=0

STEP 03 : Execute your code

START TRANSACTION;
INSERT IGNORE INTO info (Created, Modified, MType, Pform, Name, Version, Sig) VALUES ('2013-07-31','2013-07-31','mtype','pform','name','version','signature');
(repeat 49,000 times)

edit: SELECT id INTO @r FROM info WHERE sig = 'signature';

INSERT IGNORE INTO fullname (ID, Name) values (@r,'fullname');
(repeat 49,000 times)
UPDATE info SET dbversion = 2098 WHERE dbversion = 0;
UPDATE version SET ver = 2098;
COMMIT;

STEP 04 : service mysql restart

Your query should have less disk I/O while doing the bulk update.

Give it a Try !!!

CAVEAT : Setting innodb_fast_shutdown to 0 forces all transactional changes in ibdata1, ib_logfile0, and ib_logfile1 to be fully flushed to disk. This makes for a shorter mysql startup.

What is the most efficient way to add a lot of data to a mysql database?

2 Answers2

InnoDB Architecture