Project

General

Profile

Actions
Copy link

Feature #5204

open

Implement bulk inserts

Added by Carl Negro about 7 years ago. Updated almost 7 years ago.

Status:
New
Priority:
Normal
Assignee:
Carl Negro
Category:
-
Target version:
-
Start date:
09/11/2017
Due date:
% Done:

0%

Estimated time:

Description

If these people can get ~200k database inserts/second: https://www.percona.com/blog/2011/01/07/high-rate-insertion-with-mysql-and-innodb/
...we should be able to also.

This is what the mysql command would look like:

https://stackoverflow.com/questions/14330314/bulk-insert-in-mysql

Other helpful links:
http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql
https://www.percona.com/blog/2007/05/24/predicting-how-long-data-load-would-take/

Actions
Copy link
 #1

Updated by Carl Negro almost 7 years ago

  • Description updated (diff)
Actions
Copy link
 #2

Updated by Carl Negro almost 7 years ago

  • Subject changed from Implement bulk insert for makestack to Implement bulk inserts

Here is some testing I did with bulk insert queries for uploadRelion3DRefine that look like

LOAD DATA INFILE '/gpfs/appion/cnegro/17jun23d/extract/relion3drefine15/testbulklist3M.txt'
INTO TABLE nyap_243.`ApParticleData`
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

Inserted Rows / Query Time (sec)
97478 / 2.7635
1072268 / 38.3927
3216806 / 132.6572

Then I converted the database engine for apParticleData to Innodb (this only effects the table for the individual processing db, not all of the processing db's):

Inserted Rows / Query Time (sec)
97478 / 10.0635
1072268 / 127.5264
3216806 / 384.3908

This is slower, but it should get faster than MYISAM when we have optimized some settings. InnoDB also has the ability to execute atomic transactions.

Actions
Copy link

Also available in: Atom PDF