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

Magnus Hagander schrieb:

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

No, 4 millions distinct semaphores by calling:
CreateSemaphore( NULL, 0, 1, NULL );

>> >> 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.
>
>Followup, when running these tests, could you check using Process Explorer
>if you're hitting close to the limit of either of the two pools? See
>http://blogs.technet.com/askperf/archive/2007/03/07/memory-management-understanding-pool-resources.aspx

Well after installing Postgres explorer and starting the system information
program the kernel memory section shows me the current count, but not the
limits (it says "no symbols"). I am currently downloading the "Debugging Tools
for Windows". Maybe these limits are shown after the installation.

I just repeated the test with a local connection. After 150 connections, the
following values are displayed:
Paged physical    113000
Paged virtual   120000
Nonpaged     28000

Also there are 1.583.182 handles open.

I will check the behaviour with a remote connection later (have to go now...).

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

I followed Josh's advice here:
<http://archives.postgresql.org/pgsql-performance/2007-06/msg00606.php>

What value would you recommend then? The default 32MB?

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

No.

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

A few tests indicated, that the maximum no. of connections is 150, regardless
of the <max_connections> settings. But I will have to check whether this is
somehow caused by the ODBC driver.

Rainer