Re: refusing connections based on load ...

The whole argument over how to get load averages seems rather silly,
and it's moot if the idea of using the load information to alter
PG behavior is rejected.

I personally have no use for it, but I don't think it's a bad idea in
general. Particularly given future redundancy/load sharing features.
On the other hand, I think almost all of this stuff can and should be
done outside of postmaster.

Here is the 0-change version, for rejecting connections, and for
operating systems that have built-in firewall capability, such as
FreeBSD: a standalone daemon that adds a reject rule for the Postgres
port when the load gets too high, and drops that rule when the load
goes back down.

Now here's the small-change version: add support to Postgres for a SET
command or similar way to say "stop accepting connections", or "set
accept/transaction delay to X". Write a standalone daemon which
monitors the load and issues commands to Postgres as necessary. That
daemon may need extra privileges, but it is small, auditable, and
doesn't talk to the outside world. It's probably better to include
in the Postgres protocol support for accepting (TCP-wise) a connection,
then closing it with an error message, because this daemon needs to
be able to connect to tell it to let users in again. It's probably as
simple as always letting the superuser in.

The latter is nicer in a number of ways. Persistent connections were
already mentioned - rejecting new connections may not be a good enough
solution there. With a fancier approach, you could even hang up on
some existing connections with an appropriate message, or just NOTICE
them that you're slowing them down or you'd like them to go away
voluntarily.

From a web-hosting standpoint, someday it would be nifty to have
per-user-per-connection limits, so I could put up a couple of big
PG servers and only allow user X one connection, which can't use
more than Y amount of RAM, and passes a scheduling hint to the OS
so it shares CPU time with other economy-class users, which can
be throttled down to 25% of what ultra-mega-hosting users get.
Simple load shedding is a baby step in the right direction. If
nothing else, it will cast a spotlight on some of the problem
areas.
--
Christopher Masto Senior Network Monkey NetMonger Communications
chris(at)netmonger(dot)net info(at)netmonger(dot)net http://www.netmonger.net

Free yourself, free your machine, free the daemon -- http://www.freebsd.org/