Can CPU cores be separated for MySQL?

Question

Is it possible to separate CPU cores in Mysql?

I understand it is possible for Memory as we can tune it in innodb_buffer_pool_size or innodb_additional_mem_pool_size. How about CPU cores in Hardware? Or something in OS Level? It is going to be on RHEL 6.5.

EDITED:

I have a standalone Server with

• 2*8 Processor

• 32 GB Ram

I would like to run two Mysql instances one to use only 2 processors and another instance should use remaining all? Is it possible in defining them in server variables of Mysql?

Answer 1

If you mean getting MySQL to engage multiple cores, you have come to the right place.

In order to get MySQL use multiple core for InnoDB, you must use the new settings that came with the InnoDB Plugin starting with MySQL 5.1.38.

Rather than reinventing the wheel, here are my earlier posts on this subject for InnoDB

• 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

Give it a Try !!!

Answer 2

No, MySQL cannot do this alone, but you can bind unix processes to specific cores in general, see this question on unix.stackexchange and the linux manual concerning taskset, and this can also be applied to mysql processes of course.

But actually I would not try this first. Processes under linux are more or less good distributed. Sticking processes to specific cores removes the possibility to use more cores, if those are available.

I would configure the MySQL instances like Rolando stated in his answer according to the balancing wishes, like set the number of treads in a percentaged manner. And only if this fails miserably I would enforce processes onto specific cores. (Actually at first I would try to merge the two instances into one instance...)

Keep in mind that just because you restrict processes to single CPUs this does not mean the processes will finish in the same ratio you distributed them among CPUs. Accessing RAM, harddisk and available RAM are just some examples of resources, which are not controlled by this change, but all have serious influence on the finishing time of computations. If you want to aggressively change scheduling of resources, you may want to take a look at interfering at those levels too...