Re: Admission Control

On 6/25/10 12:15 PM, Robert Haas wrote:
> I think a good admission control system for memory would be huge for
> us. There are innumerable threads on pgsql-performance where we tell
> people to set work_mem to a tiny value (like 4MB or 16MB) because any
> higher value risks driving the machine into swap in the event that
> they get an unusually large number of connections or those connections
> issue queries that require an unusual number of hashes or sorts.

Greenplum did this several years ago with the Bizgres project; it had a
resource control manager which was made available for PostgreSQL core.
However, it would have required a large and unpredictable amount of work
to make it compatible with OLTP workloads.

The problem with centralized resource control is the need for
centralized locking on requests for resources. That forces transactions
to be serialized in order to make sure resources are not
double-allocated. This isn't much of a problem in a DW application, but
in a web app with thousands of queries per second it's deadly.
Performance engineering for PostgreSQL over the last 7 years has been
partly about eliminating centralized locking; we don't want to add new
locking.

That means that a realistic admissions control mechanism would need to
operate based on projections and estimates and "best effort" policies.
Not only is this mathematically more complex, it's an open question
whether it puts us ahead of where we are now vis-a-vis underallocation
of memory. Realistically, a lot of tuning and testing would be required
before such a tool was actually an improvement.

Or, to put it another way: the "poor man's admission control" is a waste
of time because it doesn't actually help performance. We're basically
facing doing the hard version, or not bothering.

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