Friday, October 21, 2011

MySQL Thread Pool: Limiting number of concurrent transactions

There are hot spots in the MySQL Server that become hotter when many
transactions are handled concurrently. This means that it is imperative
to avoid having too many concurrent transactions executing in parallel.

The thread pool handles this by prioritizing queued queries according
to whether they have already started executing a transaction or not.
It is also possible for the user to decide that a connection will be of
high priority independent of whether a transaction is started or not.

Such a prioritization can have issues with livelock if there are transactions
that are very long. To avoid this problem a query will be moved to the high
priority queue after a configurable time have expired. This time is set in the
configuration parameter --thread_pool_prio_kickup_timer
(number of milliseconds before a query is kicked up).

However to avoid too many movements in a short time, the thread pool will
at most move one query per 10 milliseconds per thread group.

It is possible for the user to define his connection as always being of
high priority to ensure queries from that connection always moves faster
through the server.

No comments: