Re: Why does MySQL's performance decrease when queries are executed in parallel?

[Rick James]

> why the number of queries per second decreases when I use several connections

There are many possible reasons, some not related much to the queries.

If practical, I suggest you try XtraDB (percona.com), which is an InnoDB replacement, and is standard in MariaDB. It addresses some of the following...

"Mutexes" are the villain. To design multi-threaded software, one must deal with cases where one thread might step on another. This can be handled by a mutex that allows only one thread to execute a selected piece of code. There are mutexes for maintenance of each of the caches (buffer_pool, query cache, etc) and many other things.

When a core needs to do something 'protected by' a mutex, the process either idles for a while (wasting cpu cycles, in hopes of getting the mutex quickly) or stops (allowing another process to get the cpu; process swapping is costly). This partially explains performance declines (but I am getting ahead of myself).

When MySQL was created (in the last century), the typical machine had only 1 CPU (and no cores / hyperthreading). So, a single mutex was no less efficient than many detailed mutexes. MySQL is now paying the price for not carefully designing separate mutexes for separate things.

Percona (XtraDB) put a lot of work into splitting up the InnoDB mutexes, thereby allowing more cores to get useful work done in parallel. MyISAM has had virtually no development in the past few years.

I deal with a lot of production machines, but I rarely see one with a workload anything like the highly-parallel things that the benchmark programs (such as the one you are using) generate. That being said, I sometimes see InnoDB slowing down for "no good reason" when a lot of queries are running at the same time. I hope (but don't have enough experience yet to say) that XtraDB will exhibit this less frequently.

If you need "read scaling", use Slaves. If you need "write scaling", shard the data and write the application code necessary to handle the sharding.