Re: MySQL on an SMP system - exploiting it

On 26.05.2017 15:13, bit-naughty_at_hotmail.com wrote:
> Guys... what I'm asking is, if I add in another chip,
> will it increase the performance of my server -
> it's a simple question really...?

Nope. That's *not* a simple question. The answer depends on your workload.

> When MySQL goes to do a SELECT, can it TELL that the
> core it's trying to do it on is overloaded, and switch
> to a different core? - I guess that's the question I'm trying to ask.....

That just underlines that you have no idea of performance measurement. There is no such thing as an "overloaded core".

MySQL uses a model where each database connection is handled by a worker thread [1]. If there are many connections into the database, there will be many threads. If some connections run a query at the same time, then there will be some active threads. And the operating system will schedule those active threads onto the available CPU cores. If there are more active threads than CPU cores, then the *system throughput* will benefit from adding CPU cores. If there are fewer active threads than CPU cores, then adding cores will have (nearly) no effect.

Forget what Lew said: there is no operating system (any more) that couldn't handle multiple CPU cores. This works on any OS that you will find on a computer from this century.

The important point is: executing a single query will use exactly one thread. And this thread will use at most one CPU core (when it is not waiting for the disk or network). So if your workload runs only one query at a time, then adding a second CPU core will not help you in any way.

[1] yes, for some time now, MySQL knows the "pool-of-threads" scheduling model. This however doesn't change the fact that each query executes on at most one CPU core. It just keeps down the total number of threads and avoids excessive scheduling overhead. Received on Sun May 28 2017 - 22:45:30 CEST