Re: Query caching

Frank Joerdens wrote:
>
> On Wed, Nov 01, 2000 at 10:16:58AM +0000, Poul L. Christiansen wrote:
> > PostgreSQL hits the disk on UPDATE/DELETE/INSERT operations. SELECT's
> > are cached, but the default cache is only ½MB of RAM. You can change
> > this to whatever you want.
>
> That sound like a very cool thing to do, and the default seems awfully
> conservative, given the average server´s RAM equipment nowadays. If you
> have a small Linux server with 128 MB of RAM, it would be interesting to
> see what happens, performance-wise, if you increase the cache for
> selects to, for instance, 64 MB. Has anyone tried to benchmark this? How
> would you benchmark it? Where do you change this cache size? How do you
> keep the cache from being swapped out to disk (which would presumably
> all but eradicate the benefits of such a measure)?

I have a PostgreSQL server with 80MB of RAM running Redhat Linux 7.0 and
in my /etc/rc.d/init.d/postgresql start script I have these 2 lines that
start the postmaster.

echo 67108864 > /proc/sys/kernel/shmmax

su -l postgres -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o
'-i -B 4096 -o -F' start >/dev/null 2>&1" < /dev/null

The first line increases the maxium shared memory to 64MB.
The "-B 4096" indicates 4096 * 8kb = 32MB to each postmaster.

I haven't benchmarked it, but I know it's MUCH faster.

Poul L. Christiansen