Simple MySQL benchmarks
Here are preliminary results of very simple "benchmarks" on different hypervisors.
Host Hardware:
- CPU: AMD Athlon(tm) 64 X2 Dual Core Processor 3800+
- MB: MS-7250
- 6GB RAM
- 200GB SATA Maxtor disk
Guest Hardware:
- 1xCPU
- 1GB RAM
- 10GB virtual disk
- 1x Bridged Network
Guest Setup:
-
installed fresh CentOS 7.4 from
CentOS-7-x86_64-Minimal-1708.iso -
installed updates:
yum update -y
-
disable selinux (its sole purpose is to break things):
# WARNING! "sed" breaks symbolik links with `-i.bak` option - we must edit link target... # see https://www.centos.org/forums/viewtopic.php?t=45982#p195392 sed -i.bak -e 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
-
disable interfering services:
systemctl stop crond.service systemctl disable crond.service systemctl stop tuned systemctl disable tuned systemctl stop postfix systemctl disable postfix
-
reboot:
init 6
-
verify that selinux is really disabled:
sestatus SELinux status: disabled
-
installed appropriate guest tools:
- oVirt (KVM/QEMU) -
yum install qemu-guest-agent - XenServer
cd /mnt/cdrom/Linux;sh ./install.sh - ESXi
yum install open-vm-tools
- oVirt (KVM/QEMU) -
-
installed MariaDB with benchmarks:
yum install mariadb-bench mariadb-server systemctl enable mariadb systemctl restart mariadb -
tested MySQL/MariaDB version:
MySQL 5.5.56 MariaDB
WARNING!
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 output entirely, see
To quickly install test-ATIS from sources, try:
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 maria) server:
# for Ubuntu 22.04 LTS sudo apt-get install libdbi-perl libdbd-mysql-perl mariadb-serverExample running test-ATIS under Ubuntu 22.04 LTS:
sudo mysql -e 'create database test' cd ~/sql-bench/ sudo ./test-ATISWARNING! There are some reports on colission 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-serverversion10.6.12-0ubuntu0.22.04.1thetest-ATISworks without problems.
Run following test MySQL (MariaDB) test:
cd /usr/share/sql-bench
time ./test-ATISGuest FS was default xfs. Barriers were later turned off using:
mount -o remount,nobarrier /And ./test-ATIS was run again.
Here are results:
| Hypervisor | Guest Kernel | Time to insert (9768) - barrier | Time to insert (9768) - nobarrier/cache=unsafe5 | Total time - barrier | Total time - nobarrier/cache=unsafe5 | Notes |
|---|---|---|---|---|---|---|
Proxmox VE 5.1-3 (pve-qemu-kvm: 2.9.1-5) |
N/A | 84 | 7 | 528 | 446 | (no)barrier has large impact on inserts |
Proxmox VE 5.2-1 (pve-qemu-kvm: 2.11.1-5) |
3.10.0-862.2.3.el7.x86_64 | 87 | 9 | 541 | 466 | (no)barrier has large impact on inserts |
Proxmox VE 5.2-1 (pve-qemu-kvm: 2.11.1-5) |
Win2008R2 SP14 | 368(!) | 14 | 1140 | 782 | Using cache=unsafe fixed insert performance |
| oVirt 4.2 (KVM/QEMU) | N/A | 87 | 9 | 542 | 459 | Pending |
| XenServer 7.4.0 | N/A | 12 | 11 | 482 | 478 | consistent results (total time varies only around 10 seconds in multiple runs) |
| XenServer 7.4.0 | Win2008R2 SP14 | 17 | N/A | 767 | N/A | |
| ESXi 5.5.0 build-79675711 | 3.10.0-862.2.3.el7.x86_64 | 27 | N/A | 1045 | N/A | very poor, 2x slower, barrier makes no diff. |
| ESXi 5.5.0 build-7967571 | 4.16.9-1.el7.elrepo.x86_642,3 | 8 | 8 | 447 | 444 | kernel 4.x makes big difference!!! |
| ESXi 5.5.0 build-7967571 | Win2008R2 SP14 | 13 | N/A | 750 | N/A | please see footnote |
1) output of vmware -v
2) from elrepo - see https://www.tecmint.com/install-upgrade-kernel-version-in-centos-7/ for installation instructions
3) kernel 4.x reports:
XFS (dm-0): nobarrier option is deprecated, ignoring.
So at least for this version there is explanation.
4) I used following for tests under Windows 2008 R2 Server Standard SP1 (version from ver: 6.1.7601):
-
mariadb-5.5.60-winx64.msi(128MB pool - same as in Linux) strawberry-perl-5.26.2.1-64bit.msi- copied
/usr/share/sql-benchfrom Linux to Windows and then run:cd \sql-bench perl test-ATIS
5) Depends on Guest OS:
- Linux -> Use
nobarrieroption inside guest for XFS filesystem - Win2008R2 -> Use
cache=unsafeoption in Proxmox VE host for guest's disk
Xen Notes:
Xen results are very similar with and without barrier - which is strange. However hypervisor reports that it is providing barrier:
dmesg | grep -i barrier ... blkfront: xvda: barrier: enabled; persistent grants: disabled; indirect descriptors: disabled;I have no explanation for this.
Notes for ESXi
- barrier/nobarrier makes no difference (same as under Xen)
- stock kernel 3.x is 2x slower than kernel 4.x from elrepo.
- under stock kernel 3.x there was very high Sys cpu% (typically around 20%), but low IO-wait (which means that disk access was OK, but something very slow in kernel).
- results were very inconsistent under kernel 3.x (and Xen-like deterministic under elrepo's kernel 4.x)
- maybe here is explanation
(both Xen and KVM/QEMU have lot of virtualization hooks for better speed)
# dmesg on kernel 3.x Booting paravirtualized kernel on bare hardware # dmesg on kernel 4.x Booting paravirtualized kernel on VMware hypervisor
- same behavior was observed under
ESXi 5.1U3 build 3872664(see also ESXi 5.1 tips how it was installed).
Under ESXi 5.5 there is empty output from perf top command. Fortunately there is solution
at https://stackoverflow.com/a/35802635 - use -e cpu-clock for example:
perf top -e cpu-clockProduces:
Samples: 41K of event 'cpu-clock', Event count (approx.): 10185093750
Overhead Shared Object Symbol
11.82% [kernel] [k] finish_task_switch
2.50% [vdso] [.] __vdso_clock_gettime
2.22% mysqld [.] MYSQLparse
1.43% libc-2.17.so [.] __strcmp_sse2
1.09% libc-2.17.so [.] __memcpy_sse2
0.93% [kernel] [k] _raw_spin_unlock_irqrestore
0.87% [kernel] [k] system_call_after_swapgs
0.84% libc-2.17.so [.] __memset_x86_64
...
HZ config is following:
grep HZ /boot/config-`uname -r` | grep -v '#'
CONFIG_NO_HZ_COMMON=y
CONFIG_NO_HZ_FULL=y
CONFIG_NO_HZ=y
CONFIG_HZ_1000=y
CONFIG_HZ=1000
CONFIG_MACHZ_WDT=m
Using these commands:
perf record -e cpu-clock -ag
# Ctrl-C after few seconds
perf report
# looking for highest self-timeHere I get:
- 12.54% 12.54% test-ATIS [kernel.kallsyms] [k] finish_task_switch â
- 7.99% 0 â
0x4080200000001 â
0x880cd0 â
0xac07f0 â
mysql_st_internal_execute â
mysql_real_query â
0x26ca6 â
0x257ac â
0x32b7c â
0x31d0a â
0x4db71 â
0xe7fd â
system_call_fastpath â
sys_read â
vfs_read â
do_sync_read â
sock_aio_read â
sock_aio_read.part.10 â
unix_stream_recvmsg â
unix_stream_read_generic â
schedule_timeout â
schedule â
__schedule â
finish_task_switch â
- 0.69% 0x1 â
0x4080200000001 â
0x880cd0 â
0xac07f0
Ummm....
Please see dedicated page Hypervisor Memory overcommit tests].
Copyright © Henryk Paluch. All rights reserved.
