I am about to setup a database server (MySQL) in OpenVZ container and I've been wondering how many CPUs I should assign to it. I decided to benchmark it. I compared two OS/MySQL distributions and tested how they performed with 1, 2, 3 and 4 CPUs.
The first software configuration was:
- CentOS release 6.5 (Final)
- mysql Ver 14.14 Distrib 5.1.71, for redhat-linux-gnu (x86_64) using readline 5.1
The second:
- Debian GNU/Linux 7 \n \l
- mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2
Both were running on the same kernel - 2.6.32-openvz-042stab083.2-amd64 #1 SMP Fri Nov 8 17:59:25 MSK 2013 x86_64 GNU/Linux.
All software was installed from packages and used out-of-the-box without any custom config tweaking.
Hardware: 6GB RAM, 1-4 CPUs 3.5 GHz.
For benchmarking I used sysbench with the following scenario:
sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-password=d prepare
sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-password=d --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
In both cases the table's engine was InnoDB.
The output I was looking at was the number of transactions per second. The results were quite stable - the error was less than 1%.
The results were nice and expected for CentOS/MySQL5.1, but very strange for Debian/MySQL5.5:
As you may see, MySQL5.5 on Debian fails to properly take advantage of multiple CPUs. While the performance with 2 CPUs is higher than with 1, it is clearly lower than on CentOS/MySQL5.1. Moreover, it goes down when we add more CPUs on top of 2 which is really weird.
Can someone please explain what is going on there? Why on Earth MySQL would perform worse as we add CPUs?