From owner-freebsd-performance@FreeBSD.ORG Sun Jun 11 17:45:29 2006 Return-Path: X-Original-To: performance@FreeBSD.org Delivered-To: freebsd-performance@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 832C216A494; Sun, 11 Jun 2006 17:45:29 +0000 (UTC) (envelope-from kris@obsecurity.org) Received: from elvis.mu.org (elvis.mu.org [192.203.228.196]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3F63D43D49; Sun, 11 Jun 2006 17:45:29 +0000 (GMT) (envelope-from kris@obsecurity.org) Received: from obsecurity.dyndns.org (elvis.mu.org [192.203.228.196]) by elvis.mu.org (Postfix) with ESMTP id 20CC11A3C2D; Sun, 11 Jun 2006 10:45:29 -0700 (PDT) Received: by obsecurity.dyndns.org (Postfix, from userid 1000) id 598F65157C; Sun, 11 Jun 2006 13:45:28 -0400 (EDT) Date: Sun, 11 Jun 2006 13:45:28 -0400 From: Kris Kennaway To: performance@FreeBSD.org Message-ID: <20060611174527.GA31119@xor.obsecurity.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="3V7upXqbjpZ4EhLz" Content-Disposition: inline User-Agent: Mutt/1.4.2.1i Cc: scrappy@FreeBSD.org Subject: Postgresql performance profiling X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 11 Jun 2006 17:45:29 -0000 --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--