Thursday Nov 12, 2009

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  -


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



Conclusion


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.


For more information about ZFS and OpenSolaris

About

This blog covers cloud computing, big data and virtualization technologies

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today