Priority connection scheduling for thread pool
One issue discovered by thread pool benchmarks is that even though
thread pool puts a limit on the number of concurrently running queries,
the number of open transactions remains high, because connections with
already started transactions are put to the end of the queue. Higher
number of open transactions has a number of implications on the
currently running queries.
This blueprint is to implement priority scheduling for thread pool so
that active transactions have a configurable chance to commit faster
without waiting in the common queue.
Blueprint information
- Status:
- Complete
- Approver:
- Alexey Kopytov
- Priority:
- Medium
- Drafter:
- Alexey Kopytov
- Direction:
- Approved
- Assignee:
- Alexey Kopytov
- Definition:
- Approved
- Series goal:
- Accepted for 5.5
- Implementation:
-
Implemented
- Milestone target:
-
5.5.30-30.2
- Started by
- Alexey Kopytov
- Completed by
- Stewart Smith
Related branches
Related bugs
Sprints
Whiteboard
Introduce the new ' thread_
to thread pool implementation with the following semantics:
This variable controls the high priority queue policy. Each new
connection is assigned this many tickets to enter the high priority
queue. Whenever a query has to be queued to be executed later because no
threads are available, the thread pool puts the connection into the high
priority queue if the following conditions apply:
1. The connection has an open transaction in the server.
2. The number of high priority tickets of this connection is non-zero.
If both the above conditions hold, the connection is put into the high
priority queue and its tickets value is decremented. Otherwise the
connection is put into the common queue with the initial tickets value
specified with this option.
Each time the thread pool looks for a new connection to process, it
first checks the high priority queue, and picks connections from the
common queue only when the high priority one is empty.
The idea is to minimize the number of open transactions in the
server. In many cases it is beneficial to give short-running
transactions a chance to commit faster and thus deallocate server
resources and locks without waiting in the same queue with other
connections that are about to start a new transaction, or those that
have run out of their high priority tickets.
With the default value of 0, all connections are always put into the
common queue, i.e. no priority scheduling is used as in the original
implementation in |MariaDB|. The higher is the value, the more chances
each transaction gets to enter the high priority queue and commit before
it is put in the common queue.