Magnus Hagander wrote:
>Trevor Talbot wrote:
>> On 10/20/07, Rainer Bauer <usenet@munnin.com> wrote:
>>
>>> Anyway, the problem are the no. of semaphores created by Postgres:
>>> Every backend creates at least 4*<max_connections> semaphores. Just
>>> increase <max_connections> to an unusual high value (say 10000) and
>>> start creating new connections while monitoring the handle count.
>>
>> Hmm, they're actually the same semaphores, so the only cost is for
>> slots in each process's handle table, which comes from kernel paged
>> pool. Testing shows I can easily create about 30 million handles to a
>> given object on this machine. This is under win2003 with 1.25GB RAM,
>> which gives it a paged pool limit of 352MB.
On my system I can only create about 4 millions semaphores.
>> I tried going up to 20000 max_connections, and still blew postmaster's
>> VM space long before paged pool was exhausted. I couldn't test any
>> higher values, as there's some interaction between max_connections and
>> shared_buffers that prevents it from mapping the buffer contiguously.
>>
>> Something's missing though, since I'm not hitting the same issue you
>> are. How are you generating the connections? I just have an app
>> calling PQconnectdb() in a loop, but I guess that's not good enough.
I am using the ASCII version of the psqlODBC driver version 8.2.4.2 to
establish the test connections.
>Yeah, something is obviously missing.. Are you guys on the exactly the
>same Windows versions? WRT both version and servivepack. Anybody on x64
>windows?
No, I am using WinXP SP2 32 bit with 2GB RAM.
These are my altered settings from the default 8.2.5 Postgres installation:
ssl = on
shared_buffers = 512MB
work_mem = 16MB
maintenance_work_mem = 256MB
wal_sync_method = fsync_writethrough
checkpoint_segments = 15
checkpoint_timeout = 30min
random_page_cost = 3.0
effective_cache_size = 1GB
autovacuum_vacuum_scale_factor = 0.10
autovacuum_analyze_scale_factor = 0.05
>Another thing worth testing - check if the amount of shared memory used
>makes a noticable difference. Try both very small and very large values.
Well I tried different shared_buffers settings, but the result was consisting:
with max_connections set to 10000, I can create 150 database connections.
However, I checked the handle count at the moment the last connection fails
and it is only at 1,5 million. So it seems the handles are not the primary
problem.
Let me know if you want any other tests performed on this machine. I also have
VS2005 installed, but until now I haven't compiled Postgres here (I was
waiting for 8.3 which fully supports building with VS).
Rainer
