MySQL Server Performance on Microsoft Azure, a lesson learned from a Linux Distribution
Microsoft Azure support infrastructure as a service, that is, support for creating your custom virtual machines with the operating system of your choice. So you can chose Linux as your operating system. Linux has a huge list of different distributions to choose from. In this post I want to show the different in performance found when you host a MySQL Server on different Linux distributions.
Supported and no supported Linux distributions
Inside the Azure portal you can create virtual machines from the gallery. Those images include Windows Server images and Linux. For Linux there are Ubuntu, Centos and OpenSuse. Microsoft support is limited to the Azure platform and services and a support case through support agreement is required to engage the Azure support team. Microsoft support will not offer assistance in the design, architecture, development, or deployment of applications or solutions on Azure. If Linux support is needed, customers should reach out to the vendors directly for support. For more info click here.
Also you can download to your Azure Storage images made by the community, by selecting Virtual Machines on left side, and on that panel, selecting “Images” tab and there clicking on ”Browse VM Depot”.
There you can select a wide range of Linux distribution with a lot flavors, that is. You can download an image with all necessary software to run Wordpress, Alfresco, ect.
On the list there are a Debian GNU/Linux 7.0 wheezy that we are going to use on our demo too. That is because when want to show the different between a Microsoft’s supported Linux distribution and a community Linux distribution.
Mysqlslap
Mysqlslap is a command line tool that is included on the mysql-client package that helps database administrator to simulate work load on the server. This tool can simulate multiple concurrent users, multiple queries and can work with string and int types while simulating the work load.
This tool is great for a performance comparison because you can create work load on every machine and using the same tool.
A typical command line for this tool could it be like this.
mysqlslap --user=root -p --auto-generate-sql --concurrency=1 --number-of-queries=100 --number-char-cols=8 --number-int-cols=10
--iterations=10
During this load test I’m going to change the currency value to simulate more users on the test, this will help to measure the performance on the Linux distributions.
All test are going to be executed on machines that doesn’t have swap partition, only have one disk for the system, the Virtual Machine Size is A3 (4 cores, 7GB of memory).
IOPS
All virtual machine have a limit on the number of IOPS (Input/Ouput Operation per Seconds) of 300 IOPS for basic virtual machines and 500 IOPS for Standard virtual machines.
To increase this number you can add more disk to your machine and set them as a RAID0 to enable striping. With this level there is no redundancy and no level of fault tolerance. The more disk you add to the virtual machine the more IPOS per second you can have.
Results
Concurrency |
Debian 7.0 |
Ubuntu 14.10 |
CentOS 13 |
Debian 7.0 |
Ubuntu 14.10 |
CentOS13 |
1 |
1.991 |
1.9740 |
0.0470 |
2.24 |
2.179 |
0.069 |
10 |
0.442 |
0.3670 |
0.0240 |
0.393 |
0.461 |
0.049 |
100 |
0.098 |
0.0930 |
0.0250 |
0.145 |
0.138 |
0.051 |
500 |
9.778 |
9.5290 |
8.0280 |
14.249 |
13.353 |
12.546 |
Conclusion
The best Linux distribution for MySQL performance is CenOS, it has the best time executing all queries, and obviously the standard tier is the best option too.
All machines on this series of test doesn’t have a swap partition, so that mean that there is no noise on the hard drive, affecting performance.
Luis Guerrero.
Technical Evangelist Microsoft Azure