Hi,

I'm using Postgresql 8.1 on windows2000 and I have a hard time
understanding how to limit the memory of the sessions to 1 MB.

What I have right now is that each connection (opened with jdbc) takes
about 3MB (some take a little more, some a little less). I think that
this is a waste of memory (am-I wrong?).


I've read the doc
(http://www.postgresql.org/docs/8.1/s...-resource.html)
for work_mem and temp_buffers and they have this value:

work_mem = 1024
temp_buffers = 100

What am I missing to limit the memory taken by session to 1MB?

In addition I'd like to understand better temp_buffers. I never create
temporary tables but I wonder if postgresql does it behind the scene
when I do some big selects.

__________________________________________________ _______________
Del dine store filer uden problemer på MSN Messenger:
http://messenger.msn.dk/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

James Im wrote:
> Hi,
>
> I'm using Postgresql 8.1 on windows2000 and I have a hard time
> understanding how to limit the memory of the sessions to 1 MB.

Well, you don't explicitly, but see below.

> What I have right now is that each connection (opened with jdbc) takes
> about 3MB (some take a little more, some a little less). I think that
> this is a waste of memory (am-I wrong?).

Depends what you mean by "waste". If you never use those connections,
then yes. Otherwise, each connection does need a little space to work in
you know.

> I've read the doc
> (http://www.postgresql.org/docs/8.1/s...-resource.html)
> for work_mem and temp_buffers and they have this value:
>
> work_mem = 1024
> temp_buffers = 100
>
> What am I missing to limit the memory taken by session to 1MB?

You can't. In particular, work_mem is memory *per sort* so can be
several times that. If you're trying to get PG to run in 64MB or
something like that, I think you're going to be disappointed. In
particular, large result sets will tend to need more than 1MB at either
the server or client end of the connection.

Is there a particular problem you're trying to solve?

> In addition I'd like to understand better temp_buffers. I never create
> temporary tables but I wonder if postgresql does it behind the scene
> when I do some big selects.

Various operations will spill to disk in a similar manner to a temporary
table, but they're not tables per-se.

--
Richard Huxton
Archonet Ltd

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

Richard Huxton <dev@archonet.com> writes:
> James Im wrote:
>> What am I missing to limit the memory taken by session to 1MB?

> You can't. In particular, work_mem is memory *per sort* so can be
> several times that. If you're trying to get PG to run in 64MB or
> something like that, I think you're going to be disappointed.

Yeah. I think the working RAM per backend is approaching a megabyte
these days just for behind-the-scenes overhead (catalog caches and
so forth), before you expend even one byte on per-query structures
that work_mem would affect.

Something else to consider: I dunno what tool you were using on Windows
to look at memory usage or how it counts shared memory, but on Unix a
lot of process-monitoring tools tend to count shared memory against each
process touching that shared memory. Which leads to artificially
bloated numbers. The default PG shared memory block size these days is
order-of-10-megabytes I think; if a backend has touched any significant
fraction of that since it started, that could dwarf the backend's true
private workspace size.

If you're concerned about total memory footprint for a pile of backends,
usually the right answer is to put some connection-pooling software in
front of them, not try to hobble each backend to work in a tiny amount
of space.

regards, tom lane

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

On Wed, 2007-01-17 at 11:02 +0000, James Im wrote:
> Hi,
>
> I'm using Postgresql 8.1 on windows2000 and I have a hard time
> understanding how to limit the memory of the sessions to 1 MB.
>
> What I have right now is that each connection (opened with jdbc) takes
> about 3MB (some take a little more, some a little less). I think that
> this is a waste of memory (am-I wrong?).

How are you measuring the memory usage? What is your shared_buffers
setting?

The shared_buffers are only allocated once for all processes, but
because there's no way to tell which process is using the memory
(because it's shared), "top" reports that each process is using the
entire shared buffers, plus all it's private memory.

So, when a new connection is established, it might not actually be using
3MB more than before.

Regards,
Jeff Davis


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