Are there benefits to using more than 4 CPUs for MySQL?

Question

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing the number to 8?

Answer 1

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread.

However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, meaning that certain IO operations like flushing data, precaching and data purging can happen simultaneously, with some "laboratory" benchmarks by MySQL vendors showing that it can scale up to 64 cores under high stress.

Having said that, please understand that in most cases, a database like MySQL is not CPU-bound, but IO-bound, meaning that it is not the CPU power that is causing the bottleneck in latency, it is the read and write latency and throughput of the secondary storage. There are certain cases where you can be CPU-bound, for example, in some special cases while performing certain checksums in a FusionIO device, if you need SSL/other kind of encryption, if you perform complex mathematical operations at SQL level, or applying row filters that can be done exclusively on-memory.

If you have suffered CPU spikes in the past, or have constant high CPU usage, it may help, but only assuming you can parallelize the query computation: it won't made ALTER TABLES or SQL parsing faster for a single query. That, combined with the "lightweight" approach that many of us recommend when working with MySQL (trying to avoid business logic -stored procedures- at MySQL side) makes the laboratory benchmark that I mentioned before difficult to reproduce in reality. Getting to use the extra concurrency sometimes requires a recent version of MySQL and configuration changes. In general, for MySQL, it is way better to invest in more powerful cores than many of them.

Answer 2

InnoDB

InnoDB can access multiple cores, but don't expect it to be that way out of the box. InnoDB must be configured to do so.

• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?
• May 26, 2011 : About single threaded versus multithreaded databases performance

If you do not configure the multicore features, then older versions of MySQL will be better

• Jun 19, 2011 : How do I properly perform a MySQL bake-off?
• Oct 05, 2011 : Query runs a long time in some newer MySQL versions
• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

VM Environments

When it comes MySQL in a VM, InnoDB must be handled with great care and very conservatively. Why ?

When InnoDB is used out-of-the-box (no tuning for threading), it may give less than linear gains. You could even say it levels off some (See Page 8 of this PDF). What can make this worse is the fact that more virtual CPUs require increase scheduling overhead (See Page 18 of this PDF).

Epilogue

Knowing both sides of the issue of MySQL in a VM, you can now moderately tune for more CPus. This is vital since you can go a little crazy on a bare metal server if you have plenty of physical cores along with 192+ GB of RAM.

Therefore, to benefit from having more CPUs, you must tune InnoDB responsibly.

You can only be a little more liberal if

• You increase VMWare physical resources (Deep Pockets and a Happy CFO required)
• You go to a bare metal server