Anyone running or considering running MySQL Server on VMware Infrastructure should take some time to read a best practices and performance guide by Mike Frank, Jennifer Glore, and Adina Kalin. The three cover recent testing conducted by Sun/MySQL and VMware where virtualized instances of MySQL Server instances running on VMware ESX were compared to non-virtualized MySQL Server instances running on an equivalent hardware server.

The guide explains the setup, configuration, and results from the testing. "Our goal was to make this testing a fair comparison of MySQL behavior in virtual environments versus non-virtual environments," the authors write.

Frank, Glore, and Kalin conclude that overall performance variance is minimal on a properly configured system, as shown in tests on a typical small to medium business level server with MySQL running in a VMware virtualized environment. "...the difference in transactions per second is just under 4.9% on average between a physical and virtual environment. This difference is likely unnoticeable to end users, especially in a more typical situation with a lower transaction volume," they conclude. "Furthermore, in our tests, the number of threads (connections) didn’t have any significant effect on performance for either HW or VM results and the differences remained in the same range."

Best practices offered include:

STORAGE CONFIGURATION

RAID

• Use a RAID configuration that works well for databases. In this testing 0+1 was used
• Use a hardware RAID and not a software-based RAID (e.g. a volume manager)

HBA Card

• To optimize the throughput of HBA card(s), consider changing their maximum queue depth setting within the ESX configuration. In this testing, the Queue Depth for QLogic HBA was adjusted as follows: /etc/vmware/esx.conf Set ql2xmaxqdepth=128

General

• For optimum performance, separate the OS and database disks.
• Spread virtual disks across physical disks
• Do not dynamically reconfigure virtual disks with the MySQL database running. This applies to regular hardware as well.

GENERAL VMWARE BEST PRACTICES

For Performance Testing

• Ensure that database queries use indices effectively, thus reducing any unnecessary I/O operations. Overall, additional I/O operations may cause an increase in CPU use compared with physical systems.
• For Red Hat-based versions of Linux, be aware of the effects of the frequent timer interrupts, which may need to be managed via the divider command.
• Reduce the number of vCPUs in the virtual machines to the minimum, as larger numbers of vCPUs will incur more scheduling overhead. For example, if running on a four vCPU virtual machine with only 30% CPU utilization, reduce the number of vCPUs to two and expect to run at around 55 to 60% CPU utilization.
• Disable the floppy drive, CD ROM and USB adapters in the virtual machines. This reduces overhead slightly, and can also reduce the chances of IRQ conflicts on the system.
• Use physical systems that have multiple physical NICs available, and separate network traffic across the two NICs to reduce network contention.

More Information

Virtualization for MySQL on VMware: Best Practices and Performance Guide

MySQL

MySQL Virtualization Forums

MySQL 5.1 Reference Manual Section 15.1 Common Issues with Virtualization

VMware [...read more...]

		fullsource
Print Email Current Profile Archive Blog Generate newsletter