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