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?
Not to burst anyone's bubble of guesses, but this is a bug in MySQL 5.5.
Well its a really leading question without first knowing something about the architecture that you are running.. but generally there is an exponential hit upon bus throughput when adding CPU capacity especially architecture that supports multiple multi core processors, just stop and think for a moment of the implications upon the interrupt cycle across whatever bus bandwidth limitation deployed.... in any event of performance... understanding that your architecture will either bottle neck I/O or Bits Per Cycle, again keep in mind that multiple cpu's need multiple threading.. so no matter the speed of your FSB... if you've hardware is only 64bit then the physical electrical connectivity is going to have to share this limitation across as many cores in the same clock cycle... now stop and think of the positive implications of upgrading from a 64bit to a 256bit system board you may get x3 times better throughput at full tilt given that x1 will be spent upon threading/maintaining the operation overhead.. however during normal operations you may experience an uncomfortable reality about the overhead when you only need a single processor/operator.. I guess its a balancing act or more specifically the right horse for the right course.. Steveo Reedo [email protected]
When you add CPUs the thing will do more operations in parallel. If you have not increased the number of RAM, swapping can occur then, especially if you use a fatter (and newer mostly means fatter) version of the software you are testing.