The main task of the thread pool is to limit the number of
concurrent statement executions. The thread pool achieves
this by trying to always operate a thread group such that
only one or zero queries are concurrently executed per
thread group.
There is however a livelock issue to consider. A long-running
query in a thread group will in this manner block out all
other queries in this thread group until the query is completed.
To resolve this issue there will be a configurable timer that
decides when a statement execution is declared as stalled. When
a query is declared as stalled, it is allowed to continue
executing until completed. The thread group will handle the
connection as stalled and not count it as an active connection.
Thus new queries can be executed in the thread group again when a
query have been declared as stalled.
Another issue is when a statement execution is blocked for some
reason. Queries can be blocked e.g. by Row Locks, File IO, Table
Locks, Global Read Locks and so forth. If it is likely that the
blockage will continue for at least a millisecond or so, then it
makes sense to start up another statement execution in the thread
group to ensure that we continue to keep the number of concurrent
active connections at the right level.
To enable this the MySQL Server will make callbacks to the thread
pool stating when a block begins and when it ends. The thread
pool will use this to keep track of number of active statement
executions and this is used to decide when to start a new query
and when to allow an incoming query to start.
It is important that the wait is sufficiently long since it is
necessary to immediately continue executing the query when the
blockage ends.
2 comments:
So, what is the time limit for the stall timer? And regarding the locks, is the time limit the same? If you have much locking you will of course end up having many queries executing in parallell from each group, which kind of undermines the creation of the groups in the first place.
The default time limit is 60ms (=6). This is however configurable and I usually put it at 1 second or so (=100).
There is no time limit around locks for the moment, it's based on categorization in the code instead. So we categorize user-level locks such as Row locks, Table Locks and Global Read locks as longer and ignore low level locks such as mutexes.
A new query is only started if the number of active queries is zero, it's still possible to get more queries executing in parallel then what is desired if the blocked queries are restarted very close to each other.
The thread pool will still in those cases not be worse than without a thread pool.
Post a Comment