8

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?

5
  • 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, 2013 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.
    – Greendrake
    Dec 20, 2013 at 5:34
  • I mean, there were of course a few other virtual machines on the server, but they were idling.
    – Greendrake
    Dec 20, 2013 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, 2014 at 14:07
  • one different, debian uses eglibc en.wikipedia.org/wiki/Embedded_GLIBC, maybe this isn't the problem
    – c4f4t0r
    Jan 12, 2014 at 16:19

3 Answers 3

2

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

1

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]

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .