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 …

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book
[1
] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also …