refusing connections based on load ...
Anyone thought of implementing this, similar to how sendmail does it? If
load > n, refuse connections?
Basically, if great to set max clients to 256, but if load hits 50 as a
result, the database is near to useless ... if you set it to 256, and 254
idle connections are going, load won't rise much, so is safe, but if half
of those processes are active, it hurts ...
so, if it was set so that a .conf variable could be set so that max
connection == 256 *or* load > n to refuse connections, you'd hvae best of
both worlds ...
sendmail does it now, and, apparently relatively portable across OSs ...
okay, just looked at the code, and its kinda painful, but its in
src/conf.c, as a 'getla' function ...
If nobody is working on something like this, does anyone but me feel that
it has merit to make use of? I'll play with it if so ...
On Mon, Apr 23, 2001 at 03:09:53PM -0300, The Hermit Hacker wrote:
Anyone thought of implementing this, similar to how sendmail does it? If
load > n, refuse connections?
...
If nobody is working on something like this, does anyone but me feel that
it has merit to make use of? I'll play with it if so ...
I agree that it would be useful. Even more useful would be soft load
shedding, where once some load average level is exceeded the postmaster
delays a bit (proportionately) before accepting a connection.
Nathan Myers
ncm@zembu.com
Nathan Myers wrote:
On Mon, Apr 23, 2001 at 03:09:53PM -0300, The Hermit Hacker wrote:
Anyone thought of implementing this, similar to how sendmail does it? If
load > n, refuse connections?
...
If nobody is working on something like this, does anyone but me feel that
it has merit to make use of? I'll play with it if so ...I agree that it would be useful. Even more useful would be soft load
shedding, where once some load average level is exceeded the postmaster
delays a bit (proportionately) before accepting a connection.
Or have the load check on AtXactStart, and delay new
transactions until load is back below x, where x is
configurable per user/group plus some per database scaling
factor.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
The soft load shedding idea is great.
Along the lines of "lots of idle connections" is the issue with the simple
number of connections. I suspect in most real world apps you'll have
logic+web serving on a set of frontends talking to a single db backend
(until clustering is really nailed).
The issue we hit is that if we all the frontends have 250 maxclients, the
number on the backend goes way up.
This falls in the connection pooling realm, and could be implemented with
the client lib presenting a server view, so apps would simply treat the
pooler as a local server which would allocate connections as needed from a
pool of persistent connections. This also has a benefit in cases (cgi) where
persistent connections cannot be maintained properly. I suspect we've got a
10% duty cycle on the persistent connections we set up... This problem is
predicated on the idea that holding a connection is not negligible (i.e.,
5,000 connections open is worse than 200) for the same loads. Not sure if
that's the case...
AZ
"Nathan Myers" <ncm@zembu.com> wrote in message
news:20010423121105.Y3797@store.zembu.com...
On Mon, Apr 23, 2001 at 03:09:53PM -0300, The Hermit Hacker wrote:
Anyone thought of implementing this, similar to how sendmail does it?
If
load > n, refuse connections?
...
If nobody is working on something like this, does anyone but me feel
that
Show quoted text
it has merit to make use of? I'll play with it if so ...
I agree that it would be useful. Even more useful would be soft load
shedding, where once some load average level is exceeded the postmaster
delays a bit (proportionately) before accepting a connection.Nathan Myers
ncm@zembu.com---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Nathan Myers wrote:
On Mon, Apr 23, 2001 at 03:09:53PM -0300, The Hermit Hacker wrote:
Anyone thought of implementing this, similar to how sendmail does it?
If
load > n, refuse connections?
...
If nobody is working on something like this, does anyone but me feel
that
it has merit to make use of? I'll play with it if so ...
I agree that it would be useful. Even more useful would be soft load
shedding, where once some load average level is exceeded the postmaster
delays a bit (proportionately) before accepting a connection.Or have the load check on AtXactStart, and delay new
transactions until load is back below x, where x is
configurable per user/group plus some per database scaling
factor.
How is this different than limiting the number of backends that can be
running at once? It would seem to me that a user that has a "delayed"
startup is going to think there's something wrong with the server and keep
trying, where as a message like "too many clients - try again later"
explains what's really going on.
len morgan
Import Notes
Resolved by subject fallback
The Hermit Hacker <scrappy@hub.org> writes:
sendmail does it now, and, apparently relatively portable across OSs ...
sendmail expects to be root. It's unlikely (and very undesirable) that
postgres will be installed with adequate privileges to read /dev/kmem,
which is what it'd take to run the sendmail loadaverage code on most
platforms...
regards, tom lane
On Mon, 23 Apr 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
sendmail does it now, and, apparently relatively portable across OSs ...
sendmail expects to be root. It's unlikely (and very undesirable) that
postgres will be installed with adequate privileges to read /dev/kmem,
which is what it'd take to run the sendmail loadaverage code on most
platforms...
Actually, not totally accurate ... sendmail has a 'RunAs' option for those
that don't wish to have it run as root, and still works for the loadavg
stuff, to the best of my knowledge (its an option I haven't played with
yet) ...
* The Hermit Hacker <scrappy@hub.org> [010423 21:38]:
On Mon, 23 Apr 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
sendmail does it now, and, apparently relatively portable across OSs ...
sendmail expects to be root. It's unlikely (and very undesirable) that
postgres will be installed with adequate privileges to read /dev/kmem,
which is what it'd take to run the sendmail loadaverage code on most
platforms...Actually, not totally accurate ... sendmail has a 'RunAs' option for those
that don't wish to have it run as root, and still works for the loadavg
stuff, to the best of my knowledge (its an option I haven't played with
yet) ...
And 8.12.x will have some other options as well....
Like the SUBMISSION prog only needs to be SGID, not SUID....
LER
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
The Hermit Hacker <scrappy@hub.org> writes:
On Mon, 23 Apr 2001, Tom Lane wrote:
sendmail expects to be root.
Actually, not totally accurate ... sendmail has a 'RunAs' option for those
that don't wish to have it run as root,
True, it doesn't *have* to be root, but the loadavg code still requires
privileges beyond those of mere mortals (as does listening on port 25,
last I checked).
On my HPUX box:
$ ls -l /dev/kmem
crw-r----- 1 bin sys 3 0x000001 Jun 10 1996 /dev/kmem
so postgres would have to run setuid bin or setgid sys to read the load
average. Either one is equivalent to giving an attacker the keys to the
kingdom (overwrite a few key /usr/bin/ executables and wait for root to
run one...)
On Linux and BSD it seems to be more common to put /dev/kmem into a
specialized group "kmem", so running postgres as setgid kmem is not so
immediately dangerous. Still, do you think it's a good idea to let an
attacker have open-ended rights to read your kernel memory? It wouldn't
take too much effort to sniff passwords, for example.
Basically, if we do this then we are abandoning the notion that Postgres
runs as an unprivileged user. I think that's a BAD idea, especially in
an environment that's open enough that you might feel the need to
load-throttle your users. By definition you do not trust them, eh?
A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
regards, tom lane
* Larry Rosenman <ler@lerctr.org> [010423 21:45]:
* The Hermit Hacker <scrappy@hub.org> [010423 21:38]:
On Mon, 23 Apr 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
sendmail does it now, and, apparently relatively portable across OSs ...
sendmail expects to be root. It's unlikely (and very undesirable) that
postgres will be installed with adequate privileges to read /dev/kmem,
which is what it'd take to run the sendmail loadaverage code on most
platforms...Actually, not totally accurate ... sendmail has a 'RunAs' option for those
that don't wish to have it run as root, and still works for the loadavg
stuff, to the best of my knowledge (its an option I haven't played with
yet) ...And 8.12.x will have some other options as well....
Like the SUBMISSION prog only needs to be SGID, not SUID....
Actually, the sendmail DAEMON will still have ROOT privs, so it can
read /dev/kmem.
I suspect I don't have as much of an issue if we are sgid kmem...
LER
LER
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
* Tom Lane <tgl@sss.pgh.pa.us> [010423 21:54]:
The Hermit Hacker <scrappy@hub.org> writes:
On my HPUX box:
$ ls -l /dev/kmem
crw-r----- 1 bin sys 3 0x000001 Jun 10 1996 /dev/kmemso postgres would have to run setuid bin or setgid sys to read the load
average. Either one is equivalent to giving an attacker the keys to the
kingdom (overwrite a few key /usr/bin/ executables and wait for root to
run one...)
On my UnixWare box it's 0440 sys.sys....
On Linux and BSD it seems to be more common to put /dev/kmem into a
specialized group "kmem", so running postgres as setgid kmem is not so
immediately dangerous. Still, do you think it's a good idea to let an
attacker have open-ended rights to read your kernel memory? It wouldn't
take too much effort to sniff passwords, for example.Basically, if we do this then we are abandoning the notion that Postgres
runs as an unprivileged user. I think that's a BAD idea, especially in
an environment that's open enough that you might feel the need to
load-throttle your users. By definition you do not trust them, eh?A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
Then there are boxes like my UnixWare one where the load average is
not available AT ALL:
$ uptime
10:05pm up 2 days, 3:16, 3 users
$
It's a threaded kernel, and SCO/Novell/whoever has removed all traces
from userland of the load average. avenrun[] is still a symbol in the
kernel, but...
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Tom Lane wrote:
A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
Rather than do system('uptime') and incur the process start-up each time,
you could do fp = popen('vmstat 60', 'r'), then just read the fp.
I believe vmstat is fairly standard. For those systems
which don't support vmstat, it could be faked with a shell script.
You could write the specific code to handle each arch, but it's
a royal pain, because it's so different for many archs.
Another possibility could be to read from /proc for those systems
that support /proc. But I think this will be more variable than
the output from vmstat. Vmstat also has the added benefit of
providing other information.
I agree with Tom about not wanting to open up /dev/kmem,
due to potential security problems.
Neal
Rather than do system('uptime') and incur the process start-up each time,
you could do fp = popen('vmstat 60', 'r'), then just read the fp.
popen doesn't incur a process start? Get real. But you're right, popen()
is the right call not system(), because you need to read the stdout.
I believe vmstat is fairly standard.
Not more so than uptime --- and the latter's output format is definitely
less variable across platforms. The HPUX man page goes so far as to say
WARNINGS
Users of vmstat must not rely on the exact field widths and spacing of
its output, as these will vary depending on the system, the release of
HP-UX, and the data to be displayed.
and that's just for *one* platform.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
On Linux and BSD it seems to be more common to put /dev/kmem into a
specialized group "kmem", so running postgres as setgid kmem is not so
immediately dangerous. Still, do you think it's a good idea to let an
attacker have open-ended rights to read your kernel memory? It wouldn't
take too much effort to sniff passwords, for example.
On Linux you can get the load average by doing `cat /proc/loadavg'.
On NetBSD you can get the load average via a sysctl. On those systems
and others the uptime program is neither setuid nor setgid.
A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
That is the way to do it on systems where obtaining the load average
requires special privileges. But do you really need the load average
once a minute? The load average printed by uptime is just as accurate
as the load average obtained by examining the kernel.
Ian
---------------------------(end of broadcast)---------------------------
TIP 652: Life is a serious burden, which no thinking, humane person would
wantonly inflict on someone else.
-- Clarence Darrow
other then a potential buffer overrun, what would be the problem with:
open(kmem)
read values
close(kmem)
?
I would think it would be less taxing to the system then doing a system()
call, but still effectively as safe, no?
On Mon, 23 Apr 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
On Mon, 23 Apr 2001, Tom Lane wrote:
sendmail expects to be root.
Actually, not totally accurate ... sendmail has a 'RunAs' option for those
that don't wish to have it run as root,True, it doesn't *have* to be root, but the loadavg code still requires
privileges beyond those of mere mortals (as does listening on port 25,
last I checked).On my HPUX box:
$ ls -l /dev/kmem
crw-r----- 1 bin sys 3 0x000001 Jun 10 1996 /dev/kmemso postgres would have to run setuid bin or setgid sys to read the load
average. Either one is equivalent to giving an attacker the keys to the
kingdom (overwrite a few key /usr/bin/ executables and wait for root to
run one...)On Linux and BSD it seems to be more common to put /dev/kmem into a
specialized group "kmem", so running postgres as setgid kmem is not so
immediately dangerous. Still, do you think it's a good idea to let an
attacker have open-ended rights to read your kernel memory? It wouldn't
take too much effort to sniff passwords, for example.Basically, if we do this then we are abandoning the notion that Postgres
runs as an unprivileged user. I think that's a BAD idea, especially in
an environment that's open enough that you might feel the need to
load-throttle your users. By definition you do not trust them, eh?A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.regards, tom lane
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On 23 Apr 2001, Ian Lance Taylor wrote:
Tom Lane <tgl@sss.pgh.pa.us> writes:
On Linux and BSD it seems to be more common to put /dev/kmem into a
specialized group "kmem", so running postgres as setgid kmem is not so
immediately dangerous. Still, do you think it's a good idea to let an
attacker have open-ended rights to read your kernel memory? It wouldn't
take too much effort to sniff passwords, for example.On Linux you can get the load average by doing `cat /proc/loadavg'.
On NetBSD you can get the load average via a sysctl. On those systems
and others the uptime program is neither setuid nor setgid.
Good call ... FreeBSD has it also, and needs no special privileges ...
just checked, and the sysctl command isn't setuid/setgid anything, so I'm
guessing that using sysctl() to pull these values shouldn't create any
security issues on those systems that support it ?
At 03:09 PM 23-04-2001 -0300, you wrote:
Anyone thought of implementing this, similar to how sendmail does it? If
load > n, refuse connections?Basically, if great to set max clients to 256, but if load hits 50 as a
result, the database is near to useless ... if you set it to 256, and 254
idle connections are going, load won't rise much, so is safe, but if half
of those processes are active, it hurts ...
Sorry, but I still don't understand the reasons why one would want to do
this. Could someone explain?
I'm thinking that if I allow 256 clients, and my hardware/OS bogs down when
60 users are doing lots of queries, I either accept that, or figure that my
hardware/OS actually can't cope with that many clients and reduce the max
clients or upgrade the hardware (or maybe do a little tweaking here and
there).
Why not be more deterministic about refusing connections and stick to
reducing max clients? If not it seems like a case where you're promised
something but when you need it, you can't have it.
Cheerio,
Link.
Tom Lane <tgl@sss.pgh.pa.us> writes:
Rather than do system('uptime') and incur the process start-up each time,
you could do fp = popen('vmstat 60', 'r'), then just read the fp.popen doesn't incur a process start? Get real. But you're right, popen()
is the right call not system(), because you need to read the stdout.
Tom,
I think the point here is that the 'vmstat' process, once started,
will keep printing status output every 60 seconds (if invoked as
above) so you don't have to restart it every minute, just read the
pipe.
I believe vmstat is fairly standard.
Not more so than uptime --- and the latter's output format is definitely
less variable across platforms. The HPUX man page goes so far as to sayWARNINGS
Users of vmstat must not rely on the exact field widths and spacing of
its output, as these will vary depending on the system, the release of
HP-UX, and the data to be displayed.and that's just for *one* platform.
A very valid objection. I'm also dubious as to the utility of the
whole concept. What happens when Sendmail refuses a message based on
load? It is requeued on the sending end to be tried later. What
happens when PG refuses a new client connection based on load? The
application stops working. Is this really better than having slow
response time because the server is thrashing?
I guess my point is that Sendmail is a store-and-forward situation
where the mail system can "catch up" once the load returns to normal.
Whereas, I would think, the majority of PG installations want a
working database, and whether it's refusing connections due to load or
simply bogged down isn't going to make a difference to users that
can't get their data.
-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan
Import Notes
Reply to msg id not found: TomLanesmessageofMon23Apr2001232125-0400
On Mon, Apr 23, 2001 at 10:50:42PM -0400, Tom Lane wrote:
Basically, if we do this then we are abandoning the notion that Postgres
runs as an unprivileged user. I think that's a BAD idea, especially in
an environment that's open enough that you might feel the need to
load-throttle your users. By definition you do not trust them, eh?
No. It's not a case of trust, but of providing an adaptive way
to keep performance reasonable. The users may have no independent
way to cooperate to limit load, but the DB can provide that.
A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
Yes, this alternative looks much better to me. On Linux you have
the much more efficient alternative, /proc/loadavg. (I wouldn't
use system(), though.)
Nathan Myers
ncm@zembu.com
On Tue, Apr 24, 2001 at 12:39:29PM +0800, Lincoln Yeoh wrote:
At 03:09 PM 23-04-2001 -0300, you wrote:
Basically, if great to set max clients to 256, but if load hits 50
as a result, the database is near to useless ... if you set it to 256,
and 254 idle connections are going, load won't rise much, so is safe,
but if half of those processes are active, it hurts ...Sorry, but I still don't understand the reasons why one would want to do
this. Could someone explain?I'm thinking that if I allow 256 clients, and my hardware/OS bogs down
when 60 users are doing lots of queries, I either accept that, or
figure that my hardware/OS actually can't cope with that many clients
and reduce the max clients or upgrade the hardware (or maybe do a
little tweaking here and there).Why not be more deterministic about refusing connections and stick
to reducing max clients? If not it seems like a case where you're
promised something but when you need it, you can't have it.
The point is that "number of connections" is a very poor estimate of
system load. Sometimes a connection is busy, sometimes it's not.
Some connections are busy, some are not. The goal is maximum
throughput or some tradeoff of maximum throughput against latency.
If system throughput varies nonlinearly with load (as it almost
always does) then this happens at some particular load level.
Refusing a connection and letting the client try again later can be
a way to maximize throughput by keeping the system at the optimum
point. (Waiting reduces delay. Yes, this is counterintuitive, but
why do we queue up at ticket windows?)
Delaying response, when under excessive load, to clients who already
have a connection -- even if they just got one -- can have a similar
effect, but with finer granularity and with less complexity in the
clients.
Nathan Myers
ncm@zembu.com
