Priority connection scheduling for thread pool

Registered by Alexey Kopytov on 2013-04-02

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:
milestone icon 5.5.30-30.2
Started by
Alexey Kopytov on 2013-04-02
Completed by
Stewart Smith on 2013-04-04

Whiteboard

Introduce the new ' thread_pool_high_prio_tickets' configuration option
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.

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.