X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E63FA9FB4B3; Thu, 20 Jul 2006 16:54:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 21673-01-4; Thu, 20 Jul 2006 19:54:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 65C039FB564; Thu, 20 Jul 2006 16:02:00 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k6KJ1wAw008670; Thu, 20 Jul 2006 15:01:59 -0400 (EDT) To: Tatsuo Ishii cc: Robert.Lor@Sun.COM, pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Sun Donated a Sun Fire T2000 to the PostgreSQL In-reply-to: <20060713.180234.45872055.t-ishii@sraoss.co.jp> References: <20060617.101521.95879771.t-ishii@sraoss.co.jp> <10226.1150511655@sss.pgh.pa.us> <20060617.111838.39487910.t-ishii@sraoss.co.jp> <20060713.180234.45872055.t-ishii@sraoss.co.jp> Comments: In-reply-to Tatsuo Ishii message dated "Thu, 13 Jul 2006 18:02:34 +0900" Date: Thu, 20 Jul 2006 15:01:58 -0400 Message-ID: <8669.1153422118@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200607/200 X-Sequence-Number: 20060 Tatsuo Ishii writes: >>> 18% in s_lock is definitely bad :-(. Were you able to determine which >>> LWLock(s) are accounting for the contention? > Sorry for the delay. Finally I got the oprofile data. It's > huge(34MB). If you are interested, I can put somewhere. Please let me > know. I finally got a chance to look at this, and it seems clear that all the traffic is on the BufMappingLock. This is essentially the same problem we were discussing with respect to Gavin Hamill's report of poor performance on an 8-way IBM PPC64 box (see hackers archives around 2006-04-21). If your database is fully cached in shared buffers, then you can do a whole lot of buffer accesses per unit time, and even though all the BufMappingLock acquisitions are in shared-LWLock mode, the LWLock's spinlock ends up being heavily contended on an SMP box. It's likely that CVS HEAD would show somewhat better performance because of the btree change to cache local copies of index metapages (which eliminates a fair fraction of buffer accesses, at least in Gavin's test case). Getting much further than that seems to require partitioning the buffer mapping table. The last discussion stalled on my concerns about unpredictable shared memory usage, but I have some ideas on that which I'll post separately. In the meantime, thanks for sending along the oprofile data! regards, tom lane