Simple MySQL benchmarks 2023

Simple MySQL benchmarks 2023

Here are preliminary results of very simple "benchmarks" (MySQL benchmark called test-ATIS) on different Hypervisors, run again (on different conditions) on 2023. Please see Simple MySQL benchmarks for original and comprehensive tests.

Host Hardware:

• CPU: AMD Athlon(tm) 64 X2 Dual Core Processor 3800+ at 2 GHz
• Tested MB: K9N Platinum, MSI-7250, Rev 1.1
• BIOS Version: MS-7250 V1.11, 4/7/2008 - flashed latest available update (required for Hyper-V 2012 to work)
• 8GB RAM (was 6GB RAM) - 4x 2GB (supported maximum)
• Kingston SA400 480GB SSD disk (was 200GB SATA Maxtor disk) and in case of one of 2 Proxmox VE installations, also HDD Seagate Iron Wolf 4TB + lvm-thin is used.
• AHCI ASMedia1164 SATA 3.0 PCIe controller (was on-board MCP 55 SATA 2 controller)
  • with the exception of ESXi6 - where only MCP 55 SATA 2 is supported (see ESXi 6 AHCI)

Guest Hardware:

• 1xCPU
• 1GB RAM
• 10GB virtual disk
• 1x Bridged Network (bridged on host, using PV network drivers where possible)

Linux Guest Setup

Guest Setup:

• installed Ubuntu 22.04 LTS (from live server) (was fresh CentOS 7.4 from CentOS-7-x86_64-Minimal-1708.iso)

• system was debloated using my Ubuntu-debloat guide.

• installed appropriate guest tools:

  • KVM - apt-get install qemu-guest-agent
  • ESXi apt-get install open-vm-tools
  • not sure in case of Xen (there are agents, but normally used only under XenServer and/or XCP-ng)
  • similarly I did not install Hyper-V tools inside guest - they have only limited usage under Linux guests

• installed MariaDB for benchmarks:

  sudo apt-get install mariadb-server

• tested database package: mariadb-server 10.6.12-0ubuntu0.22.04.1

How to install test-ATIS (our benchmarks)

According to http://www.tocker.ca/plan-to-remove-sql-bench-from-mysql-5-7.html

In the MySQL team, we are planning to remove sql-bench from the MySQL 5.7 sources.

My Note:

poor Oracle is fixing things by removing them - maybe inspired by systemd - which "fixed" incorrect StandardOutput behaviour with syslog(2) by removing Syslog entirely, see

• https://github.com/systemd/systemd/pull/15812
• https://github.com/systemd/systemd/issues/15807

To quickly install test-ATIS from sources, I did:

mkdir -p ~/src
cd ~/src
curl -fLO https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.51.tar.gz
cd mysql-5.6.51/sql-bench/
mkdir -p ~/sql-bench
# have no clue why these files have bogus .sh suffix that is removed on install...
for i in bench-init.pl.sh server-cfg.sh test-ATIS.sh;do  cp $i ~/sql-bench/${i%%.sh};done
chmod +x ~/sql-bench/test-ATIS
find Data/ATIS/ | cpio -pvdm ~/sql-bench/

Now you need to manually install several perl packages + mysql (or MariaDB) server:

# for Ubuntu 22.04 LTS
sudo apt-get install libdbi-perl libdbd-mysql-perl mariadb-server

WARNING for FreeBSD users:

FreeBSD packages are broken - you can install MariaDB OR perl-DBD-Mysql (or perl-DBD-MariaDB), but NOT both! So I gave up on benchmarking FreeBSD. See more info on my FreeBSD page.

Example running test-ATIS under Ubuntu 22.04 LTS:

sudo mysql -e 'create database test' # run only for the 1st time
cd ~/sql-bench/
sudo ./test-ATIS

WARNING! There are some reports on collision with reserved word rank, if your MySQL is too new! According to:

http://monty-says.blogspot.com/2018/04/congratulations-to-oracle-on-mysql-80.html

The problem affects:

• (now) Oracle MySQL 8+

• but MariaDB users are lucky:

  MariaDB also has 'rank' as a keyword in 10.2 and 10.3 but one can still use it as an identifier.

I have verified that on Ubuntu 22.04 LTS and package mariadb-server version 10.6.12-0ubuntu0.22.04.1 the test-ATIS works without problems.

Windows Guest setup

Not yet reproduced - planning to use again Windows Server 2008R2 (last good version without crippled Windows 8 UI).

Windows Server 2008R2 Notes:

• Trial version can be get here (not tested):
  • https://archive.org/download/wserver2008r2/7601.17514.101119-1850_x64fre_server_eval_en-us-GRMSXEVAL_EN_DVD.iso
• you need VMware Tools Version 10 (mounted as additional DVD) to use system disk as PV SCSI
• Path on VMware tools CD: X:\Program Files\VMware\VMware Tools\Drivers\pvscsi\Vista\amd64\
• unfortunately this URL for Version 10 is no longer working:
  • https://packages.vmware.com/tools/esx/latest/windows/VMware-tools-windows-10.3.10-12406962.iso
• try archive (this version was not yet tested):
  • https://web.archive.org/web/20181228141358if_/https://packages.vmware.com/tools/esx/latest/windows/VMware-tools-windows-10.3.5-10430147.iso

Preliminary tests

The goal of these tests is to find fastest configuration possible for development (where lessen data reliability in case of crash is not material problem).

These tests are preliminary and not yet complete.

Hypervisor	Guest Kernel	Total time 3	Notes
bare-metal7	5.15.0-91-generic	22s	defaults I/O bound
bare-metal7	5.15.0-91-generic	13s	...trx_commit=2 4
ESXi 6.0.0 build-15517548 1	5.15.0-91-generic	22s	MCP55 SATA 2.02
ESXi 6.0.0 build-15517548 1	5.15.0-91-generic	18s	...trx_commit=2 4
Xen 4.17 Debian12	N/A PV	32s	MySQL Defaults
Xen 4.17 Debian12	N/A PV-HVM	51s	MySQL Defaults
Proxmox VE 8.1.3, kern 6.5.11-7-pve	5.15.0-52-generic	23s	MySQL defaults
Proxmox VE 8.1.3, kern 6.5.11-7-pve	5.15.0-52-generic	15s	...trx_commit=2 4
Hyper-V 2012R2 5	5.15.0-91-generic	38s	MySQL defaults 6
Hyper-V 2012R2 5	5.15.0-91-generic	18s	...trx_commit=2 4
Hyper-V 2012R2 5	FreeBSD 14.0	24s	MySQL defaults

---

¹) output of vmware -v

²) ESXi 6 does not support my AHCI ASMedia1164 SATA 3 controller, so on-board MCP 55 Ultra is used.

³) As reported (last line from ./test-ATIS output)

⁴) Using this configuration in /etc/mysql/mariadb.conf.d/99-local.cnf:

[mysqld]
innodb_flush_log_at_trx_commit = 2

Verify with sudo mysql -e "show variables like 'innodb_flush%'"

⁵) Windows Server 2012R2 Full (with GUI) with Hyper-V Role. Output from SystemInfo.exe:

OS Name:                   Microsoft Windows Server 2012 R2 Standard
OS Version:                6.3.9600 N/A Build 9600

⁶) Very high IOwait (around 65%)

⁷) using all available resources - 2 cores and 8GB RAM

Best results are (not surprising) on bare-metal (13s), however only if innodb_flush_log_at_trx_commit=2 is used, otherwise I/O will become bottleneck.

Worst results are under Xen PV-HVM (51s), however I need to retest them with new InnoDB settings.

Although these tests are preliminary, we can already say:

• Proxmox VE 8 and ESXi 6 have comparable performance for Linux guests, Proxmox VE/KVM is fastest
• results on Xen are puzzling - it seems to be lagging. Not sure why PV-HVM (HVM with PV drivers for Disk and LAN) is even 2 times slower than PV (maybe missing nested pages support on this old CPU?) - but KVM and/or ESXi is fine with that...
• Hyper-V is same Type-1 hypervisor as Xen, but able to achieve CPU bound results comparable to ESXi.

Once innodb_flush_log_at_trx_commit=2 is used the IO is no longer bottleneck (but using default value 1 causes I/O to be bottlencek) on most platforms:

• under Linux host using iostat -Nsxyz 1 or pidstat 1 - iowait typically under 1%
• under ESXi 6.0 using esxtop and v view or d view - read latency LAT/rd was always 0 write latency LAT/wr was 0, 0.2ms or up to 1ms for short time.

Where I/O is noticeable bottleneck unless innodb_flush_log_at_trx_commit=2 is used:

• on Hyper-V
• on bare-metal (Yes!, because there no cache=unsafe or other shortcut)

In case of Proxmox VE, I concluded tests using discard=on,cache=unsafe on two different storages:

1. HDD IronWolf 4TB, lvm-thin
2. SSD Kingston SA400 480GB, ext4 (relatime), RAW file backend ATIS test numbers were same (within 1s jitter).

Resources

See Simple MySQL benchmarks for complete, yet old tests.