Re: Admission Control

> While this does have the advantage of being relatively simple to
> implement, I think it would be a bitch to tune...

Precisely.  So, there's a number of issues to solve here:

1) We'd need to add accouting for total memory usage to explain plans 
(worth doing on its own, really, even without admission control).

2) Our memory usage estimates aren't that accurate, which would still 
force us to underallocate (as we do with work_mem) if we were letting 
queries through based on memory usage, or force us to abort queries 
whose actual memory usage was too far off estimated usage.

3) Due to concurrency, by the time the query executes, there will be a 
different set of queries executing on the server than were executing at 
evaluation time.   This will also cause underallocation to continue.

4) Many other processes can use major hunks of memory (autovacuum, for 
example) and would need to be brought under centralized management if 
this was to be a real fix for the underallocation issue.

5) Running out of CPU is, in my experience, more common than running out 
of memory on modern systems.  So it's questionable how useful managing 
overall memory usage at all would be.

Having tinkered with it, I'll tell you that (2) is actually a very hard 
problem, so any solution we implement should delay as long as possible 
in implementing (2).  In the case of Greenplum, what Mark did originally 
IIRC was to check against the global memory pool for each work_mem 
allocation.  This often resulted in 100's of global locking checks per 
query ... like I said, feasible for DW, not for OLTP.

The same is the case with (3).  In some ways, (3) is an even harder 
issue because it allows any kind of admission system to be its own 
enemy; you can imagine "admission storms" where the server tries to 
serve 150 queries which have been waiting for admission at once as soon 
as current load drops.

Given this, I think that actually solving the issue of underallocation, 
or even materially improving on memory underallocation compared to where 
we are now, is a much harder issue than a lot of people realize. 
However, it does point towards two areas for further work:

First, (1) would be valuable on its own as part of diagnostics, logging, 
pg_stat_statements, etc.  And seems like a good "easy" TODO.

We can go back to Kevin's originally proposed simple feature: just 
allowing the DBA to limit the number of concurrently executing queries 
by role and overall.   This would consist of two parameters, 
max_concurrent_statements and max_statement_wait; the second would say 
how long the connection would wait for a query slot before giving up and 
returning an error.

This might help the overall underallocation issue,  and would be far 
simpler than schemes involving RAM, I/O and CPU accounting.  However, 
even this solution has two thorny issues to be solved:

a) the "waiting query storm" issue mentioned above, and

b) pending queries are sitting on idle connections, which could easily 
block higher-priority queries, so managing max_connections per role 
would become much more complex.

Overall, it sounds like a lot of work for improving underallocation 
issues for users who can't make effective use of connection pooling but 
nevertheless have high-performance needs.  Are there enough of these 
users to make it worthwhile?

--                                   -- Josh Berkus                                     PostgreSQL Experts Inc.
                           http://www.pgexperts.com