Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5057E9FB310 for ; Thu, 26 Jul 2007 18:18:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 00797-02 for ; Thu, 26 Jul 2007 18:18:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from brmea-mail-3.sun.com (brmea-mail-3.Sun.COM [192.18.98.34]) by postgresql.org (Postfix) with ESMTP id 2E8C49FB289 for ; Thu, 26 Jul 2007 18:17:58 -0300 (ADT) Received: from fe-amer-03.sun.com ([192.18.108.177]) by brmea-mail-3.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l6QLHvUg007688 for ; Thu, 26 Jul 2007 21:17:57 GMT Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) id <0JLT00H011KUOK00@mail-amer.sun.com> (original mail from J.K.Shah@Sun.COM) for pgsql-performance@postgresql.org; Thu, 26 Jul 2007 15:17:57 -0600 (MDT) Received: from [129.148.9.17] by mail-amer.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPSA id <0JLT00IE01TWC9J4@mail-amer.sun.com>; Thu, 26 Jul 2007 15:17:57 -0600 (MDT) Date: Thu, 26 Jul 2007 17:17:55 -0400 From: "Jignesh K. Shah" Subject: Re: User concurrency thresholding: where do I look? In-reply-to: <1185480291.5510.22.camel@ebony.site> To: Simon Riggs Cc: Tom Lane , Josh Berkus , pgsql-performance@postgresql.org, Alvaro Herrera Message-id: <46A90F83.8050401@sun.com> Organization: Sun Microsystems MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=iso-8859-1 Content-transfer-encoding: 7BIT References: <200707190828.59088.josh@agliodbs.com> <200707191819.13899.josh@agliodbs.com> <15685.1184895926@sss.pgh.pa.us> <200707192138.39679.josh@agliodbs.com> <46A0FA31.2080208@sun.com> <12002.1184955831@sss.pgh.pa.us> <46A1055F.9010304@sun.com> <12445.1184958793@sss.pgh.pa.us> <46A11116.5030905@sun.com> <14909.1184962561@sss.pgh.pa.us> <46A1205B.8010205@sun.com> <15343.1184965054@sss.pgh.pa.us> <1185180668.4284.149.camel@ebony.site> <23848.1185199901@sss.pgh.pa.us> <1185201556.4284.243.camel@ebony.site> <25095.1185202498@sss.pgh.pa.us> <1185205663.4284.249.camel@ebony.site> <1185212261.4284.270.camel@ebony.site> <1545.1185214760@sss.pgh.pa.us> <46A7D5AD.9000706@sun.com> <13162.1185405865@sss.pgh.pa.us> <46A8AFDF.3050804@sun.com> <26825.1185460816@sss.pgh.pa.us> <46A8BD62.2080904@sun.com> <1185465076.4190.87.camel@ebony.site> <46A8F9AA.4040507@sun.com> <1185480291.5510.22.camel@ebony.site> User-Agent: Thunderbird 1.5.0.8 (X11/20061110) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/375 X-Sequence-Number: 26059 Will try 16 and 32 CLOGBUFFER tomorrow: But here is locks data again with about increased time profiling (about 2 minutes) for the connection with about 2000 users: bash-3.00# time ./4_lwlock_waits.d 13583 ^C Lock Id Mode Count ProcArrayLock Shared 5 XidGenLock Exclusive 13 CLogControlLock Shared 14 XidGenLock Shared 15 CLogControlLock Exclusive 21 WALInsertLock Exclusive 77 WALWriteLock Exclusive 175 ProcArrayLock Exclusive 275 Lock Id Combined Time (ns) XidGenLock 194966200 WALInsertLock 517955000 CLogControlLock 679665100 WALWriteLock 2838716200 ProcArrayLock 44181002600 Top Wait time seems to come from the following code path for ProcArrayLock: Lock Id Mode Count ProcArrayLock Exclusive 21 Lock Id Combined Time (ns) ProcArrayLock 5255937500 Lock Id Combined Time (ns) postgres`LWLockAcquire+0x1f0 postgres`CommitTransaction+0x104 postgres`CommitTransactionCommand+0xbc postgres`finish_xact_command+0x78 postgres`exec_execute_message+0x42c postgres`PostgresMain+0x1838 postgres`BackendRun+0x2f8 postgres`ServerLoop+0x680 postgres`PostmasterMain+0xda8 postgres`main+0x3d0 postgres`_start+0x17c 5255937500 Regards, Jignesh Simon Riggs wrote: > On Thu, 2007-07-26 at 15:44 -0400, Jignesh K. Shah wrote: > >> BEAUTIFUL!!! >> >> Using the Patch I can now go upto 1300 users without dropping.. But now >> it still repeats at 1300-1350 users.. >> > > OK, can you try again with 16 and 32 buffers please? We need to know > how many is enough and whether this number needs to be variable via a > parameter, or just slightly larger by default. Thanks. > > >> I corrected the Lock Descriptions based on what I got from lwlock.h and >> retried the whole scalability again with profiling again.. This time it >> looks like the ProcArrayLock >> > > That's what I would expect with that many users. > > >> Lock Id Mode Count >> XidGenLock Exclusive 1 >> CLogControlLock Shared 2 >> XidGenLock Shared 2 >> WALWriteLock Exclusive 4 >> WALInsertLock Exclusive 8 >> CLogControlLock Exclusive 9 >> ProcArrayLock Exclusive 9 >> > > ...but as Tom mentioned, we need to do longer runs now so these counts > get to somewhere in the hundreds so we have some statistical validity. > >