X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 40494329CE3 for ; Thu, 23 Sep 2004 20:07:55 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41359-06 for ; Thu, 23 Sep 2004 19:07:44 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 79E15329CDB for ; Thu, 23 Sep 2004 20:07:44 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8NJ6q2S028415; Thu, 23 Sep 2004 15:06:52 -0400 (EDT) To: Josh Berkus Cc: "Jason Coene" , "'Mr Pink'" , "'Scott Kirkwood'" , pgsql-performance@postgresql.org Subject: Re: Caching of Queries In-reply-to: <200409231156.31450.josh@agliodbs.com> References: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com> <27750.1095960947@sss.pgh.pa.us> <200409231156.31450.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Thu, 23 Sep 2004 11:56:31 -0700" Date: Thu, 23 Sep 2004 15:06:52 -0400 Message-ID: <28414.1095966412@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/254 X-Sequence-Number: 8382 Josh Berkus writes: >> I think you are probably looking at the same problem previously reported >> by Josh Berkus among others. > That would be interesting. Previously we've only demonstrated the > problem on long-running queries, but I suppose it could also affect > massive concurrent query access. Well, the test cases we used were designed to get the system into a tight loop of grabbing and releasing shared buffers --- a long-running index scan is certainly one of the best ways to do that, but there are others. I hadn't focused before on the point that Jason is launching a new connection for every query. In that scenario I think the bulk of the cycles are going to go into loading the per-backend catalog caches with the system catalog rows that are needed to parse and plan the query. The catalog fetches to get those rows are effectively mini-queries with preset indexscan plans, so it's not hard to believe that they'd be hitting the BufMgrLock nearly as hard as a tight indexscan loop. Once all the pages needed are cached in shared buffers, there's no I/O delays to break the loop, and so you could indeed get into the context swap storm regime we saw before. I concur with the thought that using persistent connections might go a long way towards alleviating his problem. regards, tom lane