Re: 8.2.3: Server crashes on Windows using Eclipse/Junit - Mailing list pgsql-general

On Sun, Oct 21, 2007 at 09:43:27PM +0200, Rainer Bauer wrote:
> 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.

Is that 4 million semaphores, or 4 million handles to a smaller number of
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.

Could you try the same tests with the client runnint on a different system?
Since the client eats up a bunch of handles and such as well, and that
would eliminate the difference due to different clients.


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

Ok. So one is on XP and one is on 2003. That' interesting - given that 2003
is tuned towards servers, it doesn't surprise me that it allows more
clients before breaking.


> These are my altered settings from the default 8.2.5 Postgres installation:
> ssl = on

Does it make a difference if you turn this off?

> shared_buffers = 512MB

As a general note, thsi is *way* too high. All evidence I've seen points to
that you should have shared_buffers as *small* as possible on win32,
because memory access there is slow. And leave more of the caching up to
the OS.

> 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

None of those should make a difference on this.

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

Ok. But if you decrease max_connections, you can have more connections? Or
the other way around?


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

Good, it shouldn't be, but it's good to have that confirmed.

/Magnus