Date: Sun, 11 Jun 2006 13:45:28 -0400 From: Kris Kennaway <kris@obsecurity.org> To: performance@FreeBSD.org Cc: scrappy@FreeBSD.org Subject: Postgresql performance profiling Message-ID: <20060611174527.GA31119@xor.obsecurity.org>
next in thread | raw e-mail | index | archive | help
--3V7upXqbjpZ4EhLz Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --3V7upXqbjpZ4EhLz Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (FreeBSD) iD8DBQFEjFa3Wry0BWjoQKURAnpmAKClAPG4O9VCh82gg30kdE4xVyw6gwCgw1fz Xr5QpUf1hCBIIXmcZuNdx8U= =Tu8r -----END PGP SIGNATURE----- --3V7upXqbjpZ4EhLz--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20060611174527.GA31119>