Subject Re: [Firebird-Architect] MySQL Connection Handling and Scaling
Author Alex Peshkoff
On 3/21/19 6:38 PM, James Starkey jim@...
[Firebird-Architect] wrote:
> What it doesn’t say is that each MySQL thread maintains it own data
> structures so there is almost no cross took between threads.  If a
> storage engine needs to coordinate access, it’s the storage engine’s
> problem, not the MySQL server.
> Incidentally, and I hope this is old news, is that a thread per
> connection is terribly wasteful of resources and scales poorly with
> concurrent connections. A better architecture is to assign a worker
> thread from a pool when a user connection initiates an interaction.
> Better still is the number of work threads active is restricted to the
> about the number of cores with a mechanism to release another worker
> thread when another stalls on a lock or I/O.  A modest number of
> threads (greater than number of cores) is fine but massive number of
> threads just cause contention that needs to be managed.

That's good rule but blindly following it is bad idea. When process with
limited number of threads has more long-running requests than that
number of threads server's response becomes slow. Yes, from server POV
load will be optimal but we should also take into an account losses at
client side.