| $PostgreSQL$ |
| |
| Resource Scheduling |
| =================== |
| |
| Overview |
| -------- |
| |
| The resource scheduler is an attempt to provide improved control of query |
| resource consumption. It is primarily aimed at DSS workloads, but is amenable |
| to more traditional reporting style workloads too. |
| |
| ----------------------------------------------------------------------------- |
| |
| Data Structures |
| --------------- |
| |
| The basic design makes use of data structures from the standard lock |
| implementation, but adapts them to handle the differing semantics required |
| for tracking transient resources. |
| |
| A psuedo E-R diagram for the various resource scheduling objects is: |
| |
| |
| role >---- resource queue ----< limits |
| | |
| | |
| queue ----< limit thresholds, limit values |
| | |
| | |
| lock ----- proclock -----------------+ |
| | | |
| | ^ |
| proc ---< portal ----< portal increment |
| |
| |
| The objects that are additional to the standard lock system are are described |
| below: |
| |
| Limits - |
| These describe the resource itself. It contains a threshold, counter for |
| current utilization and a type. |
| |
| Queues - |
| A queue consists of a set of limits. It also has some attributes of its |
| own - a flag for whether queries whose cost is > the cost limit will be |
| allowed to run (under special circumstances) and a special limit that |
| allows queries with a small cost to avoid being locked at all. |
| |
| |
| Resource Lock - |
| The lock related information for a queue, each queue has 1 and only 1 |
| lock. |
| |
| Portal Increment - |
| The resource related information for a statement. This consists of the |
| process id, portal id and the set of additional changes to the limit |
| counters that will be applied should this portal execute. A portal that |
| is managed by the scheduler [1] *always* has increments - portals for |
| queries that are not going to be locked have portalId set to InvalidOid |
| (either because the owning role is not in a queue or the query has cost |
| smaller than the queue's "ignore" limit - see Queues above). |
| |
| Proclock - |
| Some changes have been made to the proclock structure... this should be |
| backed out and a new proclock like structure (similar to locallock) should |
| be used to track the additional elements (TODO). |
| |
| Portal - |
| Additional fields have been added to the portaldata structures: an id |
| for indexing, the original nodetag of the query, the queue oid that |
| is locking the portal and a flag for whether to release the resource |
| lock at portal destruction time. |
| |
| ----------------------------------------------------------------------------- |
| |
| Functions |
| --------- |
| |
| The basic API consists of: |
| |
| ResLockPortal(Portal portal, PlannedStmt *stmt); |
| ResUnLockPortal(Portal portal); |
| |
| which are inserted into PortalStart and PortalDrop respectively. In PortalStart |
| the ResLockPortal call is made after planning is completed, so there is access |
| to all elements of the plan structure, thus controlling resources on the basis |
| of cost is possible. |
| |
| As the resource lock is taken after parse/analyze most standard locks have |
| been already acquired before the resource lock is (tuple level locks are the |
| exception). |
| |
| The internal routines are named similarly to the standard lock equivalents: |
| |
| ResLockAcquire(LOCKTAG *locktag, ResPortalIncrement *incrementSet); |
| ResLockRelease(LOCKTAG *locktag, unit32 resPortalId); |
| |
| They require slightly different arguments, this is mainly to supply the various |
| increments to the replacement for the Conflict routines: |
| |
| ResLockCheckLimit(LOCK *lock, RESPROCLOCK *proclock, |
| ResPortalIncrement *incrementSet, |
| bool increment); |
| |
| This function encapsulates the fact that resource lock conflict is not |
| deterministic, but depends on whether current limit counter values are at their |
| thresholds. If there is not a conflict, a statement's increments are added (or |
| subtracted) to the shared counters by: |
| |
| ResLockUpdateLimit(LOCK *lock, RESPROCLOCK *proclock, |
| ResPortalIncrement *incrementSet, |
| bool increment); |
| |
| Commit and Abort handling is mostly handled from the existing ResourceOwner |
| code - as dropping portals unlocks them! However for WITH HOLD cursors an |
| extra function to unlock any remaining portals at sesion exit is required: |
| |
| AtExitCleanup_ResPortals(void); |
| |
| Some of the standard lock manager code is almost duplicated to handle interrupts |
| ,sleeping and wait queue removal: |
| |
| ResLockWaitCancel(void); |
| ResProcSleep(LOCKMODE lockmode, LOCALLOCK *locallock): |
| ResRemoveFromWaitQueue(PGPROC *proc, uint32 hashcode); |
| |
| Deadlocks between standard and resource locks are possible, and are handled |
| by the deadlock detector. We pass the lock mode as ExclusiveLock - which |
| results in overly agressive detection and rollback of deadocks. However, safety |
| is the *initial* goal! More sophisticated detection logic is an obvious next |
| step. |
| |
| Also deadlocks with oneself are possible, usually only achiveable with several |
| open cursors: |
| ResCheckSelfDeadLock(LOCK *lock); |
| ----------------------------------------------------------------------------- |
| |
| Lock Design |
| ----------- |
| |
| The resource locks share the lock hash table(s) with the standard (and user) |
| locks. This means that they use the standard lock manager partitioned |
| lightweight lock(s): |
| |
| FirstLockMgrLock,..., FirstLockMgrLock + NUM_LOCK_PARTITIONS |
| |
| for lightweight lock operations. |
| |
| The queues themselves and their data are protected by a seperate lightweight |
| lock: |
| |
| ResQueueLock |
| |
| so at some points the partition lock and the queue lock could be held. |
| |
| |
| ----------------------------------------------------------------------------- |
| |
| Admin |
| ----- |
| |
| Some new GUCs are introduced to control the scheduler: |
| |
| #resource_scheduler = off # enable resource scheduling |
| |
| |
| Some new commands are added and others amended to allow administration of the |
| resource queues: |
| |
| Queues can be created, altered and dropped via the CREATE|ALTER|DROP QUEUE |
| commands. |
| |
| CREATE RESOURCE QUEUE <name> [ACTIVE THRESHOLD <int value>] |
| [COST THRESHOLD <float value>] |
| [IGNORE THRESHOLD <float value>] |
| [OVERCOMMIT|NOOVERCOMMIT]; |
| ALTER RESOURCE QUEUE <name> [ACTIVE THRESHOLD <int value>] |
| [COST THRESHOLD <float value>] |
| [IGNORE THRESHOLD <float value>] |
| [OVERCOMMIT|NOOVERCOMMIT]; |
| DROP RESOURCE QUEUE <name>; |
| |
| The value used for the cost threshold *must* be a float - i.e. 2.0 or 2e+0 |
| not 2. This slightly annoying syntax is needed to allow values of 1e+19 or |
| larger to be accepted. At this point we don't really have a good idea for what |
| a maximum reasonable threshold for cost is likely to be, so we allow very large |
| values. |
| The ignore threshold sets a lower limit for queries to be locked for execution - |
| query cost < this limit means locking will be skipped. |
| The overcommit parameter controls what happens for a query whose cost > cost |
| limit for the queue - it either aborts (the default) or waits until noone else |
| is executing. This is to avoid the situation of querys being in the wait state |
| forever. |
| These last two parameters complicate the logic considerably, needless to say! |
| |
| The ALTER RESOURCE QUEUE command will only allow the thresholds to be amended |
| to a value larger than the current counter for the in-memory queues. This is a |
| safety feature to prevent unexpected behaviour. |
| |
| The DROP RESOURCE command does not allow queues to be dropped if there are roles |
| assigned to it, It does not allow it to be dropped unless all of the current |
| values for the in-memory queue are zero. |
| |
| Only superuser can create, alter or drop resource queues. |
| |
| |
| Roles are assigned to and removed from the resource queues by CREATE|ALTER ROLE |
| commands: |
| |
| CREATE ROLE <name> [role options] [RESOURCE QUEUE <name>]; |
| ALTER ROLE <name> [role options] [RESOURCE QUEUE <name>]; |
| ALTER ROLE <name> [role options] [RESOURCE QUEUE none]; |
| |
| The resource queue name 'none' is used to mean 'no resource queue thanks'. |
| |
| ----------------------------------------------------------------------------- |
| |
| Catalog Changes |
| --------------- |
| |
| Resource Scheduling add one new catalog (pg_resqueue) and alters another |
| (pg_authid). This alteration is propagated to the derived view (pg_roles). |
| |
| The new catalog and indexes have not been added to the catcache system. |
| |
| |
| Tables: |
| |
| pg_resqueue (new catalog with OID 6026) |
| rsqname name not null |
| rsqcountlimit real not null |
| rsqcostlimit real not null |
| rsqovercommit real not null |
| rsqignorecostlimit real not null |
| |
| |
| pg_authid (existing catalog gets 1 new column) |
| rolresqueue oid |
| |
| Indexes: |
| |
| pg_resqueue_oid_index (new unique index with OID 6027) |
| pg_resqueue.oid |
| |
| pg_resqueue_rsqname_index (new unique index with OID 6028) |
| pg_resqueue.rsqname |
| |
| pg_authid_rolresqueue_index (new non-unique index with OID 6029) |
| pg_authid.rolresqueue |
| |
| |
| Views: |
| |
| pg_roles (existing view gets 1 new column, added before pg_authid.oid) |
| pg_authid.rolresqueue |
| pg_resqueue_status (new) |
| |
| ----------------------------------------------------------------------------- |
| |
| Debugging Code |
| -------------- |
| |
| There are a number of (hopefully) helpful elog messages at the DEBUG1 level - |
| however the symbol RESLOCK_DEBUG must be defined before they are enabled |
| (usually done in src/include/pg_config_manual.h) |
| |
| ----------------------------------------------------------------------------- |
| |
| Notes |
| ----- |
| |
| [1] "Managed by the Resource Scheduler" - this deserves some explanation, |
| In the current incarnation of the code it means: |
| |
| portal->queueId != InvalidOid |
| portal->releaseResLock == true |
| |
| |
| Typically every portal gets queueId set at creation, but this may get |
| reset when the to-lock-or-not-to-lock decision comes around (see |
| ResLockPortal). |
