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