X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A86C4529AC for ; Wed, 20 Jul 2005 06:54:21 -0300 (ADT) 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 26837-07 for ; Wed, 20 Jul 2005 09:54:15 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 2793D528FF for ; Wed, 20 Jul 2005 06:54:14 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j6K9s9H8016169 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 20 Jul 2005 03:54:12 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j6K9s8pF042931; Wed, 20 Jul 2005 03:54:08 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j6K9s749042930; Wed, 20 Jul 2005 03:54:07 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 20 Jul 2005 03:54:07 -0600 From: Michael Fuhr To: Tsirkin Evgeny Cc: Chris Travers , Tsirkin Evgeny , pgsql-admin@postgresql.org Subject: Re: caching table/query Message-ID: <20050720095407.GA38597@winnie.fuhr.org> References: <42DA3A4C.3080009@mail.jct.ac.il> <20050717140655.GA9637@winnie.fuhr.org> <42DA7226.5070808@mail.jct.ac.il> <28088.1121615607@sss.pgh.pa.us> <42DB4DE2.6090304@mail.jct.ac.il> <42DDEC2C.9050009@metatrontech.com> <42DDFC87.1000807@mail.jct.ac.il> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42DDFC87.1000807@mail.jct.ac.il> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/148 X-Sequence-Number: 18204 On Wed, Jul 20, 2005 at 10:25:59AM +0300, Tsirkin Evgeny wrote: > trying to reduce query time and since that is a web application i now > using caching with perl MLDBM::Sync::SDBM_File on application level. > (Basically that involves quering all the table and putting it into > MLDBM::Sync::SDBM_File and then quering it back). What is interesting > is that while time quering the tables from postgres is taken 0.3 sec. > using sdbm it takes 0.1 sec. PostgreSQL isn't likely to win a speed contest against a trivial storage/retrieval library when the test involves simple queries and little or no concurrency. PostgreSQL is a full-featured database with goals beyond just "make SELECT statements as fast as possible." > Actually i am not anymore sure that postgres does NOT cache table in memory, > maybe it does and i don't know about it? As I mentioned in an earlier message, you could enable statistics gathering and use the statistics views to see whether your queries are being satistified from the buffer cache or if they require a call to the operating system: http://www.postgresql.org/docs/8.0/static/monitoring-stats.html The first time you query a table it will probably need to be fetched from disk (or from the operating system's cache); subsequent queries will likely be much faster because PostgreSQL will have stored the fetched pages in its buffer cache. Example: SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = 'foo'; heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit ----------------+---------------+---------------+-------------- 0 | 0 | 0 | 0 (1 row) EXPLAIN ANALYZE SELECT * FROM foo WHERE id = 12345; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Index Scan using foo_pkey on foo (cost=0.00..3.01 rows=1 width=18) (actual time=73.686..73.698 rows=1 loops=1) Index Cond: (id = 12345) Total runtime: 73.934 ms (3 rows) SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = 'foo'; heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit ----------------+---------------+---------------+-------------- 1 | 0 | 3 | 0 (1 row) EXPLAIN ANALYZE SELECT * FROM foo WHERE id = 12345; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using foo_pkey on foo (cost=0.00..3.01 rows=1 width=18) (actual time=0.072..0.083 rows=1 loops=1) Index Cond: (id = 12345) Total runtime: 0.237 ms (3 rows) SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = 'foo'; heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit ----------------+---------------+---------------+-------------- 1 | 1 | 3 | 3 (1 row) Notice how much faster the second query was, even though it was identical to the first. As you can see from the statistics view, the second query's page fetches were all "hits," meaning they were retrieved from the buffer cache. If you want to analyze performance, then make sure your tests mimic the activity you expect to see on the production system, such as number of concurrent connections; amount of select, insert, update, and delete activity; similar data sets in terms of size and distribution; representative queries; etc. Use PostgreSQL's and the operating system's instrumentation to identify performance bottlenecks, and then look for remedies. BTW, pgsql-performance might be a more appropriate list for this thread. -- Michael Fuhr http://www.fuhr.org/~mfuhr/