Dear MariaDB List Readers,
I hope formatting will survive...i tried to make this as readable
as possible.
Due to our ongoing migration from RHEL7 to RHEL9, we are also
upgrading some databases from MariaDB 5.5.68 (on RHEL7) to MariaDB
10.11.6 (on RHEL9). During this process, we've noticed a
significant performance drop, particularly with UPDATE and DELETE
operations.
To investigate this further and rule out external factors, I
created a single RHEL9 VM and downloaded both the x86_64/systemd
versions of MariaDB 5.5.68 and 10.11.9 from MariaDB Downloads.
(https://mariadb.org/download) I installed both versions on the
same VirtualBox VM, using default configurations, and placed them
on the same partition (/usr/local). I followed the installation
instructions in the INSTALL-BINARY file.
I then ran a benchmark on both versions using a variation of this
PHP benchmark script:
https://github.com/sergix44/php-benchmark-script. Here are the
results:
MariaDB 5.5.68:
Server version: 5.5.68-MariaDB MariaDB
Server
mysql::ping,0.0002
mysql::select_version,0.1299
mysql::select_all,0.6270
mysql::select_cursor,0.8542
mysql::seq_insert,2.0182
mysql::bulk_insert,0.0204
mysql::update,0.0005
mysql::update_with_index,0.0005
mysql::transaction_insert,0.0014
mysql::indexes,0.0000
mysql::delete,0.0005
mysql::select_version::q/s,15403
mysql::select_all::q/s,3190
mysql::seq_insert::q/s,991
mysql::update::q/s,3895386
mysql::update_with_index::q/s,4105436
mysql::transaction_insert::t/s,1460821
mysql::delete::q/s,4436065
Total time: 13.2027 s
Peak memory usage: 23.82 MiB
MariaDB 10.11.9:
Server version: 10.11.9-MariaDB MariaDB
Server
mysql::ping,0.0001
mysql::select_version,0.1268
mysql::select_all,0.6435
mysql::select_cursor,0.8352
mysql::seq_insert,2.2696
mysql::bulk_insert,0.6556
mysql::update,1.4104
mysql::update_with_index,0.3981
mysql::transaction_insert,2.5425
mysql::indexes,0.2353
mysql::delete,2.2246
mysql::select_version::q/s,15772
mysql::select_all::q/s,3108
mysql::seq_insert::q/s,881
mysql::update::q/s,1418
mysql::update_with_index::q/s,8963
mysql::transaction_insert::t/s,787
mysql::delete::q/s,899
Total time: 20.9426 s
Peak memory usage: 23.82 MiB
The key observations:
- **UPDATE performance:**
- 5.5.68: 3,895,386 q/s
- 10.11.9: 1,418 q/s
- **UPDATE with index:**
- 5.5.68: 4,105,436 q/s
- 10.11.9: 8,963 q/s
- **DELETE performance:**
- 5.5.68: 4,436,065 q/s
- 10.11.9: 899 q/s
I also tested MariaDB 10.11.6, and the results were similar to
10.11.9.
Our web application feels noticeably slower, and the benchmark
results seem to confirm this.
Is this drastic performance drop expected with newer MariaDB
versions? While we are aware of tuning options (like
`innodb_buffer_pool_size`, file system mount options,
`innodb_flush_log_at_trx_commit`, etc.), both versions were run
with default settings and no tuning.
How can it be that MariaDB 5.5.68 is performing several orders of
magnitude faster than 10.11.x for these specific operations?
Best regards,
MJ
To end, and to be complete: find below the actual script used to create the stats:
<?php
$initialRowCount = 1000;
$mysqli = null;
setup(function ($args) use (&$mysqli, $initialRowCount) {
if (!extension_loaded('mysqli')) {
print('The mysqli extension is not loaded' . PHP_EOL);
return;
}
if ($args['mysql_host'] === null || $args['mysql_user'] ===
null || $args['mysql_password'] === null) {
print('The --mysql_host=, --mysql_user=, and
--mysql_password= arguments are required' . PHP_EOL);
return;
}
$mysqli = new mysqli($args['mysql_host'],
$args['mysql_user'], $args['mysql_password'], null,
isset($args['mysql_port']) ? $args['mysql_port'] :
3306);
if ($mysqli->connect_error) {
printf("Mysql Connect Error (%s) %s\n",
$mysqli->connect_errno, $mysqli->connect_error);
return;
}
// drop database
$mysqli->query("DROP DATABASE IF EXISTS `bench_test`");
// create database
$mysqli->query("CREATE DATABASE IF NOT EXISTS
`bench_test`");
$mysqli->select_db('bench_test');
$mysqli->query("CREATE TABLE IF NOT EXISTS
`bench_test`.`test` (id INT PRIMARY KEY AUTO_INCREMENT, name
VARCHAR(255))");
for ($i = 0; $i < $initialRowCount; $i++) {
$values[] = "('test$i')";
}
$mysqli->query("INSERT INTO `bench_test`.`test` (name)
VALUES " . implode(',', $values));
});
teardown(function () use (&$mysqli) {
if ($mysqli === null) {
return;
}
$mysqli->query("DROP DATABASE IF EXISTS `bench_test`");
$mysqli->close();
});
return [
'ping' => function () use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$mysqli->ping();
return 1;
},
'select_version' => function ($multiplier = 1, $count =
1000) use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("SELECT VERSION()");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
'select_all' => function ($multiplier = 1, $count = 1000)
use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("SELECT * FROM
`bench_test`.`test`");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
'select_cursor' => function ($multiplier = 1, $count =
1000) use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
for ($i = 0; $i < $count; $i++) {
$result = $mysqli->query("SELECT * FROM
`bench_test`.`test`");
while ($row = $result->fetch_assoc()) {
}
$result->close();
}
return $i;
},
'seq_insert' => function ($multiplier = 1, $count = 1000)
use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("INSERT INTO `bench_test`.`test`
(name) VALUES ('test')");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
'bulk_insert' => function ($multiplier = 1, $count =
100000) use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$values = [];
for ($i = 0; $i < $count; $i++) {
$values[] = "('test$i')";
}
$mysqli->query("INSERT INTO `bench_test`.`test`
(name) VALUES " . implode(',', $values));
return $i;
},
'update' => function ($multiplier = 1, $count = 1000) use
(&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("UPDATE `bench_test`.`test` SET
name = 'test' WHERE id = '$i'");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
'update_with_index' => function ($multiplier = 1, $count
= 1000) use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$mysqli->query("CREATE INDEX idx ON
`bench_test`.`test` (id)");
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("UPDATE `bench_test`.`test` SET
name = 'test' WHERE id = '$i'");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
$mysqli->query("DROP INDEX idx ON
`bench_test`.`test`");
return $i;
},
'transaction_insert' => function ($multiplier = 1, $count
= 1000) use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->begin_transaction();
$mysqli->query("INSERT INTO `bench_test`.`test`
(name) VALUES ('test')");
$mysqli->commit();
}
extraStat('t/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
'indexes' => function ($multiplier = 1, $count = 1000)
use (&$mysqli) {
if ($mysqli === null) {
return INF;
}
$mysqli->query("CREATE INDEX idx_name ON
`bench_test`.`test` (name)");
$mysqli->query("DROP INDEX idx_name ON
`bench_test`.`test`");
return 1;
},
'delete' => function ($multiplier = 1, $count = 1000) use
(&$mysqli) {
if ($mysqli === null) {
return INF;
}
$count = $count * $multiplier;
$time = StopWatch::time();
for ($i = 0; $i < $count; $i++) {
$mysqli->query("DELETE FROM `bench_test`.`test`
WHERE id = '$i'");
}
extraStat('q/s', round($count / (StopWatch::time() -
$time)));
return $i;
},
];