Performance Study / Best Practices for running MySQL on Xen Based Hypervisors

ABSTRACT

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.

Tests Objective:

The main objective is to bring an understanding on how MySQL behaves within a virtualized environment, using UFS or ZFS file system

Tests Description:

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.

Hardware configuration:

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

root@para-opensolaris:~# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
     0. c7d3 <DEFAULT cyl 4096 alt 0 hd 128 sec 32>
        /xpvd/xdf@3
     1. c7t0d0 <DEFAULT cyl 3915 alt 0 hd 255 sec 63>
        /xpvd/xdf@51712

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 -