Re: postgresql and process titles - Mailing list pgsql-hackers

On Mon, Jun 12, 2006 at 10:08:22AM -0500, Jim C. Nasby wrote:
> 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@obsecurity.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?

I actually forget all the steps I needed to do to get super-smack
working with postgresql since it required a lot of trial and error for
a database newbie like me (compiling it from the
benchmarks/super-smack port was trivial, but unlike mysql it required
configuring the database by hand - this should hopefully be more
obvious to someone familiar with pgsql though).

It would be great if someone on your end could make this easier, BTW -
e.g. at least document the steps.  Also super-smack should be changed
to allow use via a local socket with pgsql (this is the default with
mysql) - this avoids measuring network stack overhead.

The only thing I had to change on FreeBSD was to edit the
select-key.smack and change "localhost" to "127.0.0.1" in two
locations to avoid possibly using IPv6 transport.

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

They were posted previously.  This is Intel hardware (AMD doesn't do
HTT), and I didn't retest without HTT.  I'll try to do so if I have
the time (however previously when profiling mysql, HTT did give a small
positive change).

> > 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?

I didn't yet retest with the patch.  It's in my perforce branch:
 http://perforce.freebsd.org/changeList.cgi?FSPC=//depot/user/kris/contention/...

although you probably need a combination of the changes in order for
it to be usable.

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

I don't know what the best way to implement it would be, but limiting
the frequency of these updates does seem to be the way to go.

> > 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. :(

Part of that is Giant contention again; for example on 6.x semop() and
setproctitle() both want to acquire it, so they'll fight with each
other and with anything else on the system that wants Giant
(e.g. IPv6, or the USB stack, etc).  As I mentioned Giant is not an
issue here going forward, but there is still as much lock contention
just between semop() calls running on different CPUs.  It may be
possible for someone to implement more fine-grained locking here, but
I don't know if there is available interest.

Kris