Postgresql performance profiling

[Kris Kennaway]

On Sun, Jun 11, 2006 at 01:45:28PM -0400, Kris Kennaway wrote:
> 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:

FYI, on a dual p4 + HTT, mysql significantly outperforms pgsql (by
>55% peak performance, probably more if I was using libthr which I
cannot on this machine for technical reasons) on select-key.smack when
configured the same way (i.e. transport over IPv4 instead of local
socket, which supersmack prefers for mysql).

Contention is still a big issue here (only listing mutexes contended
more than 10% of acquisitions):

     0          0     142969      0       1996      14458   .101 kern/kern_synch.c:218 (Giant)
     0          0     199028      0      11649      27944   .140 kern/kern_condvar.c:208 (sellck)
     0          0     400103      0     111216      91336   .228 kern/kern_sysctl.c:1317 (Giant)
     0          0     303147      0     108735     131237   .432 i386/i386/trap.c:1005 (Giant)

I turned off process title setting and got an 8% performance boost.

Contention is now a bit better but still serious:

     0          0      22952      0       2067       2521   .109 vm/vm_fault.c:987 (vm object)
     0          0     199153      0      12589      31512   .158 kern/kern_condvar.c:208 (sellck)
     0          0     361305      0     124766     130901   .362 i386/i386/trap.c:1005 (Giant)

i.e. semop() (the Giant-locked syscall) is contending with itself a
lot, and select() is a secondary problem.

Actually rwatson noticed that semop() is marked MPSAFE, so it's not
clear (but nevertheless true) why Giant is acquired here.  OK, pjd
worked out that it's because SYSCALL_MODULE_HELPER() *never* sets the
mpsafe flag, so all such syscalls registered that way (i.e. those
which are part of subsystems that may be loaded from kld) are
Giant-locked regardless of what syscalls.master says.

I removed the SYSCALL_MODULE_HELPERs from sysv_sem.c but now
postgresql hangs when trying to start; possibly the locking in
sysv_sem.c is just broken since it was never in fact tested.

Kris

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


-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20060611/38bca5d0/attachment.pgp