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.
Advertising
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
