Database insert speed?

Question

MySQL

The task arose - to insert a set (tens of millions of records) into the database.
Here is such a scheme:

<font color="black"><font color="#0000ff">CREATE</font> <font color="#0000ff">TABLE</font> <font color="#0000ff">IF</font> <font color="#0000ff">NOT</font> <font color="#0000ff">EXISTS</font> `TaskSolution_60` (<br/>
 `id` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font> AUTO_INCREMENT,<br/>
 `period` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalDepricateCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalDevelopeCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalNewCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalRunningCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak0` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak1` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak2` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak3` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak4` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `status` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font> <font color="#0000ff">DEFAULT</font> <font color="#A31515">'0'</font>,<br/>
 <font color="#0000ff">PRIMARY</font> <font color="#0000ff">KEY</font> (`id`)<br/>
) ENGINE=MEMORY <font color="#0000ff">DEFAULT</font> CHARSET=cp1251 AUTO_INCREMENT=1;<br/>
</font><br/>
<font color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font color="gray">Source Code Highlighter</font></a>.</font>

Such a request:

<font color="black"><font color="#0000ff">INSERT</font> <font color="#0000ff">INTO</font> TaskSolution_60 <font color="#0000ff">SET</font> totalCost=10, totalDepricateCost=9, totalDevelopeCost=8, totalNewCost=7, totalRunningCost=6,ak0=5,ak1=4,ak2=3,ak3=2,ak4=1,period=0</font><br/>
<br/>
<font color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font color="gray">Source Code Highlighter</font></a>.</font>

I got a speed of only 16,000 inserts per second , which seems very small.
Experimenting on ENGINE = MEMORY.
Tried using LOCK TABLES WRITE as well as DISABLE KEYS.
statement prepare only slows down the process.
I checked recommendations like mysql.ru/docs/man/SEC463.html and dev.mysql.com/doc/refman/5.0/en/insert-speed.html
on various operating systems and my servers, everywhere the result is about the same.
I ask for advice on possible cunning optimizations.
I would like to increase the insert speed by 10 times, so the option is considered to abandon Mysql and use another DBMS, probably nosql. Recommendations are welcome!

Answer 1

You need to combine rows in one query:

INSERT INTO TaskSolution_60 (totalCost, totalDepricateCost,
totalDevelopeCost, totalNewCost, totalRunningCost, ak0, ak1, ak2, ak3, ak4, period)
VALUES
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0),
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0),
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0) …

Just be careful not to exceed the maximum request size.

Answer 2

If the source data allows, then load data infile can help.

Answer 3

Why did you ask about how you insert data - there will be very big differences from this. A small study was here: forum.php.su/topic.php?forum=28&topic=4026&postid=1318871174#1318871174
The crowd of queries combined into one mysqli_multi_query showed the best insert speed. True, the prepared requests were incorrectly made, theoretically they should be a little faster.
And the mysql_query crowd in the loop is, of course, much slower.

Answer 4

Somewhere I met recommendations that more than 10 million records in InnoDB should not be added to one table. Those. need to be spread across different tables.
We have already said about combining into one request - it becomes much easier.
Can you show the prepared code? I almost doubled the speed on Insert and Update with prepared, so it's very interesting.

Answer 5

Unambiguously combine in one request. In principle, 16000 is nothing without information about the hardware.
I can’t tell you about MySQL, but on PostgreSQL 120,000 per second I put purely numbers without indexes into the database with a record on the HDD (industrial server, SAS RAID10 on 8 disks) using COPY. I really think it will take two hundred.
But in general, correct me, but 120k per second for a couple of minutes is 15 million. With such volumes of data in the table, relational systems do not work with ice.

Answer 6

>Experimenting on ENGINE = MEMORY.
And then you plan to write to disk? For MySQL, I get about 120-200 simple records per second in multi-threaded mode and about 20 records in single-threaded mode.
Check burn to disk, your results are very interesting.

Answer 7

Maybe the answer will not be appropriate, but ... Have you tried working with NoSQL solutions?
For example, with MongoDB, I achieved many times faster write / read speed than with MySQL