Re: postgresql and process titles

On Mon, Jun 12, 2006 at 12:24:36AM -0400, Kris Kennaway wrote:
> 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.

Can you provide the actual commands you used to setup and run the test?
This would allow others to duplicate your results and debug the
situation on their own. This is also important because we've generally
found HTT to be a loss (except on Windows), so it'd be good to see what
impact this has on AMD hardware. It would also be very useful to have
the raw test result numbers you obtained.

> 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).

What affect did that patch have on the numbers? And where is it, in case
anyone here wants to try it?

> 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).

We have a similar issue internally with stats_command_string. The issue
is that it's very helpful to be able to see what a 'long running' (more
than a second or so) statement is doing, but there's very little value
in doing all that work for a statement that's only going to run for a
few ms. If there's a very cheap way to set some kind of a timer that
would update this information once a statement's been around long enough
that might be a way to handle this (I don't know if we're already using
ALRM in the backend, or if that's a cheap enough solution).

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

Yeah, I've seen PostgreSQL on FreeBSD fall over at high load with a lot
of procs in either semwait or semlock. :(
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461