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 A6CD2329E52 for ; Sat, 2 Oct 2004 22:08:02 +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 76250-03 for ; Sat, 2 Oct 2004 21:07:57 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id DFCFC329DCD for ; Sat, 2 Oct 2004 22:07:59 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i92L7rbx077650 for ; Sat, 2 Oct 2004 21:07:53 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i92KjUV9072856 for pgsql-performance@postgresql.org; Sat, 2 Oct 2004 20:45:30 GMT From: William Yu X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Caching of Queries Date: Sat, 02 Oct 2004 13:47:26 -0700 Organization: Hub.Org Networking Services Lines: 23 Message-ID: References: <00e801c4a4b6$5526dd30$8300a8c0@solent> <200410011010.40705.josh@agliodbs.com> <200410021304.21281.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en In-Reply-To: <200410021304.21281.josh@agliodbs.com> To: pgsql-performance@postgresql.org 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: 200410/16 X-Sequence-Number: 8492 Josh Berkus wrote: > 1) Query caching is not a single problem, but rather several different > problems requiring several different solutions. > > 2) Of these several different solutions, any particular query result caching > implementation (but particularly MySQL's) is rather limited in its > applicability, partly due to the tradeoffs required. Per your explanation, > Oracle has improved this by offering a number of configurable options. > > 3) Certain other caching problems would be solved in part by the ability to > construct "in-memory" tables which would be non-durable and protected from > cache-flushing. This is what I'm interested in chatting about. Just my 2 cents on this whole issue. I would lean towards having result caching in pgpool versus the main backend. I want every ounce of memory on a database server devoted to the database. Caching results would double the effect of cache flushing ... ie, now both the results and the pages used to build the results are in memory pushing out other stuff to disk that may be just as important. If it was in pgpool or something similar, I could devote a separate machine just for caching results leaving the db server untouched.