Thread: 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

> 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.


I have run BSDI for years and never saw this problem.  Not sure on a
cause, though.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> 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'm not sure, but my guess is: the BE needs to allocate data for over 74
megs (your table data), probably more due to formatting and page alignment
losses. Then it's sending it over either a TCP/IP connection or a UNIX
domain socket, probably another 74 megs to be allocated somewhere in
memory(?) and finally it gets to psql which needs to get yet another 74
megs.

Does this make any sense to the experts?

Maarten

--

Maarten Boekhold, maarten.boekhold@tibcofinance.com
TIBCO Finance Technology Inc.
"Sevilla" Building
Entrada 308
1096 ED Amsterdam, The Netherlands
tel: +31 20 6601000 (direct: +31 20 6601066)
fax: +31 20 6601005
http://www.tibcofinance.com