3

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?

5

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.

  • The front-end application is web-based, and we are seeing periods of 100% CPU, but it sounds like we won't get much benefit from going from 4 to 8 CPUs. – chris Jul 11 '14 at 0:12
  • I think @chris you are taking away the wrong impression from this answer. More CPU is almost always better than less, it's just that core speed is often more beneficial than core count, since no single running query runs on more than one core. If you are hitting 100% and running multiple queries at the same time, then yes, add more, and configure accordingly. – Michael - sqlbot Jul 14 '14 at 2:43
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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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