RE: [GENERAL] memory

John,

Overrunning shared memory normally causes a different error, but this may be
worth mentioning. I noticed that you have set the number of buffers to 884.
Buffers are 8192 bytes each, so you would need shared memory to be
configured for at least 7,241,728 bytes. I am running on Solaris 2.6 and I
believe the default max size for shared memory is only about 512K. To allow
for 884 buffers, you would need to increase SHMMAX (in /etc/system) and
reboot the system.

Phil Culberson

-----Original Message-----
From: John Henderson [mailto:jrh(at)is(dot)com(dot)fj]
Sent: Monday, November 29, 1999 4:01 PM
To: pgsql-general(at)hub(dot)org
Subject: [GENERAL] memory

Thanks to Ken Gunderson for his recommendation to try the FreeBSD config
options SYSVSHM, SYSVSEM AND SESVMSG. I did and they had no effect on
BSD/OS3.0
Here is the question again...
Hi,
I could really use some help understanding where exactly the limits are in
my use of memory and how postgres uses memory.
I am running PostgreSQL 6.4 on BSDI 3.0 with 64M ram and 262M virtmem.
table sessions is 74M and 371K records

isfiji=> explain select user_name from sessions;
NOTICE: QUERY PLAN:
Seq Scan on sessions (cost=21330.73 size=371719 width=12)
EXPLAIN

This query (above) works without a hitch from psql

isfiji=> explain select * from sessions;
NOTICE: QUERY PLAN:
Seq Scan on sessions (cost=21330.73 size=371719 width=138)
EXPLAIN

The query above can access over 250M of memory according to top but dies
with either a seg fault or the latest, something called
"calloc: Cannot allocate memory"

I have had to set datasize to 256M which seems to be unrealistic. This table
is only 74M, it has a 15M index which the explain does not think is used.
Even if the entire table has to be sucked into mem and then duplicated
elsewhere in mem as a result this only accounts for 148M. Besides, is it
reaonable to require more than 256M of ram to copy a table from disk to
psql?

Having built this mass of data I don't seem to be able to do any useful
queries with it. Actually, just having problems with the query I want which
is
select user_name,sess_time,start, stop
from sessions
where date_part('epoch',start) between '$t1' and '$t2';

By the way, the following query from a PHP script works great, and because
it uses an index, very fast.
select date_trunc('minutes',sum(sess_time)) from
sessions where
user_name='$FORM{username}' and date_part('epoch',start)>'$t1' and
date_part('epoch',start)<'$t2'

The latter query is asking for 1 out of 3000 summaries approx.

Here are the questions...

1) Can someone explain how postgreSQL uses memory so that I can understand
what I should be doing here.
BTW, I am running postgres with -B 884. Can someone also explain how
postgres uses shared mem so that I can have a clue what would be a
reasonable setting.

2) Can any BSDI folk give me any tuning tips. I am especially interested to
hear from those who claim "some might tell you that we run equally well on
FreeBSD" or "BSD is the One True Code", of course all help is gratefully
received.

Thanks,
John Henderson

************