- Ubuntu 21.10 Impish (Linux 5.13.0-22-generic)
- File system: btrfs
- CPU: Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz (family: 0x6, model: 0x4f, stepping: 0x1)
- under vmware ESXi 7.0 (with VMware PVSCSI rev 2 storage controller)
- CPU cores: 8
- VM RAM: 8 GB
- PHP 8.1.1 (8.1.1-2+ubuntu21.10.1+deb.sury.org+1)
- MariaDB 10.6 (1:10.6.5+maria~impish)
- PHP with mysqli extension on localhost via UNIX socket
- Storage: HPE RAID 6 with 10k RPM HDDs and some SSDs for caching
# nice php mysql_perf.php
Testing engine aria with 10000 rows of bogus data in database fail2ban...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Testing engine aria: 10,000 rows INSERTed in 0:59 ~ (59.70 s)
------------
Testing engine myisam with 10000 rows of bogus data in database fail2ban...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Testing engine myisam: 10,000 rows INSERTed in 0:02 ~ (2.13 s)
------------
Testing engine innodb with 10000 rows of bogus data in database fail2ban...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Testing engine innodb: 10,000 rows INSERTed in 0:55 ~ (55.25 s)
------------
Array
(
[aria] => 59.701476812363
[myisam] => 2.1279759407043
[innodb] => 55.251499891281
)
- Bogus datetime, incremented from the start point (10000 hours ago) by almost an hour on every run
- Bogus md5-ish strings varying from 32 to 254 chars in length, ASCII only
- Bogus DATE, derived from first datetime field
- Yes, the test data is 100% synthetic fakery
- Yes, the table is not wide, 3 columns only
- Yes, a single data set is not very large
- Yes, there are indexes, the DATETIME column as primary key is its own index, and there are 2 other indexes for the other two respective columns.
- Yes, the use of some PHP built-ins for generating the fake data may use some CPU cycles compared to static data.
- And yes, the (in this case) totally superfluoius use of
real_escape_stringadds extra load on the database, as well as the MySQL built-ins regarding date and time conversions. - But I think this is quite reasonable and reflects some aspects of real-world RDBMS use.
- I've run this 3 times, your mileage may vary.
- A little disappointed of ARIA performance.
- Not surprised by InnoDB performance, considering the transactional nature of the engine and intrinsic slowness of its index maintenance
- Absolutely not surprised by MyISAM performance. Got regular backups running?
- Choose your table engines wisely.
- Recommendation? It depends:
- Write-intensive table?
- How important is your data?
- Need transaction safety?
- How are your results?
Forgive me for some hangover hickups, abstraction functions and running things as root on my system :-)
<?php
if (php_sapi_name() <> 'cli') {
printf("This program requires cli.\n");
exit(1);
}
mysql_perf::test();
class mysql_perf {
const db_username = 'root'; # TODO: set to your username
const db_password = 'qwh7t5oa2ms69ebv'; # TODO: set to your password
const db_database = 'demo'; # TODO: set to your password
const eng = ['aria', 'myisam', 'innodb'];
/** @var mysqli */
static $dbh;
static function test() {
$timing = [];
$rowCount = 10000;
$dotPrint = 100;
foreach (self::eng AS $eng) {
$table = "test_$eng";
self::query("DROP TABLE IF EXISTS $table");
self::createTable($eng);
$startTime = strtotime("$rowCount hours ago");
printf("Testing engine %8s with %d rows of bogus data in database %s...\n", $eng, $rowCount, self::db_database);
for ($i = 0; $i < $rowCount; $i++) {
if ($i % $dotPrint == 0) print ".";
}
print "\r";
$µ = microtime(true);
for ($i = 0; $i < $rowCount; $i++) {
$time = $startTime + ($i*3240);
$text = str_repeat(md5($time), 8);
$text = self::sql_escape(substr($text, 0, rand(32, 254)));
self::query("INSERT IGNORE INTO $table SET
`dt` = FROM_UNIXTIME($time),
`tx` = '$text',
`dd` = DATE(FROM_UNIXTIME($time))
");
if ($i % $dotPrint == 0) print "+";
}
print("\n");
$µ = microtime(true) - $µ;
$tm = floor($µ / 60);
$ts = floor($µ % 60);
printf("Testing engine %8s: %s rows INSERTed in %2d:%02d ~ (%1.2F s)\n\n------------\n", $eng, number_format($rowCount), $tm, $ts, $µ);
$timing[$eng] = $µ;
}
print json_encode([
'Datetime' => gmdate('c'),
'Sysinfo' => self::getSystemInfo(),
'SqlEngineTestRowCount' => $rowCount,
'SqlEngineTimingResults' => $timing,
], JSON_PRETTY_PRINT^JSON_INVALID_UTF8_SUBSTITUTE^JSON_UNESCAPED_SLASHES);
printf("\n");
}
static private function __initDB() {
if (!empty(self::$dbh)) {
return;
}
self::$dbh = mysqli_connect(null, self::db_username, self::db_password, self::db_database);
self::$dbh->set_charset('utf8mb4');
self::$dbh->query('SET interactive_timeout=15');
}
static function query($query) {
self::__initDB();
return self::$dbh->query($query);
}
static function sql_escape($string) {
self::__initDB();
return self::$dbh->real_escape_string($string);
}
static function createTable($engine) {
$sql = " CREATE TABLE `test_$engine` (
`dt` DATETIME NOT NULL,
`tx` CHAR(254) CHARACTER SET ascii COLLATE ascii_bin,
`dd` DATE,
KEY `tx` (`tx`) USING BTREE,
KEY `dd` (`dd`) USING BTREE, PRIMARY KEY (`dt`)
) ENGINE=$engine;
";
self::query($sql);
}
static function getSystemInfo() {
$sysInfo = explode("\n", trim(`dmesg | egrep "boot: CPU[[:digit:]]: |MHz [pP]rocessor| (DMI|Memory): |smp: Brought up"`));
$sysInfo = array_map(function($line) { return trim(preg_split('%\]\s+%', $line, 2)[1]); }, $sysInfo);
return [
'Kernel' => trim(`uname -srv`),
'System' => $sysInfo,
'MySQL' => self::$dbh->get_server_info(),
'PHP' => PHP_VERSION,
];
}
}License notice: This is Public Domain — do whatever you please with this.
Updated above script to add sysinfo output.
Output from another Ubuntu 21.04 VM (Virtualbox) on another host system (Windows 10, uptime 261 days cough) with a simple no-redundancy SATA JBOD disk configuration:
MyISAM still is a clear INSERT winner, but the InnoDB and ARIA tests clearly show that the first system on ESXi-basis has some write performance issues itself.