Multi-Row Insert

We had some really exciting problems to solve for the last customer! Especially because the database wasn’t exactly small.

Here are some key data: CPU: 2 sockets x 24 cores x 2 threads = 96 vCores, 756 G RAM, 2 x 10 Tbyte PCIe SSD in RAID-10 and 7 Tbyte data, several thousand clients, rapidly growing.

The current throughput: 1 M SELECT/min, 56 k INSERT/min, 44 k UPDATE/min, 7 k DELETE/min averaged over 30 days. With a strong upward trend. Application and queries not consistently optimised. Database …

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts

SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)';
Statement prepared
SQL> SET @d1 = 'Bli';
SQL> SET @d2 = 'Bla';
SQL> SET @d3 = 'Blub';
SQL> EXECUTE stmt1 USING @d1, @d2, @d3;
Query OK, 3 …