Re: refusing connections based on load ...

Jan Wieck and I talked about this for awhile yesterday, and we came to
the conclusion that load-average-based throttling is a Bad Idea. Quite
aside from the portability and permissions issues that may arise in
getting the numbers, the available numbers are the wrong thing:

(1) On most Unix systems, the finest-grain load average that you can get
is a 1-minute average. This will lose both on the ramp up (by the time
you realize you overdid it, you've let *way* too many xacts through the
starting gate) and on the ramp down (you'll hold off xacts for circa a
minute after the crunch is past).

(2) You can also get shorter-time-frame CPU usage numbers (at least,
most versions of top(1) seem to display such things) but CPU load is
really not very helpful for measuring how badly the system is thrashing.
Postgres tends to beat your disks into the ground long before it pegs
the CPU. Too bad there's no "disk usage" numbers.

However, there is another possibility that would be simple to implement
and perfectly portable: allow the dbadmin to impose a limit on the
number of simultaneous concurrent transactions. (Setting this equal to
the max allowed number of backends would turn off the limit.) That
way, you could have umpteen open connections, but you could limit how
many of them were actually *doing* something at any given instant.
If more than N try to start transactions at the same time, the later
ones have to wait for the earlier ones to finish before they can start.
This'd be trivial to do with a semaphore initialized to N --- P() it
in StartTransaction and V() it in Commit/AbortTransaction.

A conncurrent-xacts limit isn't perfect of course, but I think it'd
be pretty good, and certainly better than anything based on the
available load-average numbers.

regards, tom lane