Postgresql performance profiling

Kris Kennaway kris at
Sun Jun 11 17:45:29 UTC 2006

I set up supersmack against postgresql 8.1 from ports (default config)
on a 12 CPU E4500.  It scales and performs somewhat better than mysql
on this machine (which is heavily limited by contention between
threads in a process), but there are a number of obvious performance

* The postgres processes seem to change their proctitle hundreds or
thousands of times per second.  This is currently done via a
Giant-locked sysctl (kern.proc.args) so there is enormous contention
for Giant.  Even when this is fixed (thanks to a patch from csjp@),
each of them requires a syscall and syscalls ain't free.  This is not
a clever thing to be doing from a performance standpoint.

* pgsql uses select() and this seems to be a major choke point.  I bet
you'd see fairly impressive performance gains (especially on SMP) if
it was modified to use kqueue instead of select.

* You really want to avoid using IPv6 for transport (since it's
Giant-locked).  This was an issue at first since I was running against
localhost, which maps to ::1 by default.  We should reconsider the
preference for IPv6 over IPv4 until IPv6 is Giant-free - there are
probably many other situations where IPv6 is being secretly used
"because it is there" and costing performance.

* The sysv IPC code is still giant-locked.  pgsql makes a lot of
semop() calls which grab Giant, and it also msleep()s on the Giant
lock in the semwait channel.

* When semop() wants to wake up some sleeping processes because
semaphores have been released, it does a wakeup() and wakes them all
up.  This means a thundering herd (I see up to 11 CPUs being woken
here).  Since we know exactly how many resources are available, it
would be better to only wakeup_one() that number of times instead.

Here are what seem to be the relevant heavily-contended mutex
acquisitions (ratio = cnt_lock/count measures how many times this lock
was contended by something else while held by this code line):

  count   cnt_hold cnt_lock ratio name
 106080     7420    19238   .181 kern/kern_synch.c:222 (lockbuilder mtxpool) <-- vfs
 175435    13952    42365   .241 kern/kern_condvar.c:113 (lockbuilder mtxpool) <-- vfs
1075841   271138   419862   .390 kern/kern_synch.c:220 (Giant) <-- msleep with Giant
 734613   248249   291969   .397 kern/sys_generic.c:1140 (sellck) <-- select
 800332   379020   326324   .407 kern/sys_generic.c:944 (sellck) <-- select
 401751    19731   175305   .436 kern/sys_generic.c:1092 (sellck) <-- select
 400280   198880   176623   .441 kern/sys_generic.c:935 (sellck) <-- select
1361163   695637   624171   .458 sparc64/sparc64/trap.c:586 (Giant) <-- semop
 400190   193112   238578   .596 kern/kern_condvar.c:208 (sellck) <-- select

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url :

More information about the freebsd-performance mailing list