[MariaDB discuss] Performance questions after Migrating from MariaDB 5.5.68 to 10.11.6 (and 9)

7 Oct 2024

      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;
     },
];

[MariaDB discuss] Performance questions after Migrating from MariaDB 5.5.68 to 10.11.6 (and 9)

cyusedfzfb