Обсуждение: Re: Anyone interested in improving postgresql scaling?
Kris Kennaway wrote: > If so, then your task is the following: > > Make SYSV semaphores less dumb about process wakeups. Currently > whenever the semaphore state changes, all processes sleeping on the > semaphore are woken, even if we only have released enough resources > for one waiting process to claim. i.e. there is a thundering herd > wakeup situation which destroys performance at high loads. Fixing > this will involve replacing the wakeup() calls with appropriate > amounts of wakeup_one(). I'm forwarding this to the pgsql-hackers list so that folks more qualified than I can comment, but as I understand the way postgres implements locking each process has it *own* semaphore it waits on - and who is waiting for what is controlled by an in (shared) memory hash of lock structs (access to these is controlled via platform Dependant spinlock code). So a given semaphore state change should only involve one process wakeup. Cheers Mark
Mark Kirkwood <markir@paradise.net.nz> writes:
> Kris Kennaway wrote:
>> If so, then your task is the following:
>>
>> Make SYSV semaphores less dumb about process wakeups. Currently
>> whenever the semaphore state changes, all processes sleeping on the
>> semaphore are woken, even if we only have released enough resources
>> for one waiting process to claim. i.e. there is a thundering herd
>> wakeup situation which destroys performance at high loads. Fixing
>> this will involve replacing the wakeup() calls with appropriate
>> amounts of wakeup_one().
> I'm forwarding this to the pgsql-hackers list so that folks more
> qualified than I can comment, but as I understand the way postgres
> implements locking each process has it *own* semaphore it waits on -
> and who is waiting for what is controlled by an in (shared) memory hash
> of lock structs (access to these is controlled via platform Dependant
> spinlock code). So a given semaphore state change should only involve
> one process wakeup.
Correct. The behavior Kris describes is surely bad, but it's not
relevant to Postgres' usage of SysV semaphores.
regards, tom lane
Kris Kennaway <kris@obsecurity.org> writes:
>>> Make SYSV semaphores less dumb about process wakeups. Currently
>>> whenever the semaphore state changes, all processes sleeping on the
>>> semaphore are woken, even if we only have released enough resources
>>> for one waiting process to claim.
>> Correct. The behavior Kris describes is surely bad, but it's not
>> relevant to Postgres' usage of SysV semaphores.
> Sorry, but the behaviour is real.
Oh, I'm sure the BSD kernel acts as you describe. But Mark's point is
that Postgres never has more than one process waiting on any particular
SysV semaphore, and so the problem doesn't really affect us.
Or do you mean that the kernel wakes all processes sleeping on *any*
SysV semaphore? That would be nasty :-(
regards, tom lane
On Tue, Apr 10, 2007 at 10:41:04PM +1200, Mark Kirkwood wrote: > Kris Kennaway wrote: > >If so, then your task is the following: > > > >Make SYSV semaphores less dumb about process wakeups. Currently > >whenever the semaphore state changes, all processes sleeping on the > >semaphore are woken, even if we only have released enough resources > >for one waiting process to claim. i.e. there is a thundering herd > >wakeup situation which destroys performance at high loads. Fixing > >this will involve replacing the wakeup() calls with appropriate > >amounts of wakeup_one(). > > I'm forwarding this to the pgsql-hackers list so that folks more > qualified than I can comment, but as I understand the way postgres > implements locking each process has it *own* semaphore it waits on - > and who is waiting for what is controlled by an in (shared) memory hash > of lock structs (access to these is controlled via platform Dependant > spinlock code). So a given semaphore state change should only involve > one process wakeup. I have not studied the exact code path, but there are indeed multiple wakeups happening from the semaphore code (as many as the number of active postgresql processes). It is easy to instrument sleepq_broadcast() and log them when they happen. Anyway mux@ fixed this some time ago, which indeed helped scaling for traffic over a local domain socket (particularly at higher loads), but I saw some anomalous results when using loopback TCP traffic. I think this is unrelated (in this situation TCP is highly contended, and it is often the case that fixing one bottleneck can make a highly contended situation perform worse, because you were effectively serializing a bit before, and reducing the non-linear behaviour) but am still investigating, so the patch has not yet been committed. Kris
On Tue, Apr 10, 2007 at 10:23:42AM -0400, Tom Lane wrote: > Mark Kirkwood <markir@paradise.net.nz> writes: > > Kris Kennaway wrote: > >> If so, then your task is the following: > >> > >> Make SYSV semaphores less dumb about process wakeups. Currently > >> whenever the semaphore state changes, all processes sleeping on the > >> semaphore are woken, even if we only have released enough resources > >> for one waiting process to claim. i.e. there is a thundering herd > >> wakeup situation which destroys performance at high loads. Fixing > >> this will involve replacing the wakeup() calls with appropriate > >> amounts of wakeup_one(). > > > I'm forwarding this to the pgsql-hackers list so that folks more > > qualified than I can comment, but as I understand the way postgres > > implements locking each process has it *own* semaphore it waits on - > > and who is waiting for what is controlled by an in (shared) memory hash > > of lock structs (access to these is controlled via platform Dependant > > spinlock code). So a given semaphore state change should only involve > > one process wakeup. > > Correct. The behavior Kris describes is surely bad, but it's not > relevant to Postgres' usage of SysV semaphores. Sorry, but the behaviour is real. Kris
Kris Kennaway <kris@obsecurity.org> writes:
> On Tue, Apr 10, 2007 at 02:46:56PM -0400, Tom Lane wrote:
>> Oh, I'm sure the BSD kernel acts as you describe. But Mark's point is
>> that Postgres never has more than one process waiting on any particular
>> SysV semaphore, and so the problem doesn't really affect us.
> To be clear, some behaviour that postgresql does with sysv semaphores
> causes wakeups of many processes at once. i.e. if you have 20
> clients, you will get up to 20 wakeups. I haven't studied the precise
> cause of this, but it is empirically true. This is the scaling
> problem I described, and it's what mux's patch addresses.
[ shrug... ] To the extent that that happens, it's Postgres' own issue,
and no amount of kernel rejiggering will change it. But I certainly
have no objection to a patch that fixes the kernel behavior ...
regards, tom lane
Kris Kennaway <kris@obsecurity.org> writes:
> I have not studied the exact code path, but there are indeed multiple
> wakeups happening from the semaphore code (as many as the number of
> active postgresql processes). It is easy to instrument
> sleepq_broadcast() and log them when they happen.
There are certainly cases where Postgres will wake up a number of
processes in quick succession, but that should happen from a separate
semop() kernel call, on a different semaphore, for each such process.
If there's really multiple processes being released by the same semop()
then there's a bug we need to look into (or maybe it's a kernel bug?).
Anyway I'd be interested to know what the test case is, and which PG
version you were testing.
regards, tom lane
On 2007-04-10, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kris Kennaway <kris@obsecurity.org> writes: >> I have not studied the exact code path, but there are indeed multiple >> wakeups happening from the semaphore code (as many as the number of >> active postgresql processes). It is easy to instrument >> sleepq_broadcast() and log them when they happen. > > There are certainly cases where Postgres will wake up a number of > processes in quick succession, but that should happen from a separate > semop() kernel call, on a different semaphore, for each such process. > If there's really multiple processes being released by the same semop() > then there's a bug we need to look into (or maybe it's a kernel bug?). > Anyway I'd be interested to know what the test case is, and which PG > version you were testing. This is a problem in FreeBSD, not specifically to do with postgres - the granularity for SysV semaphore wakeups in FreeBSD-6.x and earlier is the entire semaphore set, not just one specific semaphore within the set. I explained that to Kris some weeks ago, and someone (mux) did a patch (to FreeBSD, not pg) which was already mentioned in this discussion. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Kris Kennaway <kris@obsecurity.org> writes:
> On Tue, Apr 10, 2007 at 05:36:17PM -0400, Tom Lane wrote:
>> Anyway I'd be interested to know what the test case is, and which PG
>> version you were testing.
> I used 8.2 (and some older version when I first noticed it a year ago)
> and either sysbench or supersmack will show it - presumably anything
> that makes simultaneous queries. Just instrument sleepq_broadcast()
> to e.g. log a KTR event when it wakes more than 1 process and you'll
> see it happening.
Sorry, I'm not much of a BSD kernel hacker ... but sleepq_broadcast
seems a rather generic name. Is that called *only* from semop?
I'm wondering if you are seeing simultaneous wakeup from some other
cause --- sleep timeout being the obvious possibility. We are aware
of behaviors (search the PG lists for "context swap storm") where a
number of backends will all fail to get a spinlock and do short usleep
or select-timeout waits. In this situation they'd all wake up at the
next scheduler clock tick ...
regards, tom lane
On Tue, Apr 10, 2007 at 02:46:56PM -0400, Tom Lane wrote: > Kris Kennaway <kris@obsecurity.org> writes: > >>> Make SYSV semaphores less dumb about process wakeups. Currently > >>> whenever the semaphore state changes, all processes sleeping on the > >>> semaphore are woken, even if we only have released enough resources > >>> for one waiting process to claim. > > >> Correct. The behavior Kris describes is surely bad, but it's not > >> relevant to Postgres' usage of SysV semaphores. > > > Sorry, but the behaviour is real. > > Oh, I'm sure the BSD kernel acts as you describe. But Mark's point is > that Postgres never has more than one process waiting on any particular > SysV semaphore, and so the problem doesn't really affect us. > > Or do you mean that the kernel wakes all processes sleeping on *any* > SysV semaphore? That would be nasty :-( To be clear, some behaviour that postgresql does with sysv semaphores causes wakeups of many processes at once. i.e. if you have 20 clients, you will get up to 20 wakeups. I haven't studied the precise cause of this, but it is empirically true. This is the scaling problem I described, and it's what mux's patch addresses. Kris
On Tue, Apr 10, 2007 at 06:26:37PM -0400, Tom Lane wrote: > Kris Kennaway <kris@obsecurity.org> writes: > > On Tue, Apr 10, 2007 at 05:36:17PM -0400, Tom Lane wrote: > >> Anyway I'd be interested to know what the test case is, and which PG > >> version you were testing. > > > I used 8.2 (and some older version when I first noticed it a year ago) > > and either sysbench or supersmack will show it - presumably anything > > that makes simultaneous queries. Just instrument sleepq_broadcast() > > to e.g. log a KTR event when it wakes more than 1 process and you'll > > see it happening. > > Sorry, I'm not much of a BSD kernel hacker ... but sleepq_broadcast > seems a rather generic name. Is that called *only* from semop? It's part of how wakeup() is implemented. > I'm wondering if you are seeing simultaneous wakeup from some other > cause --- sleep timeout being the obvious possibility. We are aware > of behaviors (search the PG lists for "context swap storm") where a > number of backends will all fail to get a spinlock and do short usleep > or select-timeout waits. In this situation they'd all wake up at the > next scheduler clock tick ... Nope, it's not this. Kris
On Tue, Apr 10, 2007 at 03:52:00PM -0400, Tom Lane wrote: > Kris Kennaway <kris@obsecurity.org> writes: > > On Tue, Apr 10, 2007 at 02:46:56PM -0400, Tom Lane wrote: > >> Oh, I'm sure the BSD kernel acts as you describe. But Mark's point is > >> that Postgres never has more than one process waiting on any particular > >> SysV semaphore, and so the problem doesn't really affect us. > > > To be clear, some behaviour that postgresql does with sysv semaphores > > causes wakeups of many processes at once. i.e. if you have 20 > > clients, you will get up to 20 wakeups. I haven't studied the precise > > cause of this, but it is empirically true. This is the scaling > > problem I described, and it's what mux's patch addresses. > > [ shrug... ] To the extent that that happens, it's Postgres' own issue, > and no amount of kernel rejiggering will change it. But I certainly > have no objection to a patch that fixes the kernel behavior ... As we've discussed before, by far the bigger issue with postgresql performance on FreeBSD is the default setting of update_process_titles=on. Kris
On Tue, Apr 10, 2007 at 05:36:17PM -0400, Tom Lane wrote: > Kris Kennaway <kris@obsecurity.org> writes: > > I have not studied the exact code path, but there are indeed multiple > > wakeups happening from the semaphore code (as many as the number of > > active postgresql processes). It is easy to instrument > > sleepq_broadcast() and log them when they happen. > > There are certainly cases where Postgres will wake up a number of > processes in quick succession, but that should happen from a separate > semop() kernel call, on a different semaphore, for each such process. > If there's really multiple processes being released by the same semop() > then there's a bug we need to look into (or maybe it's a kernel bug?). > Anyway I'd be interested to know what the test case is, and which PG > version you were testing. I used 8.2 (and some older version when I first noticed it a year ago) and either sysbench or supersmack will show it - presumably anything that makes simultaneous queries. Just instrument sleepq_broadcast() to e.g. log a KTR event when it wakes more than 1 process and you'll see it happening. Kris
Mark Kirkwood wrote: > Kris Kennaway wrote: > >If so, then your task is the following: > > > >Make SYSV semaphores less dumb about process wakeups. Currently > >whenever the semaphore state changes, all processes sleeping on the > >semaphore are woken, even if we only have released enough resources > >for one waiting process to claim. i.e. there is a thundering herd > >wakeup situation which destroys performance at high loads. Fixing > >this will involve replacing the wakeup() calls with appropriate > >amounts of wakeup_one(). > > I'm forwarding this to the pgsql-hackers list so that folks more > qualified than I can comment, but as I understand the way postgres > implements locking each process has it *own* semaphore it waits on - > and who is waiting for what is controlled by an in (shared) memory hash > of lock structs (access to these is controlled via platform Dependant > spinlock code). So a given semaphore state change should only involve > one process wakeup. Yes but there are still a lot of wakeups to be avoided in the current System V semaphore code. More specifically, not only do we wakeup all the processes waiting on a single semaphore everytime something changes, but we also wakeup all processes waiting on *any* of the semaphore in the semaphore *set*, whatever the reason we're sleeping. I came up with a quick patch so that Kris could do some testing with it, and it appears to have helped, but only very slightly; apparently some contention within the netisr code caused problems, so that in some cases the patch helped slightly, and in others it didn't. The semaphore code needs a clean rewrite and I hope to take care of this soon, as time permits, since we are heavy consumers of PostgreSQL under FreeBSD at my company. Cheers, Maxime
Mark Kirkwood wrote: > Kris Kennaway wrote: > >If so, then your task is the following: > > > >Make SYSV semaphores less dumb about process wakeups. Currently > >whenever the semaphore state changes, all processes sleeping on the > >semaphore are woken, even if we only have released enough resources > >for one waiting process to claim. i.e. there is a thundering herd > >wakeup situation which destroys performance at high loads. Fixing > >this will involve replacing the wakeup() calls with appropriate > >amounts of wakeup_one(). > > I'm forwarding this to the pgsql-hackers list so that folks more > qualified than I can comment, but as I understand the way postgres > implements locking each process has it *own* semaphore it waits on - > and who is waiting for what is controlled by an in (shared) memory hash > of lock structs (access to these is controlled via platform Dependant > spinlock code). So a given semaphore state change should only involve > one process wakeup. [mail resent, it seems it got eaten by pgsql-hackers@ MTA somehow] Yes but there are still a lot of wakeups to be avoided in the current System V semaphore code. More specifically, not only do we wakeup all the processes waiting on a single semaphore everytime something changes, but we also wakeup all processes waiting on *any* of the semaphore in the semaphore *set*, whatever the reason we're sleeping. I came up with a quick patch so that Kris could do some testing with it, and it appears to have helped, but only very slightly; apparently some contention within the netisr code caused problems, so that in some cases the patch helped slightly, and in others it didn't. The semaphore code needs a clean rewrite and I hope to take care of this soon, as time permits, since we are heavy consumers of PostgreSQL under FreeBSD at my company. Cheers, Maxime