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@xxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
