Re: measuring shared memory usage on Windows

Performance readers ....

I follow up my promise:

> I try my theories :)) and contrary to all wisdom from all PostgreSQL
> tuning recommendations reconfigured shared memory nearly to the
> minimum: 1000 for maximum of 400 concurrent connections. (800 would be
> minimum). Single user performance was fine, now I am looking forward
> to full user scenario tomorrow.
>
> I will keep you posted.
>
> Harald

I went even further down,

max_connections = 200 #
shared_buffers = 400 # min 16 or max_connections*2, 8KB each

to the minimum allowed value of shared_buffers. And the response times are
better then ever before (with 10.000, 20.000, 5.000 and 40.000shared_buffers):

An application-level response dropped from 16 / 9.5 seconds (first run /
cached run) to 12 / 6.5 average runtime. That response time is mainly
dependend on SQL performance, and esp. the drop is caused by this change.

Moreover, the columns "swapped out memory" in task manager stay low at ~26k
per postgres.exe, compared to ~106k as my shared_buffers where at 10.000.

The "memory" column of postgres.exe in task manager process still grows up
to >10.000K, while now "virtual memory" stays ~3.600k per process

So: in this configuration / workload:
-> windows 2k3
-> small (~0,4GB) Database
-> rather complex queries
-> ~1 GB memory
-> running in virtual machine

and
-> windows xp
-> small (~0,4GB) Database
-> ~0,5 GB memory
-> rather complex queries
-> running native (laptops)

I could verify a substantial speed gain in single and many user situations
by lowering shared_buffers to the allowed minimum.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.