4

I've read many posts on internet and stack exchange about multi core usage by MySQL.

From 5.1.38 on MySQL has multi core support. But what about multi CPU?

I'm running 5.1.73 on a Dell PowerEdge with 2 Xeons each with 12 cores. The top command shows 24 cores, but only the first 12 (first CPU) are always busy. The last 12 cores (second CPU) always seem idle.

Is (a newer version of) MySQL capable of using more than 1 CPU?

  • 1
    I can think of no reason why it would matter which CPU the cores are on, they should all be used if needed... although NUMA may be involved here. Take a look at blog.jcole.us/2010/09/28/… and see if it seems applicable your system. Also, 5.1 has two different versions of InnoDB, the "built-in" and the "plugin" and you have to enable the "plugin" version to take advantage of the 5.1.38+ enhancements you mention -- it's not enabled by default, and they aren't in the built-in version. Are you? – Michael - sqlbot Nov 13 '16 at 0:00
  • it really series of dependent problems - old mysql (5.1 - 10 years old), old libraries, old OS and etc. The best way if no specific reasons , migrate to something newer. 5.5 or 5.6 mostly support all functions from 5.1, 5.7 - at least it stop support OLD_Password format at all, this could be a problem. – a_vlad Nov 13 '16 at 1:55
2

Cores, processes, CPUs, threads, hyperthrading, connections -- they are mostly interchangeable at this level of discussion.

MySQL has always been able to handle multiple connections and keep things straight. Back in the single-core single-cpu days, two connections would start up two threads (or processes, depending on OS details) and run seemingly simultaneously. With 12 CPUs / 24 cores, the 'only' difference is that there is now less contention for CPU resources.

But... One connection will use only one core. This may explain what you are seeing. 100 connections may use (and share) all 24 cores.

So, if you question is really about "parallel processing" where one connection uses multiple cores, the answer is "no" in all versions of MySQL/MariaDB. (A slight exception: InnoDB has some 'helper' threads that run in the background, possibly making use of more cores.) This still applies in the hot-off-the-press version 8.0.

In my experience, a dedicate MySQL server rarely goes past one core's worth of CPU resources. When it does, there is probably a query that needs a better index, or something else.

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.