X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D8ED39DCA72 for ; Sat, 11 Mar 2006 02:14:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43933-10 for ; Sat, 11 Mar 2006 02:14:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.travelamericas.com (unknown [206.130.134.147]) by postgresql.org (Postfix) with SMTP id 4263A9DC842 for ; Sat, 11 Mar 2006 02:14:17 -0400 (AST) Received: (qmail 30477 invoked from network); 11 Mar 2006 06:14:16 -0000 Received: from unknown (HELO ?10.0.0.253?) (10.0.0.253) by verkiel.travelamericas.com with SMTP; 11 Mar 2006 06:14:16 -0000 Message-ID: <44126AB7.6060709@metatrontech.com> Date: Fri, 10 Mar 2006 22:14:15 -0800 From: Chris Travers User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Andre Felipe Machado CC: pgsql-performance@postgresql.org Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison References: <20060307162842.M37264@techforce.com.br> <1142041197.7572.6.camel@localhost.localdomain> In-Reply-To: <1142041197.7572.6.camel@localhost.localdomain> Content-Type: multipart/mixed; boundary="------------020806090202020900040704" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.106 required=5 tests=[AWL=0.106] X-Spam-Score: 0.106 X-Spam-Level: X-Archive-Number: 200603/309 X-Sequence-Number: 17746 This is a multi-part message in MIME format. --------------020806090202020900040704 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Andre Felipe Machado wrote: >Postgresql uses around 30% cpu and hard disk heavily (not so as vacuum) >at all executions. >Firebird uses around 40% cpu and hard disk heavily at the first >execution. >The second execution uses around 60% cpu and **NO** disk activity. > >The previously cited query running at 26 miliseconds down from 10 >minutes, can achieve this performance at the second run, with **NO** >disk activity. >At the first run it uses 1,7 seconds, down from 10 minutes. > >The hard disk is clearly a bottleneck. >1,7 seconds against 26 miliseconds. > > >So, >How "convince" postgresql to use windows disk cache or to read all >indexes to ram? >It seems that effective_cache_size does not tell postgresql to actually >use windows disk cache. >What parameter must be configured? >Do you have some suggestions? > > Assuming these are selects and that you have already vacuumed, etc. Look at memory useage. It seems likely that you have a difference in caching behavior. PostgreSQL has its own cache, and failing that will use the OS disk cache. So there may be a number of possible issues involved including whether the data is staying in the OS cache, how much memory is being used for caching, etc. It is also likely that the Windows version of PostgreSQL may have some issues in these areas that the UNIX/Linux versions may not simply because it is more immature. You might even try a vacuum full to retrieve space. This may mean smaller tables, more likely to remain in disk cache, etc. But that would not provide any indication of scalability. Best Wishes, Chris Travers Metatron Technology Consulting --------------020806090202020900040704 Content-Type: text/x-vcard; charset=utf-8; name="chris.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="chris.vcf" begin:vcard fn:Chris Travers n:Travers;Chris email;internet:chris@metatrontech.com tel;work:509-888-0220 tel;cell:509-630-9974 x-mozilla-html:FALSE version:2.1 end:vcard --------------020806090202020900040704--