From: Donald Courtney <Donald.Courtney@Sun.COM>
To: pgsql-performance@postgresql.org
Cc: Frank Wiles <frank@wiles.org>
Cc: gokulnathbabu manoharan <gokulnathbabu@yahoo.com>
Subject: Re: Caching by Postgres
Date: Tue, 23 Aug 2005 14:41:39 -0400
Message-ID: <430B6DE3.6040109@sun.com> (raw)
In-Reply-To: <20050823124323.710a9dd3.frank@wiles.org>
References: <20050823171045.12112.qmail@web51309.mail.yahoo.com>
<20050823124323.710a9dd3.frank@wiles.org>
I mean well with this comment -
This whole issue of data caching is a troubling issue with postreSQL
in that even if you ran postgreSQL on a 64 bit address space
with larger number of CPUs you won't see much of a scale up
and possibly even a drop. I am not alone in having the *expectation*
that a database should have some cache size parameter and
the option to skip the file system. If I use oracle, sybase, mysql
and maxdb they all have the ability to size a data cache and move
to 64 bits.
Is this a crazy idea - that a project be started to get this adopted?
Is it
too big and structural to contemplate?
From one who likes postgreSQL
dc
Frank Wiles wrote:
>On Tue, 23 Aug 2005 10:10:45 -0700 (PDT)
>gokulnathbabu manoharan <gokulnathbabu@yahoo.com> wrote:
>
>
>
>>Hi all,
>>
>>I like to know the caching policies of Postgresql.
>>What parameter in the postgresql.conf affects the
>>cache size used by the Postgresql? As far as I have
>>searched my knowledge of the parameters are
>>
>>1. shared_buffers - Sets the limit on the amount of
>>shared memory used. If I take this is as the cache
>>size then my performance should increase with the
>>increase in the size of shared_buffers. But it seems
>>it is not the case and my performance actually
>>decreases with the increase in the shared_buffers. I
>>have a RAM size of 32 GB. The table which I use more
>>frequently has around 68 million rows. Can I cache
>>this entire table in RAM?
>>
>>
>
> increasing shared_buffers to a point helps, but after
> a certain threshold it can actually degree performance.
>
>
>
>>2. work_mem - It is the amount of memory used by an
>>operation. My guess is once the operation is complete
>>this is freed and hence has nothing to do with the
>>caching.
>>
>>
>
> This is the amount of memory used for things like sorts and
> order bys on a per backend process basis.
>
>
>
>>3. effective_cache_size - The parameter used by the
>>query planner and has nothing to do with the actual
>>caching.
>>
>>
>
> The instructs the query planner on how large the operating
> system's disk cache is. There isn't a built in cache, PostgreSQL
> relies on the operating system to cache the on disk information
> based on how often it is used. In most cases this is probably
> more accurate anyway.
>
> I wrote an article on PostgreSQL performance tuning that has
> links to several other related sites, you can find it here:
>
> http://www.revsys.com/writings/postgresql-performance.html
>
> ---------------------------------
> Frank Wiles <frank@wiles.org>
> http://www.wiles.org
> ---------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
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: Donald.Courtney@Sun.COM, frank@wiles.org, gokulnathbabu@yahoo.com
Subject: Re: Caching by Postgres
In-Reply-To: <430B6DE3.6040109@sun.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