Performance Study / Best Practices for running MySQL on Xen Based Hypervisors
By Orgad Kimchi-Oracle on Nov 12, 2009
This blog entry provides technical insight into the benchmark of the MySQL database on Xen Virtualization environment based on the xVM Hypervisor
Introduction to the xVM Hypervisor
xVM hypervisor can securely execute multiple virtual machines simultaneously, each running its own operating system, on a single physical system. Each virtual machine instance is called a domain. There are two kinds of domains. The control domain is called domain0, or dom0. A guest OS, or unprivileged domain, is called a domainU or domU. Unlike virtualization using zones, each domain runs a full instance of an operating system.
Introduction to the MySQL database
MySQL database is the world's most popular open source database because of its fast performance, high reliability, ease of use, and dramatic cost savings.
The main objective is to bring an understanding on how MySQL behaves within a virtualized environment, using UFS or ZFS file system
We built a test environment by using a Sun X4450 MySQL 5.4 was installed on OpenSolaris 2009_06 because of the OS built-in integration with the xVM Hypervisor . A separate set of performance tests was run with MySQL data placed on a SAN disk. xVM guest OS is OpenSolaris 2009_06 .
When running under xVM the server resources ( cpu, memory) were divided between the dom0 and domU guest OS.
dom0 - 2 vcpu and 2GB RAM
domU - 4 vcpu and 6GB RAM
- We used paravirtualized domU operating system in order to get the best performance.
- We chose to analyze the performance behavior for InnoDB storage engine due to its high popularity.
- We chose to analyze the performance behavior for two file systems (ZFS and UFS) in order to check which file system performs better for MySQL .
SysBench was used as loading tool to test base performance for each configuration.
The tool is simple to use, modular, cross-platform and multi-threaded. It also and can give a good feeling regarding the performance for a simple database use.
Server :SUN X4450 ,with 2X 2.9GHz dual-core CPU,8GB RAM , 2 X 146 GB internal disks.
Storage :StorageTek 6140 - configured RAID 0+1, directly attached to the server.
Software: MySQL 5.4, OpenSolaris 2009_06 .
The SysBench script
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-socket=/tmp/mysql.sock --mysql-user=root prepare sysbench --num-threads=8 --max-time=900 --max-requests=500000 --test=oltp --mysql-user=root --mysql-host=localhost --mysql-port=3306 --mysql-table-engine=innodb --oltp-test-mode=complex --oltp-table-size=80000000 run
We tested it with different number of threads 4 ,8 ,16 ,32 (--num-threads=8 )
The benchmark layout
After the creation of OpenSolaris 2009_06 in domU we attached the SAN storage
Attached the file system to the guest
xm block-attach para-opensolaris phy:/dev/dsk/c0t600A0B8000267DD400000A8D494DB1A6d0p0 3 w
Verified access to the file system from the guest
Searching for disks...done
AVAILABLE DISK SELECTIONS:
0. c7d3 <DEFAULT cyl 4096 alt 0 hd 128 sec 32>
1. c7t0d0 <DEFAULT cyl 3915 alt 0 hd 255 sec 63>
zpool create -f xvmpool c7d3
root@para-opensolaris:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
rpool 29.8G 10.6G 19.2G 35% ONLINE -
xvmpool 117.94G 89.5K 117.94G 0% ONLINE -
The first result of running this benchmark on UFS
The first result of running this benchmark on ZFS
The results after match ZFS recored size to block size and limiting ZFS ARC size
zfs create -o recordsize=16k xvmpool/mysql
set zfs:zfs_arc_max = 0x10000000 in /etc/system
The results after disable ZFS cache flush ( We have battery backed cache)
set zfs:zfs_nocacheflush = 1 in /etc/system
After ZFS tuning we were able to receive the same results as UFS
Thus, we can benefit from ZFS extra features like snapshot and clone.