Re: postgresql and process titles

On Sun, Jun 11, 2006 at 10:07:13PM -0500, Jim C. Nasby wrote:
> On Sun, Jun 11, 2006 at 09:58:33PM -0400, Tom Lane wrote:
> > Kris Kennaway <kris(at)obsecurity(dot)org> writes:
> > > On Sun, Jun 11, 2006 at 07:43:03PM -0400, Tom Lane wrote:
> > >> Let's see the evidence.
> >
> > > The calls to setproctitle() (it looks like 4 setproctitle syscalls per
> > > DB query) are causing contention on the Giant lock 25% of the time on
> > > a dual p4 + HTT. Disabling process title setting completely gives an
> > > 8% peak performance boost to the super-smack select benchmark.
> >
> > I think you misunderstood me: I asked for evidence, not interpretation.
> > What are you measuring, and with what tool, and what are the numbers?
> > On what benchmark case? And what did you do to "disable process title
> > setting completely"?
> >
> > The reason I'm being doubting Thomas here is that I've never seen any
>
> Ba-da-bum!
>
> > indication on any other platform that ps_status is a major bottleneck.
> > Now maybe FreeBSD really sucks, or maybe you're onto something of
> > interest, but let's see the proof in a form that someone else can
> > check and reproduce.
>
> It's also important to find out what version of FreeBSD this is. A lot
> of things have been pulled out of GIANT in 5.x and 6.x, so it's entirely
> possible this isn't an issue in newer versions.

It's still true in 6.x and 7.x. I have a patch that removes Giant
from the sysctl in question, and I have also removed it from another
relevant part of the kernel (semop() is bogusly acquiring Giant when
it is supposed to be mpsafe).

When it's possible to commit that patch (should be in time for 7.0,
but not sure if it will make it into 6.2) it will eliminate the worst
part of the problem, but it still leaves postgresql making thousands
of syscalls per second to flip its process titles back and forth,
which needs to be looked at carefully for a performance impact. For
now, users of FreeBSD who want that 8% should turn it off though (or
maybe one of the alternative methods is usable).

BTW, another promising performance/scalability change on BSD systems
would be to convert pgsql to use kqueue instead of select, since mutex
profiling traces show a lot of contention on the select lock in
FreeBSD.

FYI, the biggest source of contention is via semop() - it might be
possible to optimize that some more in FreeBSD, I don't know.

Kris