Re: Scalability in postgres - Mailing list pgsql-performance

On Thu, 28 May 2009, Flavio Henrique Araque Gurgel wrote:

> It is 2.6.24 We had to apply the kswapd patch also. It's important
> specially if you see your system % going as high as 99% in top and
> loosing the machine's control. I have read something about 2.6.28 had
> this patch accepted in mainstream.

It would help if you gave more specific information about what you're
talking about.  I know there was a bunch of back and forth on the "kswapd
should only wait on IO if there is IO" patch, where it was commited and
then reverted etc, but it's not clear to me if that's what you're talking
about--and if so, what that has to do with the context switch problem.

Back to Fabrix's problem.  You're fighting a couple of losing battles
here.  Let's go over the initial list:

1) You have 32 cores.  You think they should be allowed to schedule
>3500 active connections across them.  That doesn't work, and what happens
is exactly the sort of context switch storm you're showing data for.
Think about it for a minute:  how many of those can really be doing work
at any time?  32, that's how many.  Now, you need some multiple of the
number of cores to try to make sure everybody is always busy, but that
multiple should be closer to 10X the number of cores rather than 100X.
You need to adjust the connection pool ratio so that the PostgreSQL
max_connections is closer to 500 than 5000, and this is by far the most
critical thing for you to do.  The PostgreSQL connection handler is known
to be bad at handling high connection loads compared to the popular
pooling projects, so you really shouldn't throw this problem at it.
While kernel problems stack on top of that, you really shouldn't start at
kernel fixes; nail the really fundamental and obvious problem first.

2) You have very new hardware and a very old kernel.  Once you've done the
above, if you're still not happy with performance, at that point you
should consider using a newer one.  It's fairly simple to build a Linux
kernel using the same basic kernel parameters as the stock RedHat one.
2.6.28 is six months old now, is up to 2.6.28.10, and has gotten a lot
more testing than most kernels due to it being the Ubuntu 9.04 default.
I'd suggest you try out that version.

3) A system with 128GB of RAM is in a funny place where by using the
defaults or the usual rules of thumb for a lot of parameters ("set
shared_buffers to 1/4 of RAM") are all bad ideas.  shared_buffers seems to
top out its usefulness around 10GB on current generation
hardware/software, and some Linux memory tunables have defaults on 2.6.18
that are insane for your system; vm_dirty_ratio at 40 comes to mind as the
one I run into most.  Some of that gets fixed just by moving to a newer
kernel, some doesn't.  Again, these aren't the problems you're having now
though; they're the ones you'll have in the future *if* you fix the more
fundamental problems first.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD