Per-session memory footprint (9.0/windows)
Folks,
I run a PG (currently 8.4, but will shortly migrate to 9.0) database on
Windows Server 2003 that supports a desktop application which opens a
few long-running sessions per user. This is due to the Hibernate
persistence layer and the "one session per view" pattern that is
recommended for such applications.
These sessions usually load a pile of data once to display to the user,
and then occasionally query updates of this data or even fetch single
rows over a long time (like a few hours).
It seems that each of the server postmaster.exe processes takes up
approx. 5 MB of server memory (the "virtual memory size" column in task
manager), and I guess this truly is the private memory these processes
require. This number is roughly the same for 8.4 and 9.0 .
As there are many, many such server processes running, is there anything
I can do to reduce/optimize the per-session memory footprint?
I'm aware of the sort_mem etc. parameters
(http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ) but
these seem to only apply to the execution of queries, not to sessions
that mainly "sit around waiting", right?
Thank you for any hints!
-hannes
On Wed, Mar 2, 2011 at 11:07 AM, Hannes Erven <hannes@erven.at> wrote:
Folks,
I run a PG (currently 8.4, but will shortly migrate to 9.0) database on
Windows Server 2003 that supports a desktop application which opens a
few long-running sessions per user. This is due to the Hibernate
persistence layer and the "one session per view" pattern that is
recommended for such applications.
These sessions usually load a pile of data once to display to the user,
and then occasionally query updates of this data or even fetch single
rows over a long time (like a few hours).It seems that each of the server postmaster.exe processes takes up
approx. 5 MB of server memory (the "virtual memory size" column in task
manager), and I guess this truly is the private memory these processes
require. This number is roughly the same for 8.4 and 9.0 .
Task manager is mis-leading as multiple processes are sharing memory. You
need process explorer
http://technet.microsoft.com/en-us/sysinternals/bb896653 (or something like
it) to see real memory consumption per backend. Adding up the columns in
task manager is wrong and most definitely scary if you believe it :-)
--Scott
Show quoted text
As there are many, many such server processes running, is there anything
I can do to reduce/optimize the per-session memory footprint?I'm aware of the sort_mem etc. parameters
(http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ) but
these seem to only apply to the execution of queries, not to sessions
that mainly "sit around waiting", right?Thank you for any hints!
-hannes
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Scott,
It seems that each of the server postmaster.exe processes takes up
approx. 5 MB of server memory (the "virtual memory size" column in task
manager), and I guess this truly is the private memory these processes
require. This number is roughly the same for 8.4 and 9.0 .Task manager is mis-leading as multiple processes are sharing memory.
You need process explorer
That's exactly why I did not use the "default" columns of the Task
Manager, but "virtual memory size". I now compared the numbers to the
"private memory" column of Process Explorer, and Process Explorer shows
about 800k even more usage the the Task Manager.
It is still about 5 MB of private memory per "idle" backend process. Is
there anything I can do to optimize?
Thanks again,
-hannes
Hannes Erven <hannes@erven.at> writes:
It is still about 5 MB of private memory per "idle" backend process. Is
there anything I can do to optimize?
That sounds about the right ballpark for a working backend process with
caches loaded up. If that's too much for you, you ought to be using
connection pooling.
regards, tom lane