MySQL Thread Pool: Information Schema Tables

The thread pool have three information schema tables. These are TP_THREAD_STATE,
TP_THREAD_GROUP_STATE and TP_THREAD_GROUP_STATS.

The TP_THREAD_STATE table contains one row per thread that is currently
maintained by the thread pool. This row contains only interesting information
if the thread is actively executing a statement. In this case it contains information
how many 10 milliseconds slots the query has consumed, if the thread is blocked by
some event, the event is listed. Both of those information items are current state
and will change for each new query.

The TP_THREAD_GROUP_STATE table contains one row per thread group. It contains
information about number of threads of various types. The first type is consumer
threads, this is a thread not used for the moment, at most 1 such thread will
exist at any point in time. This is the next thread to use if the current threads
used are not enough and a new thread is needed.

The second type of threads are reserved threads, these are also threads not currently
used. They will be used when there is no consumer thread and a new thread needs to be
started.

It contains information about the current number of connections handled in this thread
group. It contains current information about the number of queued low priority
statements (QUEUED_QUERIES) and queued high priority statements (QUEUED_TRANS).

It contains information about configuration, thus state of stall limit, priority
kickup timer, algorithm used. Also information about current number of threads in
the thread group, current number of threads actively executing a statement in the
thread group and current number of stalled statement executions.

Finally it contains some useful information about thread number of a possible
waiter thread (the thread that listens to incoming statements), information about
the oldest query that is still waiting to be executed.

The last table is the TP_THREAD_GROUP_STATS that contains statistics about the
thread group.

There are statistics about number of connections, number of connections closed,
number of queries executed, number of queries stalled, number of queries queued,
number of queries that was kicked up in priority from low priority to high priority.

There is also statistics on threads, how many threads have been started, how many
threads have become consumer threads, become reserve threads, become waiter threads.
How many times the thread that checks for stalled threads decided to start a thread
to handle the possibility of executing a query.

Finally there is statistics about each blocking event coming from the MySQL Server
(meta data locks, row locks, file IO, sleeps and so forth).

One of the most important information here is the number of stalled queries
(STALLED_QUERIES_EXECUTED in TP_THREAD_GROUP_STATS), this counter
gives a good idea if we have many stalled queries, if there are too many such
queries, it is a good indication that one should consider increasing the
thread_pool_stall_limit.

Another very important information is the number of priority kickups
(PRIO_KICKUPS in TP_THREAD_GROUP_STATS). If this counter
grows too quick it is an indication that the thread_pool_prio_kickup_timer
might need to be higher.

It might at times be important to check the number of threads started
(THREADS_STARTED in TP_THREAD_GROUP_STATS) as well.
If the threads are started too often, it's a good indicator that we should
not be so aggressive in stopping threads and thus set thread_pool_max_unused_threads
a bit higher.

The current oldest waiting query might also be a good idea to track to ensure that
we don't get longer waits than what is acceptable. If we get too long waits here,
one can either change some configuration variable, but it might also be an indicator
that the MySQL Server is constantly overloaded and that some action should be done
to remedy this.