Re: postgresql and process titles

On Mon, Jun 12, 2006 at 11:38:01AM -0400, Kris Kennaway wrote:
> 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.
Unless supersmack has improved substantially, you're unlikely to find
much interest. Last I heard it was a pretty brain-dead benchmark. DBT2/3
(http://sourceforge.net/projects/osdldbt) is much more realistic (based
on TPC-C and TPC-H).
> > > 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.

FWIW, getting turning off stats_command_string substantially reduced
this contention, so it appears the issue is somewhere in the stats code.
This code sends stats messages to a different process via a socket (or
is it UDP?), with the intention that if the system gets heavily loaded
we'll lose some stats in the interest of not bogging down all the
backends. It seems that doesn't work so hot on FreeBSD. :(
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461