Array / Batch Insert and Prepared Statements

[mariadb-corporation]

Array / Batch Insert and Prepared Statements

[vaintroub]

Do not do many inserts in individual statements. wrap them in a transactions of reasonable size (e.g BEGIN 1000 statements COMMIT).

[markddrake]

I'll give it a try ? Is there any concept of a batch / bulk insert or prepared statement ?

Is 200/s in the ball park for single inserts

[markddrake]

That gets me to about 1250 rows per second..

[rusher]

btw, this is still not released, but as indicated in https://github.com/MariaDB/mariadb-connector-nodejs#road-map, next version will permit fast bulk. (https://jira.mariadb.org/projects/CONJS/issues/CONJS-21)

[vaintroub]

there is a lot of concept of batch bulk inserts, and there is a dedicated page on how to speedup inserts https://mariadb.com/kb/en/library/how-to-quickly-insert-data-into-mariadb/

I recommend multivalue inserts, so far those were the best.

[rusher]

until then The fastest way to insert data for now, if you can is to rewrite query :
example :

connection.query("INSERT INTO myTable(col1, col2) values (?, ?)", [1, 'john']);
connection.query("INSERT INTO myTable(col1, col2) values (?, ?)", [2, 'jack']);

transform to :

connection.query("INSERT INTO myTable(col1, col2) values (?, ?), (?, ?)", [1, 'john', 2, 'jack']);

[vaintroub]

yes, this is multivalue insert :)

[rusher]

cross-post :)

[markddrake]

Since I am generating everything, that would be relatively simple I think. Is there a recommend number of rows to process in a single insert

[rusher]

not really, the real limit is that query size must not be > to server max_allowed_packet value.
(default to 16M)

[markddrake]

Now we're cooking... 15000/s thanks

[markddrake]

Actually with comments removed I am getting ~25000/s on tables with a small number of columns.

C:\Development\YADAMU\MariaDB>node client\import.js --USERNAME=root --HOSTNAME=192.168.1.250 --PORT=3307 --PASSWORD=oracle --DATABASE=mysql --File=..\JSON\Oracle\18c\SH_DATA_ONLY.json touser=SH1
2018-10-10T16:03:02.444Z: Table "COSTS". Rows 0. Elaspsed Time 0ms. Throughput NaN rows/s.

2018-10-10T16:03:38.263Z: Table "SALES". Rows 918843. Elaspsed Time 35775ms. Throughput 25684 rows/s.

2018-10-10T16:03:38.507Z: Table "TIMES". Rows 1826. Elaspsed Time 201ms. Throughput 9085 rows/s.

2018-10-10T16:03:38.549Z: Table "CHANNELS". Rows 5. Elaspsed Time 0ms. Throughput Infinity rows/s.

2018-10-10T16:03:38.572Z: Table "PRODUCTS". Rows 72. Elaspsed Time 0ms. Throughput Infinity rows/s.

2018-10-10T16:03:38.630Z: Table "COUNTRIES". Rows 23. Elaspsed Time 0ms. Throughput Infinity rows/s.

2018-10-10T16:03:43.327Z: Table "CUSTOMERS". Rows 55500. Elaspsed Time 4691ms. Throughput 11831 rows/s.

2018-10-10T16:03:43.426Z: Table "PROMOTIONS". Rows 503. Elaspsed Time 59ms. Throughput 8525 rows/s.

Closing Pool
Import operation completed successfully.

C:\Development\YADAMU\MariaDBC:\Development\YADAMU\MariaDB>mysql -uroot -poracle -h192.168.1.250 -Dmysql -P3307 -v -f
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 712
Server version: 10.3.9-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> use SH1;
Database changed
MariaDB [SH1]> select count(*) from SALES;

select count(*) from SALES

+----------+
| count(*) |
+----------+
| 918843 |
+----------+
1 row in set (0.262 sec)

MariaDB [SH1]>

[markddrake]

If you need a test for the new bulk insert let me know.

[vaintroub]

To explain 200 inserts per second, with individual inserts. I suspect disk is the bottleneck here

With default durability settings (innodb-flush-log-at-trx-commit=1) , for each insert, there is a small amount of log data written and flushed to stable storage in the redo log.
A flush is expensive on hard disk , where with 7200 RPM you can only get 120 sectors written this way.
With less strict durability (innodb-flush-log-at-trx-commit=2) would be orders of magnitude faster, however if OS crashes, some of data can be lost.
Transactions, or multivalue inserts combine a lot of small redo log writes into one large single write on commit, therefore they are faster.

[markddrake]

FWITW the disk is a Samsung 860 EVO M.2 on an Intel NUC with an i7-6770HQ

[vaintroub]

Thanks. Network latency is certainly a factor as well (the multiple send-receive roundtrips in case of individual statements vs batch that is sent once)