[Bizgres-general] Transactional Statement Queuing

[Simon Riggs]

STATEMENT QUEUING

Some databases are accessed by multiple applications, each of which may
have its own distinct performance requirements and resource usage
profiles. Administrators operating such an environment look for ways to
provide greater levels of control to ensure Service Level Agreements can
be met across the mixed workload.

In particular, Data Warehouse type queries are often performed from end
user query tools, which offer no opportunity to execute additional code
from the user-side. These queries are often resource hungry and methods
are frequently sought to limit the number of simultaneously executing
large queries. An example of a mixed workload scenario may be a
production application that also supports ad-hoc reporting from the same
database. Another mixed workload scenario may be a regular reporting
workload ("OLAP"), plus ad-hoc reporting from power users ("data
mining").

Solution: TRANSACTIONAL STATEMENT QUEUING

Statement queuing is enabled at server-start only and is either on or
off. [If this is off, it should have zero memory overhead and only a
single if-test overhead per transaction.]
e.g. statement_queueing = on

Statement queuing is transparent to the user and the FE/BE protocol
knows nothing of this. All configuration for this is completely within
the control of the administrator. No interface changes are required.

Each ROLE will have 1 queue with 0 or more active slots

CREATE/ALTER ROLE changes required
ACTIVE TRANSACTION LIMIT n
-1 means no limit (the default)
other numbers imply different numbers of active transactions

Statements with an active slot will execute normally. Statements who do
not yet have an active slot will queue in FIFO order. 

Queuing time will be as long as it takes, though a user can pre-register
how long they wish to wait for an active slot using
statement_queue_timeout (which can be set to 0 or more) (and of course
set on a role by role basis). log_min_duration_statement,
statement_timeout and deadlock detector invocation do not apply until
execution truly begins on an active slot, so their meaning would not
need to change as a result of the new functionality.

Various parameters set on a role would allow easy control over allocated
resources, by top down planning.
e.g. 
web_production
	connection limit 20
	active transaction limit 20
	deadlock_timeout=1000
	statement_timeout=0
	statement_queue_time=0 (we use a connection pool)
	work_mem=1024
management_reports
	connection limit 50
	active transaction limit 4
	deadlock_timeout=30000
	statement_timeout=300000
	statement_queue_time=300000
	work_mem=65536
power_users
	connection_limit         10
	active trans 2
	deadlock_timeout=30000
	statement_timeout=0
	statement_queue_time=0
	work_mem=256M

Request for Active Slots will be made at start of a transaction and will
be held until *end of transaction*. Holding an active slot is possible
even if nothing is being executed while "Idle in Transaction". This
behaviour ensures that we do not get resource deadlocks caused by
statements holding resources not being able to execute. [Discuss]

BI-style queries seldom use parameterised queries and multi-statement
transactions, so the "granularity" of the queuing has little effect.
However, we must accommodate the general case so queuing transactions
rather than statements is important to minimise contention for system
resources. Hence we call this Transactional Statement Queuing, rather
than just Statement Queuing.

The limits per role can be reset dynamically to lock out users.

Reducing the max active transactions below the number currently active
will simply hold up incoming statements longer, while the number reduces
below the new limit. Increasing the number will allow incoming
statements to come off of the queue.


IMPLEMENTATION

Queues would be represented by a structure similar to a LWlock, except
that each queue would have a max number of shared holders, which can be
set for each queue dynamically. If the shared lock could not be obtained
on a queue, then queuers would wait on a semaphore and get woken up when
available. (Queues and LWLocks might be refactored together, or could be
a separate implementation - I would favour the former since LWlocks
already have padding to make their data structures larger than they need
to be).

Queues would be created at startup since NQueues <= MaxBackends, since
each backend can be only one role at a time. Whether a queue was active
or not would be done at connection/set role time. Locating the
appropriate queue would be by look up in a hash table.

Waiting on a queue would take place before major resources are allocated
to the executor, so minimal memory, and no locks or LWlocks will be held
by the queuing processes.

Queuing would take place when:
i) transaction id allocated
ii) first executable request received (Protocol 2 message, or Protocol 3
simple exec or prepared Exec). This last might be better since it would
allow all other commands to look/act right time-wise, but execution
would simply be elongated in the queuing case (though the whole purpose
of this feature is that queue-time should be predictable and much less
than the worst-case that could occur as a result of excess execution
time on a thrashing system).

[How to handle backend crash while holding ActiveSlot? Hmmmm... same way
we handle LWlock holder crashes (he says without checking yet).]

Comments? 
- Is this a good idea? For BI only?
- Is it important to work on this?

I expect to collect requirements on -bizgres, then discuss technical
solutions on -hackers.

Best Regards, Simon Riggs