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

On Tue, Jun 13, 2006 at 12:29:14PM -0500, Jim C. Nasby wrote:

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

Thanks for the reference, I'll check it out.  Brain-dead or not though
(and I've also seen evidence that it is, e.g. it does a hell of a lot
of 1-byte reads), it's a well known benchmark that seems to be fairly
widely used, so there's still some value in making it perform well
(but not at the expense of other things).  However my interest is not
so much for measuring database performance as for measuring kernel
performance and trying to optimize bottlenecks.

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

It could be it's making assumptions about what is cheap and what is
not, which are not true universally.  e.g. you might be adding a new
contention point that is even worse.  I didn't notice a change in
behaviour as I varied the load, so perhaps it was not visible on this
benchmark or I wasn't looking in the right place.

Kris