Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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: enter image description here

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?

share|improve this question
    
Resource contention could be at play here - do you have many other virtual servers / containers running on this host? Is it possible that the physical processors were under load during your benchmark of the Debian install? –  blacklight Dec 20 '13 at 1:15
    
The results were/are quite reproducible. It was my own home bare metal server and I did not load it with anything else during the test. –  Drake Enterprise Systems Dec 20 '13 at 5:34
    
I mean, there were of course a few other virtual machines on the server, but they were idling. –  Drake Enterprise Systems Dec 20 '13 at 5:43
    
I'd have said kernel, but it's the same... Could you try using the same version on both systems? –  miniBill Jan 12 '14 at 14:07
    
one different, debian uses eglibc en.wikipedia.org/wiki/Embedded_GLIBC, maybe this isn't the problem –  c4f4t0r Jan 12 '14 at 16:19

3 Answers 3

Not to burst anyone's bubble of guesses, but this is a bug in MySQL 5.5.

share|improve this answer

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 nextfriend@live.co.uk

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.