Here are the results of a little PHP bench I did :

I'm trying to insert 3000 records in 3 different ways, using PHP 8.0, MySQL 8.1 (mysqli)

Multiple insert queries, with multiple transaction :

$start = microtime(true);
for($i = 0; $i < 3000; $i++)
{
    mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
$end = microtime(true);
echo "Took " . ($end - $start) . " s\n";

Did it 5 times, average : 11.132s (+/- 0.6s)

Multiple insert queries, with single transaction :

$start = microtime(true);
mysqli_begin_transaction($res, MYSQLI_TRANS_START_READ_WRITE);
for($i = 0; $i < 3000; $i++)
{
    mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
mysqli_commit($res);
$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";

Result with 5 tests : 0.48s (+/- 0.04s)

Single aggregated insert query

$start = microtime(true);

$values = "";

for($i = 0; $i < 3000; $i++)
{
    $values .= "(null,now(), 'msg : $i','callstack','user','debug_speed','vars')";
    if($i !== 2999)
        $values .= ",";
}
mysqli_query($res, "insert into app__debuglog VALUES $values");

$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";

Result with 5 tests : 0.085s (+/- 0.05s)

So, for a 3000 row insert, looks like :

• Using multiple queries in a single write transaction is ~22 times faster than making a multiple queries with multiple transactions for each insert.
• Using a single aggregated insert statement is still ~6 times faster than using multiple queries with a single write transaction

You might want to :

• Check that auto-commit is off
• Open Connection
• Send multiple batches of inserts in a single transaction (size of about 4000-10000 rows ? you see)
• Close connection

Depending on how well your server scales (its definitively ok with PostgreSQl, Oracle and MSSQL), do the thing above with multiple threads and multiple connections.

I just did a small benchmark and it appears that for a lot of line it's not faster. Here my result to insert 280 000 rows :

• by 10 000 : 164.96 seconds
• by 5 000 : 37seconds
• by 1000 : 12.56 seconds
• by 600 : 12.59 seconds
• by 500 : 13.81 seconds
• by 250 : 17.96 seconds
• by 400 : 14.75 seconds
• by 100 : 27seconds

It appears that 1000 by 1000 is the best choice.

It's ridiculous how bad Mysql and MariaDB are optimized when it comes to inserts. I tested mysql 5.7 and mariadb 10.3, no real difference on those.

I've tested this on a server with NVME disks, 70,000 IOPS, 1.1 GB/sec seq throughput and that's possible full duplex (read and write).
The server is a high performance server as well.
Gave it 20 GB of ram.
The database completely empty.

The speed I receive was 5000 inserts per second when doing multi row inserts (tried it with 1MB up to 10MB chunks of data)

Now the clue:
If I add another thread and insert into the SAME tables I suddenly have 2x5000 /sec. One more thread and I have 15000 total /sec

Consider this: When doing ONE thread inserts it means you can sequentially write to the disk (with exceptions to indexes). When using threads you actually degrade the possible performance because it now needs to do a lot more random accesses. But reality check shows mysql is so badly optimized that threads help a lot.

The real performance possible with such a server is probably millions per second, the CPU is idle the disk is idle.
The reason is quite clearly that mariadb just as mysql has internal delays.

I would add the information that too many rows at a time depending on their contents could lead to Got a packet bigger than 'max_allowed_packet'.

Maybe consider using functions like PHP's array_chunk to do multiple inserts for your big datasets.