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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
