From: Chris Travers <chris@metatrontech.com>
To: Andre Felipe Machado <andremachado@techforce.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
Date: Fri, 10 Mar 2006 22:14:15 -0800
Message-ID: <44126AB7.6060709@metatrontech.com> (raw)
In-Reply-To: <1142041197.7572.6.camel@localhost.localdomain>
References: <20060307162842.M37264@techforce.com.br>
<1142041197.7572.6.camel@localhost.localdomain>
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
Attachments:chris.vcf (text/x-vcard, 169B) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chris@metatrontech.com, andremachado@techforce.com.br
Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
In-Reply-To: <44126AB7.6060709@metatrontech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox