Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 01A1E6346ED for ; Fri, 13 Mar 2009 00:00:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 21883-10 for ; Fri, 13 Mar 2009 00:00:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by mail.postgresql.org (Postfix) with ESMTP id 36E6C632662 for ; Fri, 13 Mar 2009 00:00:39 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id n2D30cpp025759; Thu, 12 Mar 2009 23:00:38 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id n2D30c0K025756; Thu, 12 Mar 2009 23:00:38 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Thu, 12 Mar 2009 23:00:38 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Scott Carey cc: "Jignesh K. Shah" , Kevin Grittner , "pgsql-performance@postgresql.org" Subject: Re: Proposal of tunable fix for scalability of 8.4 In-Reply-To: Message-ID: References: User-Agent: Alpine 2.01 (GSO 1184 2008-12-16) MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: Content-Type: TEXT/PLAIN; CHARSET=ISO-8859-15; format=flowed Content-Transfer-Encoding: 8BIT Content-ID: X-Archive-Number: 200903/147 X-Sequence-Number: 33069 On Thu, 12 Mar 2009, Scott Carey wrote: > Furthermore, if the problem was due to too much concurrency in the > database with active connections, its hard to see how changing the lock > code would change the result the way it did ? What I wonder about is if the locking mechanism is accidentally turning into a CPU resource scheduling problem on this benchmark. If the connections were pooled instead, control over that scheduling would be more explicit, because connections would more directly map onto physical CPUs. What if the fall-off is because the sum of the working code set here is simply exceeding the sum of the CPU caching available once the number of active connections gets big enough? The real problem could be that the connections waiting on ProcArray are just falling out of cache, such that when they do wake up they take a while to page back in and keep going. I wouldn't actually bet anything on that theory though, or any of the others offered here. I find wandering into performance bottleneck analysis presuming you know what's going on to be dangerous. The bigger issue here is that Jignesh is using a configuration known to be problematic (lots of connections), which introduces some uncertaintly about the true root cause here. Whether it's well founded or not, it still hurts his case. And to step back for a second, after reading up on it again I see that Sun's internal iGen-OLTP benchmark "stresses lock management and connectivity"[1], which makes me wonder even more than I did before about how specific this fix is to this workload. [1] http://blogs.sun.com/bmseer/entry/t2000_adds_database_leadership_to > First just run a test with a tiny delay (5ms? 0?) and fewer users to > compare.  If your theory that a connection pooler would help, that test > would provide higher throughput with low user count and not be lock > limited. If the symptoms stay the same but are just scaled to a much lower connection count, that might help rule out some types of context switching and caching problem from the list of most likely suspects. Might as well make it 0ms to minimize the number of connections. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD From pgsql-performance-owner@postgresql.org Fri Mar 13 01:23:45 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7C52663584A for ; Fri, 13 Mar 2009 01:23:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 46725-06-4 for ; Fri, 13 Mar 2009 01:23:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by mail.postgresql.org (Postfix) with ESMTP id 87C6763EF14 for ; Fri, 13 Mar 2009 00:31:06 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id n2D3V5tP029355; Thu, 12 Mar 2009 23:31:05 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id n2D3V5QC029351; Thu, 12 Mar 2009 23:31:05 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Thu, 12 Mar 2009 23:31:05 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: "Jignesh K. Shah" cc: Scott Carey , Tom Lane , Kevin Grittner , "pgsql-performance@postgresql.org" Subject: Re: Proposal of tunable fix for scalability of 8.4 In-Reply-To: <49B9566C.3010708@sun.com> Message-ID: References: <49B9566C.3010708@sun.com> User-Agent: Alpine 2.01 (GSO 1184 2008-12-16) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/148 X-Sequence-Number: 33070 On Thu, 12 Mar 2009, Jignesh K. Shah wrote: > That said the testkit that I am using is a lightweight OLTP typish > workload which a user runs against a preknown schema and between various > transactions that it does it emulates a wait time of 200ms. After re-reading about this all again at http://blogs.sun.com/jkshah/resource/pgcon_problems.pdf I remembered I wanted more info on just what Sun's iGen OLTP does anyway. Here's a collection of published comments on it that assembles into a reasonably detailed picture, as long as you're somewhat familiar with what TPC-C does: http://blogs.sun.com/bmseer/entry/t2000_adds_database_leadership_to "The iGEN-OLTP 1.5 benchmark is a SUN internally developed transaction processing database workload. This workload simulates a light-weight Global Order System that stresses lock management and connectivity." http://www.mysqlperformanceblog.com/2008/02/27/a-piece-of-sunmysql-marketing/#comment-246663 "The iGen workload was created from actual customer workloads and has a lot more complexity than Sysbench which only test very simple operations one at a time. The iGen database consist of 6 tables and its executes a combination of light, medium and heavy transactions." http://www.sun.com/third-party/global/oracle/collateral/T2000_Oracle_iGEN_05-12-06.pdf?null "The iGEN-OLTP benchmark is a stress and performance test, measuring the throughput and simultaneous user connections of an OLTP database workload. The iGEN-OLTP workload is based on customer applications and is constructed as a 2-tier orders database application where three transactions are executed: * light read-only query * medium read-only query * 'heavy' read and insert operation. The transactions are comprised of various SQL statements: read-only selects, joins, update and insert operations. iGen OLTP avoids problems that plague other OTLP benchmarks like TPC-C. TPC-C has problems with only using light-weight queries, allowing artificial data partitioning, and only testing a few database functions. The iGen transactions take almost twice the computation work compared to the TPC-C transactions." http://blogs.sun.com/ritu/entry/mysql_benchmark_us_t2_beats "iGen OLTP avoids problems that plague other OTLP benchmarks like TPC-C. In particular, it is completely random in table row selections and thus is difficult to use artificial optimizations. iGen OLTP stresses process and thread creation, process scheduling, and database commit processing...The transactions are comprised of various SQL transactions: read-only selects, joins, inserts and update operations." -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD