Postgresql performance profiling

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
bottlenecks:

* 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

Kris

Attachment: pgpbz7M872V7d.pgp
Description: PGP signature

Relevant Pages

  • Re: [patch 1/4] x86: FIFO ticket spinlocks
    ... unfair spinlocks have the potential to perform much better. ... that "given equal contention, let the guy with the hottest cache win". ... that are *protected* by the spinlock on just one CPU. ...
    (Linux-Kernel)
  • Re: [RFC PATCH] Fair rwlock
    ... Tweakable contention behavior seems interesting, ... comes in and asks for a read lock, it has to get it on the spot. ... ok, there's a pending write lock holder on _my_ CPU, so I need to just ... interrupts obviously have to be disabled here, because any reader can ...
    (Linux-Kernel)
  • Re: [patch, try 1] Re: Trouble with NFSd under 6.1-Stable, any ideas?
    ... >> of a directory structure results in sluggishness and extreme CPU usage. ... aquire additional locks on Giant, ... System with patch applied survived smoke test (client did ... du on mounted dir, patch was generated from exported fs, etc.). ...
    (freebsd-stable)
  • Re: race on multi-processor solaris
    ... > On a linked list with locks, one thread will always be running on each ... There will be massive contention. ... busy-waiting threads waste an appreciable amount of CPU time. ...
    (comp.unix.solaris)
  • Re: Fast Threading ?
    ... > Lock contention increases exponentially with the ... overhead increases lock hold times and contention dramatically. ... By "multitasking", I mean breaking up work into tasks, and then having "fixed" threads (one per CPU) process the tasks rather than let the OS handle multithreading itself. ...
    (borland.public.delphi.language.basm)