[Bizgres-general] Transactional Statement Queuing

[Mark Kirkwood]

Simon Riggs wrote:
> 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.
> 

I'm going to be working on this, and my initial thoughts are implement 
it as suggested above, then we can get a bit of field testing performed 
  to evaluate how well it works in this basic form (the of course we can 
get to enhancements/improvements ).

The next few mails from me will begin to cover design/implementation 
ideas along with some questions (as I'm going to be delving into the PG 
locking code, which is all new for me!).

regards

Mark