Re: 8.2.3: Server crashes on Windows using Eclipse/Junit
Hi,
Sorry for top-posting but since I am answering questions that don't all
appear in this message:- I installed the default download of Postgres. I didn't compile myself,
so it's probably the mingw version
It is.
- Max_connections is set to 500. I did that originally because I kept
seeing a message about no connection available and I thought it was
because I was not allocating enough connections. My machine has 2GB of RAM.
There's your problem. 500 is way above what the windows version can handle. IIRC the hard max is somewhere around 200 depending on some OS factors that we don't entirely know. I'd never recommend going above 100-150. With no more than 2Gb ram, not above 100.
You'll ned to figure out what's eating all your connections - it sounds like it's not entirely expected. Perhaps conections are leaked somewhere?
- How do I determine what DLL is failing and what is causing it to fail in
its initialization routine?
You really can't in this case, but if you could it wouldn't help you. It's windows running out of global resources.
/Magnus
"Magnus Hagander" <magnus@hagander.net> writes:
- Max_connections is set to 500.
There's your problem. 500 is way above what the windows version can
handle. IIRC the hard max is somewhere around 200 depending on some OS
factors that we don't entirely know.
Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
to something we know the platform can stand? It'd be more comfortable
if we understood exactly where the limit was, but I think I'd rather
have an "I'm sorry Dave, I can't do that" than random-seeming crashes.
regards, tom lane
On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote:
"Magnus Hagander" <magnus@hagander.net> writes:
- Max_connections is set to 500.
There's your problem. 500 is way above what the windows version can
handle. IIRC the hard max is somewhere around 200 depending on some OS
factors that we don't entirely know.Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
to something we know the platform can stand? It'd be more comfortable
if we understood exactly where the limit was, but I think I'd rather
have an "I'm sorry Dave, I can't do that" than random-seeming crashes.
Yeayh, that's probably a good idea - except we never managed to figure out
where the limit is. It appears to vary pretty wildly between different
machines, for reasons we don't really know why (total RAM has some effect
on it, but that's not the only one, for example)
//Magnus
On Wed, 17 Oct 2007 09:22:11 +0200
Magnus Hagander <magnus@hagander.net> wrote:
Maybe we should put an #ifdef WIN32 into guc.c to limit
max_connections to something we know the platform can stand? It'd
be more comfortable if we understood exactly where the limit was,
but I think I'd rather have an "I'm sorry Dave, I can't do that"
than random-seeming crashes.Yeayh, that's probably a good idea - except we never managed to
figure out where the limit is. It appears to vary pretty wildly
between different machines, for reasons we don't really know why
(total RAM has some effect on it, but that's not the only one, for
example)
How about we just emit a warning..
WARNING: Connections above 250 on Windows platforms may have
unpredictable results.
Joshua D. Drake
//Magnus
---------------------------(end of
broadcast)--------------------------- TIP 1: if posting/reading
through Usenet, please send an appropriate subscribe-nomail command
to majordomo@postgresql.org so that your message can get through to
the mailing list cleanly
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Magnus Hagander" wrote:
- Max_connections is set to 500. I did that originally because I kept
seeing a message about no connection available and I thought it was
because I was not allocating enough connections. My machine has 2GB of RAM.There's your problem. 500 is way above what the windows version can handle. IIRC the hard max is somewhere around 200 depending on some OS factors that we don't entirely know. I'd never recommend going above 100-150. With no more than 2Gb ram, not above 100.
My guess is that Windows is running out of handles. Each backend uses about
150 handles. 100 Backends means 15000 handles. Depending how many other
programs are currently running the no. of startable backends will vary
depending on the total handle limit Windows imposes.
Rainer
On 10/17/07, Magnus Hagander <magnus@hagander.net> wrote:
On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote:
Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
to something we know the platform can stand? It'd be more comfortable
if we understood exactly where the limit was, but I think I'd rather
have an "I'm sorry Dave, I can't do that" than random-seeming crashes.Yeayh, that's probably a good idea - except we never managed to figure out
where the limit is. It appears to vary pretty wildly between different
machines, for reasons we don't really know why (total RAM has some effect
on it, but that's not the only one, for example)
I tried generating idle connections in an effort to reproduce
Laurent's problem, but I ran into a local limit instead: for each
backend, postmaster creates a thread and burns 4MB of its 2GB address
space. It fails around 490.
Laurent's issue must depend on other load characteristics. It's
possible to get a trace of DLL loads, but I haven't found a
noninvasive way of doing that. It seems to require a debugger be
attached.
On 10/20/07, Rainer Bauer <usenet@munnin.com> wrote:
"Magnus Hagander" wrote:
- Max_connections is set to 500. I did that originally because I kept
seeing a message about no connection available and I thought it was
because I was not allocating enough connections. My machine has 2GB of RAM.There's your problem. 500 is way above what the windows version can handle. IIRC the hard max is somewhere around 200 depending on some OS factors that we don't entirely know. I'd never recommend going above 100-150. With no more than 2Gb ram, not above 100.
My guess is that Windows is running out of handles. Each backend uses about
150 handles. 100 Backends means 15000 handles. Depending how many other
programs are currently running the no. of startable backends will vary
depending on the total handle limit Windows imposes.
Those are kernel object handles; the ceiling does depend on available
kernel memory, but they're cheap, and postgres is in no danger of
running into that limit. Most of the handle limits people talk about
are on USER (window etc) objects, which come from a single shared
pool.
--- "Joshua D. Drake" <jd@commandprompt.com> wrote:How about we just emit a warning..
WARNING: Connections above 250 on Windows platforms may have
unpredictable results.Joshua D. Drake
I'd personally vote for a lower warning limit like 175 as I can
consistently crash Postgresql on Windows system right around the 200th
connection.
Regards,
Shelby Cain
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On 10/20/07, Shelby Cain <alyandon@yahoo.com> wrote:
I'd personally vote for a lower warning limit like 175 as I can
consistently crash Postgresql on Windows system right around the 200th
connection.
What error gets logged for your crashes?
"Trevor Talbot" wrote:
On 10/20/07, Rainer Bauer <usenet@munnin.com> wrote:
"Magnus Hagander" wrote:
- Max_connections is set to 500. I did that originally because I kept
seeing a message about no connection available and I thought it was
because I was not allocating enough connections. My machine has 2GB of RAM.There's your problem. 500 is way above what the windows version can handle. IIRC the hard max is somewhere around 200 depending on some OS factors that we don't entirely know. I'd never recommend going above 100-150. With no more than 2Gb ram, not above 100.
My guess is that Windows is running out of handles. Each backend uses about
150 handles. 100 Backends means 15000 handles. Depending how many other
programs are currently running the no. of startable backends will vary
depending on the total handle limit Windows imposes.Those are kernel object handles; the ceiling does depend on available
kernel memory, but they're cheap, and postgres is in no danger of
running into that limit. Most of the handle limits people talk about
are on USER (window etc) objects, which come from a single shared
pool.
You are right. I just did a quick test and depending on the handle type these
limits are quite high. I could create 5 millions events or 4 millions
semaphores or 3,5 millions mutexes before the system returned error 1816
ERROR_NOT_ENOUGH_QUOTA "Not enough quota is available to process this
command.".
Rainer
I wrote:
You are right. I just did a quick test and depending on the handle type these
limits are quite high. I could create 5 millions events or 4 millions
semaphores or 3,5 millions mutexes before the system returned error 1816
ERROR_NOT_ENOUGH_QUOTA "Not enough quota is available to process this
command.".
[Does some further testing] The limit is high, but nonetheless Postgres is
running out of handles. Setting <max_connections> to 10000 and starting
postgres _without_ any connection consumes 40000 handles. This correspodends
to the 4 Postgres processes running after the server was started. Every new
connection consumes another 10000 handles.
I don't know the Postgres code involved, but it seems that every backend
consumes at least <max_connections> handles. Hence increasing this value will
have the opposite effect once a certain threshold is met.
Rainer
--- Trevor Talbot <quension@gmail.com> wrote:On 10/20/07, Shelby Cain <alyandon@yahoo.com> wrote:
I'd personally vote for a lower warning limit like 175 as I can
consistently crash Postgresql on Windows system right around the200th
connection.
What error gets logged for your crashes?
It's been a while but IIRC there wasn't anything in the logs other than
an entry noting that a backend had crashed unexpectedly so the
postmaster was restarting all active backends. I can trivially
reproduce it at work on my workstation if you need the exact error
text.
Regards,
Shelby Cain
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On 10/20/07, Shelby Cain <alyandon@yahoo.com> wrote:
--- Trevor Talbot <quension@gmail.com> wrote:On 10/20/07, Shelby Cain <alyandon@yahoo.com> wrote:
I'd personally vote for a lower warning limit like 175 as I can
consistently crash Postgresql on Windows system right around the200th
connection.
What error gets logged for your crashes?
It's been a while but IIRC there wasn't anything in the logs other than
an entry noting that a backend had crashed unexpectedly so the
postmaster was restarting all active backends. I can trivially
reproduce it at work on my workstation if you need the exact error
text.
I think it would be useful; if nothing else, maybe it'll tell us if
you can see the same problem Laruent does, or if it's a different
limit entirely.
Shelby Cain wrote:
--- Trevor Talbot <quension@gmail.com> wrote:On 10/20/07, Shelby Cain <alyandon@yahoo.com> wrote:
I'd personally vote for a lower warning limit like 175 as I can
consistently crash Postgresql on Windows system right around the200th
connection.
What error gets logged for your crashes?
It's been a while but IIRC there wasn't anything in the logs other than
an entry noting that a backend had crashed unexpectedly so the
postmaster was restarting all active backends. I can trivially
reproduce it at work on my workstation if you need the exact error
text.
I could reproduce this here:
Server closed the connection unexpectedly
This probaly means the server terminated abnormally before or while processing
the request
2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code
-1073741502
Shelby, are you using the /3GB switch by chance? This will half the no. of
available handles on your system.
Rainer
On 10/20/07, Rainer Bauer <usenet@munnin.com> wrote:
I could reproduce this here:
Server closed the connection unexpectedly
This probaly means the server terminated abnormally before or while processing
the request2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code
-1073741502
How?
Hello Trevor,
Sunday, October 21, 2007, 12:15:25 AM, you wrote:
TT> On 10/20/07, Rainer Bauer <usenet@munnin.com> wrote:
I could reproduce this here:
Server closed the connection unexpectedly
This probaly means the server terminated abnormally before or while processing
the request2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code
-1073741502
TT> How?
Seems like the mailiming list is not catching up fast enough (I am
posting through usenet)...
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.
Rainer
--- Rainer Bauer <usenet@munnin.com> wrote:I could reproduce this here:
Server closed the connection unexpectedly
This probaly means the server terminated abnormally before or while
processing
the request2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit
code
-1073741502Shelby, are you using the /3GB switch by chance? This will half the
no. of
available handles on your system.Rainer
Probably not although I haven't examined boot.ini. My workstation only
has 1.5 GB of ram so I'm highly doubtful that IBM would have configured
it to boot with the /3GB switch.
Regards,
Shelby Cain
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
I wrote
Anyway, the problem are the no. of semaphores created by Postgres:
Every backend creates at least 4*<max_connections> semaphores.
Sorry, this must read <max_connections> semaphores, not 4 times.
Rainer
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.
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.
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.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.
Yeah, something is obviously missing.. Are you guys on the exactly the
same Windows versions? WRT both version and servivepack. Anybody on x64
windows?
Another thing worth testing - check if the amount of shared memory used
makes a noticable difference. Try both very small and very large values.
I don't think the paged pool is the problem - I think it's the nonpaged
pool. Would be interesting to track that one in the failing case (using
performance monitor, up to the point where it fails). And the nonpaged
one is smaller... If that looks like it's the problem, it could be
helpful to do a pooltag trace on it (see for example
http://blogs.msdn.com/ntdebugging/archive/2006/12/18/Understanding-Pool-Consumption-and-Event-ID_3A00_--2020-or-2019.aspx)
//Magnus
Trevor Talbot wrote:
On 10/17/07, Magnus Hagander <magnus@hagander.net> wrote:
On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote:
Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
to something we know the platform can stand? It'd be more comfortable
if we understood exactly where the limit was, but I think I'd rather
have an "I'm sorry Dave, I can't do that" than random-seeming crashes.Yeayh, that's probably a good idea - except we never managed to figure out
where the limit is. It appears to vary pretty wildly between different
machines, for reasons we don't really know why (total RAM has some effect
on it, but that's not the only one, for example)I tried generating idle connections in an effort to reproduce
Laurent's problem, but I ran into a local limit instead: for each
backend, postmaster creates a thread and burns 4MB of its 2GB address
space. It fails around 490.
Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.
There are two ways to get around it - one is not using a thread for each
backend, but a single thread that handles them all and then some sync
objects around it. We originally considered this but said we won't
bother changing it because the current way is simpler, and the overhead
of a thread is tiny compared to a process. I don't think anybody even
thought about the fact that it'd run you out of address space...
The other way is to finish off win64 support :-) Which I plan to look
at, but I don't think that alone should be considered a solution.
The question is if it's worth fixing that part, if it will just fall
down for other reasons before we reach these 500 connections anyway. Can
you try having your program actually run some queries and so, and not
just do a PQconnect? To see if it falls over then, because it's been
doing more?
Laurent's issue must depend on other load characteristics. It's
possible to get a trace of DLL loads, but I haven't found a
noninvasive way of doing that. It seems to require a debugger be
attached.
AFAIK, it does require that, yes.
//Magnus
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
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
On Mon, Oct 22, 2007 at 10:23:16AM +0200, Magnus Hagander wrote:
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
//Magnus
On Mon, Oct 22, 2007 at 10:41:14AM +0200, Magnus Hagander wrote:
On Mon, Oct 22, 2007 at 10:23:16AM +0200, Magnus Hagander wrote:
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
Another followup. Been working with Dave on and off today (well, him mostly
on to be honest, me a bit more on and off), and it seems that both our
repros clearly blame the desktop heap, and nothing else. Please use the
desktop heap tool and see if it breaks when the desktop heap usage
approaches 100%:
It'd still be good to know why the difference is so big between your two
systems.
//Magnus
Magnus Hagander wrote:
Another followup. Been working with Dave on and off today (well, him mostly
on to be honest, me a bit more on and off), and it seems that both our
repros clearly blame the desktop heap, and nothing else. Please use the
desktop heap tool and see if it breaks when the desktop heap usage
approaches 100%:It'd still be good to know why the difference is so big between your two
systems.
Further info on this for the record - on XP Pro (which I'm testing on),
the desktop heap size defaults to 512KB for non-interactive sessions and
3072KB for interactive. In testing I find that I can get up to around 46
or so connections when running as a service before desktop heap is
exhausted and postgres dies.
When running interactively I can get a little over 125 connections
before things start dying, however, in that case it's *not* because of
dekstop heap, because I can start a second cluster and run 125
connections on each simultaneously.
If I run three instances up together, one of them will die as soon as
desktop heap gets to 100% usage.
So, we seem to be hitting two limits here - the desktop heap, and
something else which is cluster-specific. Investigation continues...
Regards, Dave
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 = onDoes 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 = onDoes 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
Dave Page wrote:
So, we seem to be hitting two limits here - the desktop heap, and
something else which is cluster-specific. Investigation continues...
I will make these tests tonight or tomorrow morning and will let you know.
Rainer
On 10/22/07, Rainer Bauer <usenet@munnin.com> wrote:
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.
After you install that, go to Options->Configure Symbols in Process
Explorer. Change it to use the dbghelp.dll installed in the Debugging
Tools directory, and configure the symbol path like this:
http://support.microsoft.com/kb/311503
The limits should show up after that.
On Mon, Oct 22, 2007 at 04:03:35PM +0200, Rainer Bauer wrote:
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?
That advice is good - for Unix platforms. For windows, yes, try with 32Mb.
//Magnus
Dave Page wrote:
So, we seem to be hitting two limits here - the desktop heap, and
something else which is cluster-specific. Investigation continues...
In further info, I've been testing this with the 8.3b1 release build
that we put out with pgInstaller, and a build with all optional
dependencies (OpenSSL, Kerberos, gettext, ldap etc) disabled. I'm seeing
pretty much the same results with each - roughtly 9.6KB of desktop heap
used per connection.
In addition, I've tried with standard pgbench runs, as well as a script
that just does 'select version()'. Again, no differences were observed.
Magnus and I did observe that we're using 1 user object and 4 GDI
objects per connection. If anyone happens to know how we might identify
those, please shout as so far we've drawn a blank :-(
Regards, Dave
On 10/21/07, Magnus Hagander <magnus@hagander.net> wrote:
I tried generating idle connections in an effort to reproduce
Laurent's problem, but I ran into a local limit instead: for each
backend, postmaster creates a thread and burns 4MB of its 2GB address
space. It fails around 490.Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.There are two ways to get around it - one is not using a thread for each
backend, but a single thread that handles them all and then some sync
objects around it. We originally considered this but said we won't
bother changing it because the current way is simpler, and the overhead
of a thread is tiny compared to a process. I don't think anybody even
thought about the fact that it'd run you out of address space...
I'd probably take the approach of combining win32_waitpid() and
threads. You'd end up with 1 thread per 64 backends; when something
interesting happens the thread could push the info onto a queue, which
the new win32_waitpid() would check. Use APCs to add new backends to
threads with free slots.
On 10/22/07, Dave Page <dpage@postgresql.org> wrote:
Dave Page wrote:
So, we seem to be hitting two limits here - the desktop heap, and
something else which is cluster-specific. Investigation continues...In further info, I've been testing this with the 8.3b1 release build
that we put out with pgInstaller, and a build with all optional
dependencies (OpenSSL, Kerberos, gettext, ldap etc) disabled. I'm seeing
pretty much the same results with each - roughtly 9.6KB of desktop heap
used per connection.
The question is where that's coming from. I wondered if it was
desktop heap originally, but there's no reason it should be using it,
and that seems to be precisely the difference between my system and
the others. Connections here are barely making a dent; at 490 there's
an entire 45KB committed in the service desktop.
Magnus and I did observe that we're using 1 user object and 4 GDI
objects per connection. If anyone happens to know how we might identify
those, please shout as so far we've drawn a blank :-(
Those appear to belong to the console window.
I've yet to do anything that generates real load (lightweight system),
but a simple "select version()" doesn't make any difference here
either, and raising shared buffers just makes postmaster run out of VM
space faster. (I don't think I mentioned that error before, but it
shows up as "FATAL: could not create sigchld waiter thread: error
code 8".)
On Mon, Oct 22, 2007 at 08:04:03AM -0700, Trevor Talbot wrote:
On 10/22/07, Dave Page <dpage@postgresql.org> wrote:
Dave Page wrote:
So, we seem to be hitting two limits here - the desktop heap, and
something else which is cluster-specific. Investigation continues...In further info, I've been testing this with the 8.3b1 release build
that we put out with pgInstaller, and a build with all optional
dependencies (OpenSSL, Kerberos, gettext, ldap etc) disabled. I'm seeing
pretty much the same results with each - roughtly 9.6KB of desktop heap
used per connection.The question is where that's coming from. I wondered if it was
desktop heap originally, but there's no reason it should be using it,
and that seems to be precisely the difference between my system and
the others. Connections here are barely making a dent; at 490 there's
an entire 45KB committed in the service desktop.
Yes, that would be very interesting to know. Because obviouslyi it's
something.
I read somewhere that Vista makes the size of the desktop heap dynamic, but
you were on 2003, right?
Are you running the server as a service or from the commandprompt?
Magnus and I did observe that we're using 1 user object and 4 GDI
objects per connection. If anyone happens to know how we might identify
those, please shout as so far we've drawn a blank :-(Those appear to belong to the console window.
Makes sense - a Windows, a system menu, etc. There's probably a "hidden
console window" when running as a service...
I've yet to do anything that generates real load (lightweight system),
but a simple "select version()" doesn't make any difference here
either, and raising shared buffers just makes postmaster run out of VM
space faster. (I don't think I mentioned that error before, but it
shows up as "FATAL: could not create sigchld waiter thread: error
code 8".)
Yeah, that makes sense. We need to fix that, but I think that's too big of
a change to push during beta, given how few reports we've had of people
running into it.
//Magnus
On 10/22/07, Magnus Hagander <magnus@hagander.net> wrote:
I read somewhere that Vista makes the size of the desktop heap dynamic, but
you were on 2003, right?
Yeah, 32bit 2003 SP2, which has the same limits as XP. It looks like
Vista also has the same limits on actual heap sizes, but manages
kernel address space dynamically, so it doesn't get stuck with
arbitrary limits there. I don't have a Vista machine to verify
though.
Are you running the server as a service or from the commandprompt?
Service, I've been using the standard MSI install of 8.2.5.
Magnus and I did observe that we're using 1 user object and 4 GDI
objects per connection. If anyone happens to know how we might identify
those, please shout as so far we've drawn a blank :-(Those appear to belong to the console window.
Makes sense - a Windows, a system menu, etc. There's probably a "hidden
console window" when running as a service...
Well, the only thing actually running as a service is pg_ctl; the
other processes just belong to the same desktop. They're all console
executables, so they get the usual objects, but they're not visible
anywhere.
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with.
Trevor Talbot wrote:
The question is where that's coming from. I wondered if it was
desktop heap originally, but there's no reason it should be using it,
and that seems to be precisely the difference between my system and
the others. Connections here are barely making a dent; at 490 there's
an entire 45KB committed in the service desktop.
Hmm, Greg mentioned to me earlier that he was suspicious of SSPI which
seems to drag in dependencies on gdi32.dll and user32.dll via
secur32.dll. Sure enough, testing with 8.2.5 on XP Pro, I get to 150
connections running as a service having used 97.2 of desktop heap (vs.
45 connections max with 8.3).
So we have a pretty serious regression in 8.3.
Of course, that still doesn't tally up with what you're seeing on
Win2k3. I'll test on there tomorrow.
Regards, Dave
I wrote:
[ desktop heap usage ]
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with.
...yep, under XP I'm using about 3.1KB of the service heap per
connection, which tears through it quite a bit faster. Now to figure
out exactly where it's coming from...
Trevor Talbot wrote:
I wrote:
[ desktop heap usage ]
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with....yep, under XP I'm using about 3.1KB of the service heap per
connection, which tears through it quite a bit faster. Now to figure
out exactly where it's coming from...
That ties up with what I'm seeing - on 8.3 it's about 9.6KB per
connection, and I get a little under a third as many connections as 8.2
before it dies.
/D
Dave Page wrote:
Trevor Talbot wrote:
The question is where that's coming from. I wondered if it was
desktop heap originally, but there's no reason it should be using it,
and that seems to be precisely the difference between my system and
the others. Connections here are barely making a dent; at 490 there's
an entire 45KB committed in the service desktop.Hmm, Greg mentioned to me earlier that he was suspicious of SSPI which
seems to drag in dependencies on gdi32.dll and user32.dll via
secur32.dll. Sure enough, testing with 8.2.5 on XP Pro, I get to 150
connections running as a service having used 97.2 of desktop heap (vs.
45 connections max with 8.3).So we have a pretty serious regression in 8.3.
Of course, that still doesn't tally up with what you're seeing on
Win2k3. I'll test on there tomorrow.
Could you try a build without SSPI? It should be as simple as removing
the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to
touch the linker lines at all, actually, so try without first.
//Magnus
* Magnus Hagander:
Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.
Windows XP supports the STACK_SIZE_PARAM_IS_A_RESERVATION flag, which
apparently allows to reduce the reserved size. It might be better to do
this the other way round, though (leave the reservation at its 1 MB
default, and increase it only when necessary).
Florian Weimer wrote:
* Magnus Hagander:
Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.Windows XP supports the STACK_SIZE_PARAM_IS_A_RESERVATION flag, which
apparently allows to reduce the reserved size. It might be better to do
this the other way round, though (leave the reservation at its 1 MB
default, and increase it only when necessary).
It does, but we still support windows 2000 as well. I think it's better
to use a different method altogether - one not using one thread per child.
//Magnus
Trevor Talbot wrote:
On 10/21/07, Magnus Hagander <magnus@hagander.net> wrote:
I tried generating idle connections in an effort to reproduce
Laurent's problem, but I ran into a local limit instead: for each
backend, postmaster creates a thread and burns 4MB of its 2GB address
space. It fails around 490.Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.There are two ways to get around it - one is not using a thread for each
backend, but a single thread that handles them all and then some sync
objects around it. We originally considered this but said we won't
bother changing it because the current way is simpler, and the overhead
of a thread is tiny compared to a process. I don't think anybody even
thought about the fact that it'd run you out of address space...I'd probably take the approach of combining win32_waitpid() and
threads. You'd end up with 1 thread per 64 backends; when something
interesting happens the thread could push the info onto a queue, which
the new win32_waitpid() would check. Use APCs to add new backends to
threads with free slots.
I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Should be rather trivial to do.
Keeps win32_waitpid() unchanged.
That said, refactoring win32_waitpid() to be based on a queue might be a
good idea *anyway*. Have the callback from above put something in the
queue, and go with your idea for the rest.
//Magnus
Magnus Hagander wrote:
Could you try a build without SSPI? It should be as simple as removing
the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to
touch the linker lines at all, actually, so try without first.
Nope, doesn't help - still using around 9.7KB per connection. Just to be
sure I did remove the link option, and checking with depends see that
there are now only delay load references to secur32.dll, nothing direct
- as is the case with 8.2
So the only other changes I can think of that might affect things are
the VC++ build or the shared memory changes, though I can't see why they
would cause problems offhand. I'll go try a mingw build...
/D
Dave Page wrote:
Magnus Hagander wrote:
Could you try a build without SSPI? It should be as simple as removing
the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to
touch the linker lines at all, actually, so try without first.Nope, doesn't help - still using around 9.7KB per connection. Just to be
sure I did remove the link option, and checking with depends see that
there are now only delay load references to secur32.dll, nothing direct
- as is the case with 8.2
ok. That makes sense, actually...
So the only other changes I can think of that might affect things are
the VC++ build or the shared memory changes, though I can't see why they
would cause problems offhand. I'll go try a mingw build...
Yeah, it could be that the newer MSVCRT files do something we don't
like.. Other than that, did we upgrade to a different version of some of
our dependents?
Also, is this the DEBUG or RELEASE build of 8.3?
//Magnus
Dave Page wrote:
So the only other changes I can think of that might affect things are
the VC++ build or the shared memory changes, though I can't see why they
would cause problems offhand. I'll go try a mingw build...
mingw build of stock 8.3b1, no configure options specified at all,
consumes 3.2KB of desktop heap per connection.
So, it's either something we're doing different with the VC++
compile/link options, or it's the VC8 runtimes using more resources.
Oh, and I still see the second limitation where it bombs out over about
125 connections, so that isn't build/runtime specific.
Shall we take this over to -hackers btw?
/D
On 10/22/07, Magnus Hagander <magnus@hagander.net> wrote:
Trevor Talbot wrote:
I'd probably take the approach of combining win32_waitpid() and
threads. You'd end up with 1 thread per 64 backends; when something
interesting happens the thread could push the info onto a queue, which
the new win32_waitpid() would check. Use APCs to add new backends to
threads with free slots.I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Oh, good call -- I keep forgetting the native thread pool exists.
Magnus Hagander wrote:
Yeah, it could be that the newer MSVCRT files do something we don't
like.. Other than that, did we upgrade to a different version of some of
our dependents?
Most of them - but my test build is without any of them:
our $config = {
asserts=>1, # --enable-cassert
integer_datetimes=>1, # --enable-integer-datetimes
nls=>undef, # --enable-nls=<path>
tcl=>undef, # --with-tls=<path>
perl=>undef, # --with-perl
python=>undef, # --with-python=<path>
krb5=>undef, # --with-krb5=<path>
ldap=>0, # --with-ldap
openssl=>undef, # --with-ssl=<path>
xml=>undef,
xslt=>undef,
iconv=>undef,
zlib=>undef # --with-zlib=<path>
};
Also, is this the DEBUG or RELEASE build of 8.3?
Both behave similarly.
/D
Magnus Hagander <magnus@hagander.net> writes:
I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Should be rather trivial to do.
How can that possibly work? Backends have to be able to run
concurrently, and I don't see how they'll do that if they share a stack.
regards, tom lane
On 10/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Should be rather trivial to do.How can that possibly work? Backends have to be able to run
concurrently, and I don't see how they'll do that if they share a stack.
This is about what postmaster does for its SIGCHLD wait equivalent on
win32. The 64 comes from Windows' object/event mechanism, which lets
you perform a blocking wait on up to that many handles in a single
call. Currently postmaster is creating a new thread to wait on only
one backend at a time, so it ends up with too many threads.
Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Should be rather trivial to do.How can that possibly work? Backends have to be able to run
concurrently, and I don't see how they'll do that if they share a stack.
We're not talking about the backends, we're talking about the backend
waiter threads whose sole purpose is to wait for a backend to die and
then raise a signal when it does. We can easily have the kernel wait for
a whole bunch of them at once, and have it call our callback function
whenever anyone of them dies.
//Magnus
Magnus Hagander <magnus@hagander.net> writes:
We're not talking about the backends, we're talking about the backend
waiter threads whose sole purpose is to wait for a backend to die and
then raise a signal when it does.
Oh, OK, I had not twigged to exactly what the threads were being used
for. Never mind ...
regards, tom lane
"Trevor Talbot" wrote:
I wrote:
[ desktop heap usage ]
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with....yep, under XP I'm using about 3.1KB of the service heap per
connection, which tears through it quite a bit faster. Now to figure
out exactly where it's coming from...
I can confirm this here (WinXP SP2).
I have restored the original postgresql.conf file that was created when the
cluster was initialized with Postgres 8.2.4-1 (the installed version now is
8.2.5-1). The only other change to this installation is that I have moved the
WAL directory pg_xlog to another drive using a junction link.
Here are my numbers from SysInternals System Information program:
Pages Limit: 364544KB [356MB]
Nonpaged Limit: 262144KB [256MB]
These limits are never reached.
Using the Desktop Heap Monitor every new connection consumes 3232 bytes of the
total 512KB heap.
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with.
Yeah, Win2003 behaves differently accoriding to this source:
<http://blogs.msdn.com/ntdebugging/archive/2007/01/04/desktop-heap-overview.aspx>
<quote>
Session paged pool allows session specific paged pool allocations. Windows XP
uses regular paged pool, since the number of remote desktop connections is
limited. On the other hand, Windows Server 2003 makes allocations from
session paged pool instead of regular paged pool if Terminal Services
(application server mode) is installed.
</quote>
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.
Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>
The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.
Rainer
Rainer Bauer wrote:
...yep, under XP I'm using about 3.1KB of the service heap per
connection, which tears through it quite a bit faster. Now to figure
out exactly where it's coming from...I can confirm this here (WinXP SP2).
It's coming from direct dependencies on user32.dll (from which we use
wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
and is allocated when ResumeThread() is called to kickstart the new
backend, but before the backend actually does anything (proven with a
while(1) loop in main() for the -forkbackend case with a breakpoint on
ResumeThread() in the postmaster).
I've submitted a patch against 8.3 that removes these dependencies
altogether. Unfortuntely, it seems we still have indirect dependencies
on user32.dll which I don't believe we can do anything about. In
testing, the patch reduces the per-connection desktop heap usage from
arount 9.7KB to 3.2KB which is back in line with 8.2.
Regards, Dave
Dave,
It's coming from direct dependencies on user32.dll (from which we use
wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
and is allocated when ResumeThread() is called to kickstart the new
backend,
why does every backend need its own heap for user32.dll or
shell32.dll? Wasn't the point of shared dlls to be shared?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
"Harald Armin Massa" <haraldarminmassa@gmail.com> writes:
Dave,
It's coming from direct dependencies on user32.dll (from which we use
wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
and is allocated when ResumeThread() is called to kickstart the new
backend,why does every backend need its own heap for user32.dll or
shell32.dll? Wasn't the point of shared dlls to be shared?
The Desktop Heap appears to be a place for processes belonging to the same
"desktop" to allocate shared objects such as GUI elements. These are allocated
in shared space so they can be manipulated by any process running in that
"desktop".
Why Shell32 and User32 are allocating space in there just to initialize
themselves or handle these basic utility functions is a bit of a mystery.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Harald Armin Massa wrote:
Dave,
It's coming from direct dependencies on user32.dll (from which we use
wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
and is allocated when ResumeThread() is called to kickstart the new
backend,why does every backend need its own heap for user32.dll or
shell32.dll? Wasn't the point of shared dlls to be shared?
No idea, and I thought so.
It's quite easy to prove using the test program attached. Just monitor
the desktop heap with dheapmon (from Microsoft's website), and run the
program with a single command line argument to get it to spawn a 100
child processes. You can stop it loading various DLLs by commenting out
the dummy calls to functions in them and rebuilding.
Of course, none of this would be an issue if we made the backend
multithreaded. :-)
I'll get my coat...
/D
Attachments:
why does every backend need its own heap for user32.dll or
shell32.dll? Wasn't the point of shared dlls to be shared?The Desktop Heap appears to be a place for processes belonging to the same
"desktop" to allocate shared objects such as GUI elements. These are allocated
in shared space so they can be manipulated by any process running in that
"desktop".
Using this knowledge and Daves response, also looking back at "3,2kb
per backend", I stumbled upon that KB entry:
http://support.microsoft.com/?scid=kb%3Ben-us%3B184802&x=15&y=14
Please pay special attention to the following parts:
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows
SharedSection=1024,3072,512 Windows=On SubSystemType=Windows
ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3
ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off
MaxRequestThreads=16
"""The second SharedSection value (3072) is the size of the desktop
heap for each desktop that is associated with the "interactive" window
station WinSta0."""
and further down:
"""All services that are executed under the LocalSystem account with
the Allow Service to Interact with Desktop startup option selected
will use "Winsta0\Default". All these processes will share the same
desktop heap associated with the "Default" application desktop."""
Postgres is definitely NOT started as LocalSystem account; so using a
"logical not" on Microsofts Words that could indicate the reason why
our service-backends consume that memory? Add to this that MS SQL runs
as LocalSystem; and as much as I know also Oracle.
Is this a path of thinking to try?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Replying to myself....
Postgres is definitely NOT started as LocalSystem account; so using a
"logical not" on Microsofts Words that could indicate the reason why
our service-backends consume that memory? Add to this that MS SQL runs
as LocalSystem; and as much as I know also Oracle.
just some lines further down:
"""Every service process executed under a user account will receive a
new desktop in a noninteractive window station created by the Service
Control Manager (SCM). Thus, each service executed under a user
account will consume the number of kilobytes of desktop heap specified
in the third SharedSection value. All services executed under the
LocalSystem account with Allow Service to Interact with the Desktop
not selected share the desktop heap of the "Default" desktop in the
noninteractive service windows station (Service-0x0-3e7$)."""
it is exactly as suspected ... just starting the service allocates that heap
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Harald Armin Massa wrote:
Replying to myself....
Postgres is definitely NOT started as LocalSystem account; so using a
"logical not" on Microsofts Words that could indicate the reason why
our service-backends consume that memory? Add to this that MS SQL runs
as LocalSystem; and as much as I know also Oracle.just some lines further down:
"""Every service process executed under a user account will receive a
new desktop in a noninteractive window station created by the Service
Control Manager (SCM). Thus, each service executed under a user
account will consume the number of kilobytes of desktop heap specified
in the third SharedSection value. All services executed under the
LocalSystem account with Allow Service to Interact with the Desktop
not selected share the desktop heap of the "Default" desktop in the
noninteractive service windows station (Service-0x0-3e7$)."""it is exactly as suspected ... just starting the service allocates that heap
You're missing the point I think. There's 48MB (iirc) on XP that is
reserved for desktop heaps. From that, it allocates 64KB for
WinSta0\Disconnect, 128KB for WinSta0\Winlogon and 3072KB for
WinSta0\Default (ie. the regular desktop). Each additional session
started by the SCM gets allocated the non-interactive default of 512KB.
It's not the 48MB we're running out of, it's the 512KB. That's why if
you look back in the thread, you'll see I found 8.3 was crashing with 46
connections when running as a service, but with much higher numbers of
connections when run from the logon session.
The reason why Microsoft services don't consume so much heap is that
they are multi-threaded, not multi-process so they don't init user32.dll
etc. for each individual connection like we do, but only once for the
whole server.
/D
On 10/23/07, Harald Armin Massa <haraldarminmassa@gmail.com> wrote:
The Desktop Heap appears to be a place for processes belonging to the same
"desktop" to allocate shared objects such as GUI elements. These are allocated
in shared space so they can be manipulated by any process running in that
"desktop".Using this knowledge and Daves response, also looking back at "3,2kb
per backend", I stumbled upon that KB entry:http://support.microsoft.com/?scid=kb%3Ben-us%3B184802&x=15&y=14
[...]
Postgres is definitely NOT started as LocalSystem account; so using a
"logical not" on Microsofts Words that could indicate the reason why
our service-backends consume that memory? Add to this that MS SQL runs
as LocalSystem; and as much as I know also Oracle.
It's not quite what you think. The link Rainer posted upthread does a
decent job describing it, although there's still some room for
confusion: http://blogs.msdn.com/ntdebugging/archive/2007/01/04/desktop-heap-overview.aspx
The hierarchy of containers goes Session, Window Station, Desktop.
Everything relevant is under the same Session, so I'll ignore that for
now.
The console gets a Window Station; this is the interactive one since
the user sitting down works with it directly. It normally contains
one Desktop of interest (Default), which is what the user actually
sees. (It's possible to create multiple desktops as a framework for a
"virtual desktop" type of thing, but that's all third-party stuff.)
Each service registered with the Service Manager has a specific
account it logs in under. For each account, the Service Manager
creates a Window Station to contain it, and all services using the
same account share the default Desktop inside it. Most services run
under one of the 3 canned accounts, which is what that KB article is
talking about with the Local System bit.
Each Desktop created has a fixed-size chunk of memory allocated to it.
Desktops created under the interactive Window Station get the larger
chunk of memory (3072KB) since they expect to contain lots of UI
stuff. Desktops created under other Window Stations get the smaller
chunk of memory (512KB), since they aren't presenting a UI to the
user.
That fixed-size desktop heap is used to track objects handled by the
USER subsystem, which is mostly UI elements like windows and such.
Most of the API interaction for those resources go through user32.dll,
and apparently its initialization procedure grabs some of that heap
space for each process it's loaded into.
The PostgreSQL service is set to log in under its own account, so it
gets its own Window Station, and a default Desktop inside that. This
is a non-interactive Window Station, so the Desktop gets the smaller
heap. All postgres.exe processes run in that Desktop and share one
512KB heap. As each process ends up carving out a chunk of that
space, it uses up all 512KB and fails to create more backends.
On 10/23/07, Rainer Bauer <usenet@munnin.com> wrote:
"Trevor Talbot" wrote:
It could be that there's a significant difference between XP and 2003
in how that's handled though. I do have an XP SP2 machine here with
512MB RAM, and I'll try tests on it as soon as I can free up what it's
currently occupied with.Yeah, Win2003 behaves differently accoriding to this source:
<http://blogs.msdn.com/ntdebugging/archive/2007/01/04/desktop-heap-overview.aspx><quote>
Session paged pool allows session specific paged pool allocations. Windows XP
uses regular paged pool, since the number of remote desktop connections is
limited. On the other hand, Windows Server 2003 makes allocations from
session paged pool instead of regular paged pool if Terminal Services
(application server mode) is installed.
</quote>
That's a little different. There's a specific range of kernel VM
space dedicated to session-specific data, so each session references
the same addresses but it can be backed by different physical memory
(same concept as separate processes). The session paged pool area of
that VM space is used to allocate the individual desktop heaps from.
It's saying that under XP, it's mapped to the main kernel paged pool,
while under 2003 TS it's mapped to session-specific memory, to avoid
depleting the main paged pool. (Each Terminal Services connection
creates an entire Session.) It doesn't change how desktop heap is
actually used though, which is the issue we're running into.
The system I'm testing on doesn't have Terminal Services running in
appserver mode.
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.
It's probably safe to do on a typical XP box, but it's unfortunately
not something you want the installer to do, or even suggest as blanket
advice. I also wondered about having postmaster create more desktops
on demand, but that has about the same amount of sanity (i.e. not
much).
I think it boils down to getting postgres to avoid using desktop heap
if at all possible, and if not, advising people to avoid XP for high
concurrency, except for suggesting the above change in specific
circumstances.
I suspect win2000 has the same issue, but I don't have a system to
test. It'd be interesting to know if 2000 Professional behaves any
differently than Server.
Rainer Bauer wrote:
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.
This part should go in the FAQ, I think. It's valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.
Dave, you're listed as maintainer :-P
//Magnus
Magnus Hagander wrote:
Rainer Bauer wrote:
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.This part should go in the FAQ, I think. It's valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.Dave, you're listed as maintainer :-P
done.
/D
On Mon, Oct 22, 2007 at 01:19:24PM -0700, Trevor Talbot wrote:
On 10/22/07, Magnus Hagander <magnus@hagander.net> wrote:
Trevor Talbot wrote:
I'd probably take the approach of combining win32_waitpid() and
threads. You'd end up with 1 thread per 64 backends; when something
interesting happens the thread could push the info onto a queue, which
the new win32_waitpid() would check. Use APCs to add new backends to
threads with free slots.I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.Oh, good call -- I keep forgetting the native thread pool exists.
Taking this one to -hackers once and for all now...
Can you try the attached patch? See how many backends you can get up to.
This patch changes from using a single thread for each backend started to
using the builtin threadpool functionality. It also replaces the pid/handle
arrays with an i/o completion port. The net result is also, imho, much more
readable code :-)
Beware - there's still plenty of debugging code in there :-)
//Magnus
Attachments:
win32_child.patchtext/plain; charset=us-asciiDownload
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.542
diff -c -r1.542 postmaster.c
*** src/backend/postmaster/postmaster.c 26 Sep 2007 22:36:30 -0000 1.542
--- src/backend/postmaster/postmaster.c 26 Oct 2007 11:46:45 -0000
***************
*** 331,344 ****
#ifdef EXEC_BACKEND
#ifdef WIN32
- static void win32_AddChild(pid_t pid, HANDLE handle);
- static void win32_RemoveChild(pid_t pid);
static pid_t win32_waitpid(int *exitstatus);
! static DWORD WINAPI win32_sigchld_waiter(LPVOID param);
! static pid_t *win32_childPIDArray;
! static HANDLE *win32_childHNDArray;
! static unsigned long win32_numChildren = 0;
HANDLE PostmasterHandle;
#endif
--- 331,347 ----
#ifdef EXEC_BACKEND
#ifdef WIN32
static pid_t win32_waitpid(int *exitstatus);
! static void WINAPI pgwin32_deadchild_callback(PVOID lpParameter, BOOLEAN TimerOrWaitFired);
! static HANDLE win32ChildQueue;
!
! typedef struct
! {
! HANDLE waitHandle;
! HANDLE procHandle;
! DWORD procId;
! } win32_deadchild_waitinfo;
HANDLE PostmasterHandle;
#endif
***************
*** 899,914 ****
#ifdef WIN32
/*
! * Initialize the child pid/HANDLE arrays for signal handling.
*/
! win32_childPIDArray = (pid_t *)
! malloc(mul_size(NUM_BACKENDARRAY_ELEMS, sizeof(pid_t)));
! win32_childHNDArray = (HANDLE *)
! malloc(mul_size(NUM_BACKENDARRAY_ELEMS, sizeof(HANDLE)));
! if (!win32_childPIDArray || !win32_childHNDArray)
ereport(FATAL,
! (errcode(ERRCODE_OUT_OF_MEMORY),
! errmsg("out of memory")));
/*
* Set up a handle that child processes can use to check whether the
--- 902,913 ----
#ifdef WIN32
/*
! * Initialize I/O completion port used to deliver list of dead children.
*/
! win32ChildQueue = CreateIoCompletionPort(INVALID_HANDLE_VALUE, NULL, 0, 1);
! if (win32ChildQueue == NULL)
ereport(FATAL,
! (errmsg("could not create I/O completion port for child queue")));
/*
* Set up a handle that child processes can use to check whether the
***************
*** 2072,2083 ****
#define LOOPHEADER() (exitstatus = status.w_status)
#else /* WIN32 */
#define LOOPTEST() ((pid = win32_waitpid(&exitstatus)) > 0)
! /*
! * We need to do this here, and not in CleanupBackend, since this is
! * to be called on all children when we are done with them. Could move
! * to LogChildExit, but that seems like asking for future trouble...
! */
! #define LOOPHEADER() (win32_RemoveChild(pid))
#endif /* WIN32 */
#endif /* HAVE_WAITPID */
--- 2071,2077 ----
#define LOOPHEADER() (exitstatus = status.w_status)
#else /* WIN32 */
#define LOOPTEST() ((pid = win32_waitpid(&exitstatus)) > 0)
! #define LOOPHEADER()
#endif /* WIN32 */
#endif /* HAVE_WAITPID */
***************
*** 3332,3343 ****
int i;
int j;
char cmdLine[MAXPGPATH * 2];
- HANDLE childHandleCopy;
- HANDLE waiterThread;
HANDLE paramHandle;
BackendParameters *param;
SECURITY_ATTRIBUTES sa;
char paramHandleStr[32];
/* Make sure caller set up argv properly */
Assert(argc >= 3);
--- 3326,3336 ----
int i;
int j;
char cmdLine[MAXPGPATH * 2];
HANDLE paramHandle;
BackendParameters *param;
SECURITY_ATTRIBUTES sa;
char paramHandleStr[32];
+ win32_deadchild_waitinfo *childinfo;
/* Make sure caller set up argv properly */
Assert(argc >= 3);
***************
*** 3346,3358 ****
Assert(argv[2] == NULL);
/* Verify that there is room in the child list */
if (win32_numChildren >= NUM_BACKENDARRAY_ELEMS)
{
elog(LOG, "no room for child entry in backend list");
/* Report same error as for a fork failure on Unix */
! errno = EAGAIN;
return -1;
! }
/* Set up shared memory for parameter passing */
ZeroMemory(&sa, sizeof(sa));
--- 3339,3352 ----
Assert(argv[2] == NULL);
/* Verify that there is room in the child list */
+ /* XXXX still need to verify? Or not?
if (win32_numChildren >= NUM_BACKENDARRAY_ELEMS)
{
elog(LOG, "no room for child entry in backend list");
/* Report same error as for a fork failure on Unix */
! /* errno = EAGAIN;
return -1;
! }*/
/* Set up shared memory for parameter passing */
ZeroMemory(&sa, sizeof(sa));
***************
*** 3463,3496 ****
return -1;
}
! if (!IsUnderPostmaster)
! {
! /* We are the Postmaster creating a child... */
! win32_AddChild(pi.dwProcessId, pi.hProcess);
! }
!
! /* Set up the thread to handle the SIGCHLD for this process */
! if (DuplicateHandle(GetCurrentProcess(),
! pi.hProcess,
! GetCurrentProcess(),
! &childHandleCopy,
! 0,
! FALSE,
! DUPLICATE_SAME_ACCESS) == 0)
ereport(FATAL,
! (errmsg_internal("could not duplicate child handle: error code %d",
(int) GetLastError())));
! waiterThread = CreateThread(NULL, 64 * 1024, win32_sigchld_waiter,
! (LPVOID) childHandleCopy, 0, NULL);
! if (!waiterThread)
! ereport(FATAL,
! (errmsg_internal("could not create sigchld waiter thread: error code %d",
! (int) GetLastError())));
! CloseHandle(waiterThread);
- if (IsUnderPostmaster)
- CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
return pi.dwProcessId;
--- 3457,3482 ----
return -1;
}
! /* Queue a SIGCHLD handler for this process */
! childinfo = malloc(sizeof(win32_deadchild_waitinfo));
! if (!childinfo)
ereport(FATAL,
! (errcode(ERRCODE_OUT_OF_MEMORY),
! errmsg("out of memory")));
! childinfo->procHandle = pi.hProcess;
! childinfo->procId = pi.dwProcessId;
! if (!RegisterWaitForSingleObject(&childinfo->waitHandle,
! pi.hProcess,
! pgwin32_deadchild_callback,
! childinfo,
! INFINITE,
! WT_EXECUTEONLYONCE | WT_EXECUTEINWAITTHREAD))
! ereport(FATAL,
! (errmsg_internal("could not register process for wait: error code %d",
(int) GetLastError())));
! /* Don't close pi.hProcess here - the wait thread needs access to it */
CloseHandle(pi.hThread);
return pi.dwProcessId;
***************
*** 4500,4636 ****
#ifdef WIN32
- /*
- * Note: The following three functions must not be interrupted (eg. by
- * signals). As the Postgres Win32 signalling architecture (currently)
- * requires polling, or APC checking functions which aren't used here, this
- * is not an issue.
- *
- * We keep two separate arrays, instead of a single array of pid/HANDLE
- * structs, to avoid having to re-create a handle array for
- * WaitForMultipleObjects on each call to win32_waitpid.
- */
-
- static void
- win32_AddChild(pid_t pid, HANDLE handle)
- {
- Assert(win32_childPIDArray && win32_childHNDArray);
- if (win32_numChildren < NUM_BACKENDARRAY_ELEMS)
- {
- win32_childPIDArray[win32_numChildren] = pid;
- win32_childHNDArray[win32_numChildren] = handle;
- ++win32_numChildren;
- }
- else
- ereport(FATAL,
- (errmsg_internal("no room for child entry with pid %lu",
- (unsigned long) pid)));
- }
-
- static void
- win32_RemoveChild(pid_t pid)
- {
- int i;
-
- Assert(win32_childPIDArray && win32_childHNDArray);
-
- for (i = 0; i < win32_numChildren; i++)
- {
- if (win32_childPIDArray[i] == pid)
- {
- CloseHandle(win32_childHNDArray[i]);
-
- /* Swap last entry into the "removed" one */
- --win32_numChildren;
- win32_childPIDArray[i] = win32_childPIDArray[win32_numChildren];
- win32_childHNDArray[i] = win32_childHNDArray[win32_numChildren];
- return;
- }
- }
-
- ereport(WARNING,
- (errmsg_internal("could not find child entry with pid %lu",
- (unsigned long) pid)));
- }
-
static pid_t
win32_waitpid(int *exitstatus)
{
/*
! * Note: Do NOT use WaitForMultipleObjectsEx, as we don't want to run
! * queued APCs here.
*/
! int index;
! DWORD exitCode;
! DWORD ret;
! unsigned long offset;
!
! Assert(win32_childPIDArray && win32_childHNDArray);
! elog(DEBUG3, "waiting on %lu children", win32_numChildren);
!
! for (offset = 0; offset < win32_numChildren; offset += MAXIMUM_WAIT_OBJECTS)
{
! unsigned long num = Min(MAXIMUM_WAIT_OBJECTS, win32_numChildren - offset);
!
! ret = WaitForMultipleObjects(num, &win32_childHNDArray[offset], FALSE, 0);
! switch (ret)
{
! case WAIT_FAILED:
! ereport(LOG,
! (errmsg_internal("failed to wait on %lu of %lu children: error code %d",
! num, win32_numChildren, (int) GetLastError())));
! return -1;
!
! case WAIT_TIMEOUT:
! /* No children (in this chunk) have finished */
! break;
!
! default:
!
! /*
! * Get the exit code, and return the PID of, the respective
! * process
! */
! index = offset + ret - WAIT_OBJECT_0;
! Assert(index >= 0 && index < win32_numChildren);
! if (!GetExitCodeProcess(win32_childHNDArray[index], &exitCode))
! {
! /*
! * If we get this far, this should never happen, but, then
! * again... No choice other than to assume a catastrophic
! * failure.
! */
! ereport(FATAL,
! (errmsg_internal("failed to get exit code for child %lu",
! (unsigned long) win32_childPIDArray[index])));
! }
! *exitstatus = (int) exitCode;
! return win32_childPIDArray[index];
}
}
- /* No children have finished */
return -1;
}
/*
! * Note! Code below executes on separate threads, one for
! * each child process created
*/
! static DWORD WINAPI
! win32_sigchld_waiter(LPVOID param)
{
! HANDLE procHandle = (HANDLE) param;
! DWORD r = WaitForSingleObject(procHandle, INFINITE);
! if (r == WAIT_OBJECT_0)
! pg_queue_signal(SIGCHLD);
! else
! write_stderr("could not wait on child process handle: error code %d\n",
! (int) GetLastError());
! CloseHandle(procHandle);
! return 0;
}
#endif /* WIN32 */
--- 4486,4549 ----
#ifdef WIN32
static pid_t
win32_waitpid(int *exitstatus)
{
+ DWORD dwd;
+ ULONG_PTR key;
+ OVERLAPPED* ovl;
/*
! * Check if there are any dead children. If there are, return the pid of the first one that died.
*/
! if (GetQueuedCompletionStatus(win32ChildQueue,&dwd,&key,&ovl,0))
{
! *exitstatus = (int)key;
{
! char t[128];
! sprintf(t,"Unqueued dead child pid %i, exitcode %i", dwd, (int)key);
! OutputDebugString(t);
}
+ return dwd;
}
+ OutputDebugString("No dead children to unqueue");
return -1;
}
/*
! * Note! Code below executes on a thread pool! All operations must
! * be thread safe! Note that elog() and friends must *not* be used.
*/
! static void WINAPI
! pgwin32_deadchild_callback(PVOID lpParameter, BOOLEAN TimerOrWaitFired)
{
! win32_deadchild_waitinfo *childinfo = (win32_deadchild_waitinfo *)lpParameter;
! DWORD exitcode;
! if (TimerOrWaitFired)
! return; /* timeout. Should not happen. */
! /* Remove handle from wait - required even though it's set to wait only once */
! UnregisterWaitEx(childinfo->waitHandle, NULL);
!
! /* Post I/O completion about dead pid. First, figure out the exit code */
! if (!GetExitCodeProcess(childinfo->procHandle, &exitcode))
! exitcode = 255; /* should never happen */
!
! PostQueuedCompletionStatus(win32ChildQueue,childinfo->procId,(ULONG_PTR)exitcode,NULL);
! {
! char t[128];
! sprintf(t,"Posted completion status for child %i, exitcode %i", childinfo->procId, exitcode);
! OutputDebugString(t);
! }
!
! /* Handle is per-process, so we close it hear instead of in the caller */
! CloseHandle(childinfo->procHandle);
!
! free(childinfo);
!
! /* Queue SIGCHLD signal */
! pg_queue_signal(SIGCHLD);
}
#endif /* WIN32 */
Index: src/include/port/win32.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/port/win32.h,v
retrieving revision 1.76
diff -c -r1.76 win32.h
*** src/include/port/win32.h 25 Jul 2007 12:22:53 -0000 1.76
--- src/include/port/win32.h 26 Oct 2007 11:16:48 -0000
***************
*** 4,9 ****
--- 4,10 ----
#define WIN32_ONLY_COMPILER
#endif
+ #define _WIN32_WINNT 0x0500
/*
* Always build with SSPI support. Keep it as a #define in case
* we want a switch to disable it sometime in the future.
On 10/26/07, Magnus Hagander <magnus@hagander.net> wrote:
Can you try the attached patch? See how many backends you can get up to.
This patch changes from using a single thread for each backend started to
using the builtin threadpool functionality. It also replaces the pid/handle
arrays with an i/o completion port. The net result is also, imho, much more
readable code :-)
The patch looks good; I'm not set up to build yet, but I should be
able to test it sometime in the next week.
On Fri, Oct 26, 2007 at 05:25:39AM -0700, Trevor Talbot wrote:
On 10/26/07, Magnus Hagander <magnus@hagander.net> wrote:
Can you try the attached patch? See how many backends you can get up to.
This patch changes from using a single thread for each backend started to
using the builtin threadpool functionality. It also replaces the pid/handle
arrays with an i/o completion port. The net result is also, imho, much more
readable code :-)The patch looks good; I'm not set up to build yet, but I should be
able to test it sometime in the next week.
I've uploaded a set of binary files to
http://www.hagander.net/pgsql/pgsql_83_snapshot_win32child.zip.
You'll need to get the dependency DLLs elsewhere, but you may have them
already.
//Magnus
Magnus Hagander wrote:
Taking this one to -hackers once and for all now...
Can you try the attached patch? See how many backends you can get up to.
Regression tests run just fine, and I've run multiple pgbench runs with
3 and 4 sessions of 100 connections each*, with pgAdmin monitoring
things at the same time. Saw up to 403 simultanteous connections in
pg_stat_activity, and the system remained stable and responsive, albeit
somewhat slower than normal.
So, 400 connections on a 2.33GHz MacBook Pro running XP Pro with 2GB RAM
- thats not too shabby :-)
/D
* For those that weren't peering over Magnus' or Greg's shoulder during
various IM discussions over the last few days, I've found that the ~125
connection ceiling I was hitting when running from a command prompt was
actually an as yet unsolved problem in pgbench, not the server. Multiple
pgbench sessions seem to run just fine if kept to around 100 connections
each.
Taking this one to -hackers once and for all now...
Can you try the attached patch? See how many backends you can get up to.
Regression tests run just fine, and I've run multiple pgbench runs with
3 and 4 sessions of 100 connections each*, with pgAdmin monitoring
things at the same time. Saw up to 403 simultanteous connections in
pg_stat_activity, and the system remained stable and responsive, albeit
somewhat slower than normal.
What was the memory space consumption of the postmaster process, and compared to without the patch?
VM size in taskmgr should show that I think, and should show a much smaller footprint now..
/Magnus
Import Notes
Resolved by subject fallback
Dave Page wrote:
Magnus Hagander wrote:
Rainer Bauer wrote:
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.This part should go in the FAQ, I think. It's valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.Dave, you're listed as maintainer :-P
done.
Dave could you add that it's the third parameter of the "SharedSection" string
that must be changed. I read that KB article, but still had to find the
correct one by trial and error, which required a reboot every time.
Rainer
Magnus Hagander wrote:
VM size in taskmgr should show that I think, and should show a much
smaller footprint now..
With patch - 4,492K
Without patch: 28,224K
Thats with 3 x 100 pgbench connections.
/D
Dave Page wrote:
Magnus Hagander wrote:
VM size in taskmgr should show that I think, and should show a much
smaller footprint now..With patch - 4,492K
Without patch: 28,224KThats with 3 x 100 pgbench connections.
That's nice!
But. That can't be address space usage, it has to be actual memory
usage. Since each thread should chew up 4Mb of address space, and
there's at least two threads in there :-) So looking at the VM column
was obviously not correct.
* looks up some docs*
Right. You need to look at VM size in *process explorer*. VM size in
task manager has nothing to do with VM size, it's the private bytes :-S
And there is no way to see that info from task manager, I think. PE is
your friend.
Anyway. Other than a refresher on those, I'd be interested in two other
important parts:
* How many threads does it reach when you have 300 active backends?
* Is there a handle leak? meaning once your 300 backends have exited,
does the number of handles in the process drop down to the same value it
had before?
(sorry, wish I was in a position to run these tests myself, but I'm not
right now)
//Magnus
Magnus Hagander wrote:
Right. You need to look at VM size in *process explorer*. VM size in
task manager has nothing to do with VM size, it's the private bytes :-S
And there is no way to see that info from task manager, I think. PE is
your friend.Anyway. Other than a refresher on those, I'd be interested in two other
important parts:
* How many threads does it reach when you have 300 active backends?
* Is there a handle leak? meaning once your 300 backends have exited,
does the number of handles in the process drop down to the same value it
had before?
Without patch:
VM: 1,322,792K
Idle threads: 6
Peak threads: 306
Handles at start: 576
Handles at end: 576
With patch:
VM: 98,088K
Idle threads: 3
Peak threads: 7
Handles at start: 576
Handles at end: 585 (585 again after second run).
/D
Dave Page wrote:
Magnus Hagander wrote:
Right. You need to look at VM size in *process explorer*. VM size in
task manager has nothing to do with VM size, it's the private bytes :-S
And there is no way to see that info from task manager, I think. PE is
your friend.Anyway. Other than a refresher on those, I'd be interested in two other
important parts:
* How many threads does it reach when you have 300 active backends?
* Is there a handle leak? meaning once your 300 backends have exited,
does the number of handles in the process drop down to the same value it
had before?Without patch:
VM: 1,322,792K
Idle threads: 6
Peak threads: 306
Handles at start: 576
Handles at end: 576With patch:
VM: 98,088K
Idle threads: 3
Peak threads: 7
Handles at start: 576
Handles at end: 585 (585 again after second run).
Ah, now we're talking. That's the kind of reduction I was looking for :-)
I think the difference in handles is because the threadpool keeps some
things around. As long as it stays at 585 and comes back down after a
second run, we're fine at that - there's no leak.
Attached is an updated version of the patch, currently being tested by
both me and Dave. If it passes our tests, I'll apply this so it gets
included for broader testing in beta2.
//Magnus
Attachments:
win32_child.patchtext/x-patch; name=win32_child.patchDownload
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.542
diff -c -r1.542 postmaster.c
*** src/backend/postmaster/postmaster.c 26 Sep 2007 22:36:30 -0000 1.542
--- src/backend/postmaster/postmaster.c 26 Oct 2007 20:09:35 -0000
***************
*** 331,344 ****
#ifdef EXEC_BACKEND
#ifdef WIN32
- static void win32_AddChild(pid_t pid, HANDLE handle);
- static void win32_RemoveChild(pid_t pid);
static pid_t win32_waitpid(int *exitstatus);
! static DWORD WINAPI win32_sigchld_waiter(LPVOID param);
! static pid_t *win32_childPIDArray;
! static HANDLE *win32_childHNDArray;
! static unsigned long win32_numChildren = 0;
HANDLE PostmasterHandle;
#endif
--- 331,347 ----
#ifdef EXEC_BACKEND
#ifdef WIN32
static pid_t win32_waitpid(int *exitstatus);
! static void WINAPI pgwin32_deadchild_callback(PVOID lpParameter, BOOLEAN TimerOrWaitFired);
! static HANDLE win32ChildQueue;
!
! typedef struct
! {
! HANDLE waitHandle;
! HANDLE procHandle;
! DWORD procId;
! } win32_deadchild_waitinfo;
HANDLE PostmasterHandle;
#endif
***************
*** 899,914 ****
#ifdef WIN32
/*
! * Initialize the child pid/HANDLE arrays for signal handling.
*/
! win32_childPIDArray = (pid_t *)
! malloc(mul_size(NUM_BACKENDARRAY_ELEMS, sizeof(pid_t)));
! win32_childHNDArray = (HANDLE *)
! malloc(mul_size(NUM_BACKENDARRAY_ELEMS, sizeof(HANDLE)));
! if (!win32_childPIDArray || !win32_childHNDArray)
ereport(FATAL,
! (errcode(ERRCODE_OUT_OF_MEMORY),
! errmsg("out of memory")));
/*
* Set up a handle that child processes can use to check whether the
--- 902,913 ----
#ifdef WIN32
/*
! * Initialize I/O completion port used to deliver list of dead children.
*/
! win32ChildQueue = CreateIoCompletionPort(INVALID_HANDLE_VALUE, NULL, 0, 1);
! if (win32ChildQueue == NULL)
ereport(FATAL,
! (errmsg("could not create I/O completion port for child queue")));
/*
* Set up a handle that child processes can use to check whether the
***************
*** 2072,2083 ****
#define LOOPHEADER() (exitstatus = status.w_status)
#else /* WIN32 */
#define LOOPTEST() ((pid = win32_waitpid(&exitstatus)) > 0)
! /*
! * We need to do this here, and not in CleanupBackend, since this is
! * to be called on all children when we are done with them. Could move
! * to LogChildExit, but that seems like asking for future trouble...
! */
! #define LOOPHEADER() (win32_RemoveChild(pid))
#endif /* WIN32 */
#endif /* HAVE_WAITPID */
--- 2071,2077 ----
#define LOOPHEADER() (exitstatus = status.w_status)
#else /* WIN32 */
#define LOOPTEST() ((pid = win32_waitpid(&exitstatus)) > 0)
! #define LOOPHEADER()
#endif /* WIN32 */
#endif /* HAVE_WAITPID */
***************
*** 3332,3343 ****
int i;
int j;
char cmdLine[MAXPGPATH * 2];
- HANDLE childHandleCopy;
- HANDLE waiterThread;
HANDLE paramHandle;
BackendParameters *param;
SECURITY_ATTRIBUTES sa;
char paramHandleStr[32];
/* Make sure caller set up argv properly */
Assert(argc >= 3);
--- 3326,3336 ----
int i;
int j;
char cmdLine[MAXPGPATH * 2];
HANDLE paramHandle;
BackendParameters *param;
SECURITY_ATTRIBUTES sa;
char paramHandleStr[32];
+ win32_deadchild_waitinfo *childinfo;
/* Make sure caller set up argv properly */
Assert(argc >= 3);
***************
*** 3345,3359 ****
Assert(strncmp(argv[1], "--fork", 6) == 0);
Assert(argv[2] == NULL);
- /* Verify that there is room in the child list */
- if (win32_numChildren >= NUM_BACKENDARRAY_ELEMS)
- {
- elog(LOG, "no room for child entry in backend list");
- /* Report same error as for a fork failure on Unix */
- errno = EAGAIN;
- return -1;
- }
-
/* Set up shared memory for parameter passing */
ZeroMemory(&sa, sizeof(sa));
sa.nLength = sizeof(sa);
--- 3338,3343 ----
***************
*** 3463,3496 ****
return -1;
}
! if (!IsUnderPostmaster)
! {
! /* We are the Postmaster creating a child... */
! win32_AddChild(pi.dwProcessId, pi.hProcess);
! }
! /* Set up the thread to handle the SIGCHLD for this process */
! if (DuplicateHandle(GetCurrentProcess(),
! pi.hProcess,
! GetCurrentProcess(),
! &childHandleCopy,
! 0,
! FALSE,
! DUPLICATE_SAME_ACCESS) == 0)
ereport(FATAL,
! (errmsg_internal("could not duplicate child handle: error code %d",
(int) GetLastError())));
! waiterThread = CreateThread(NULL, 64 * 1024, win32_sigchld_waiter,
! (LPVOID) childHandleCopy, 0, NULL);
! if (!waiterThread)
! ereport(FATAL,
! (errmsg_internal("could not create sigchld waiter thread: error code %d",
! (int) GetLastError())));
! CloseHandle(waiterThread);
- if (IsUnderPostmaster)
- CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
return pi.dwProcessId;
--- 3447,3479 ----
return -1;
}
! /*
! * Queue a waiter for to signal when this child dies. The wait will be handled automatically
! * by an operating system thread pool.
! *
! * Note: use malloc instead of palloc, since it needs to be thread-safe
! */
! childinfo = malloc(sizeof(win32_deadchild_waitinfo));
! if (!childinfo)
! ereport(FATAL,
! (errcode(ERRCODE_OUT_OF_MEMORY),
! errmsg("out of memory")));
! childinfo->procHandle = pi.hProcess;
! childinfo->procId = pi.dwProcessId;
!
! if (!RegisterWaitForSingleObject(&childinfo->waitHandle,
! pi.hProcess,
! pgwin32_deadchild_callback,
! childinfo,
! INFINITE,
! WT_EXECUTEONLYONCE | WT_EXECUTEINWAITTHREAD))
ereport(FATAL,
! (errmsg_internal("could not register process for wait: error code %d",
(int) GetLastError())));
! /* Don't close pi.hProcess here - the wait thread needs access to it */
CloseHandle(pi.hThread);
return pi.dwProcessId;
***************
*** 4500,4636 ****
#ifdef WIN32
- /*
- * Note: The following three functions must not be interrupted (eg. by
- * signals). As the Postgres Win32 signalling architecture (currently)
- * requires polling, or APC checking functions which aren't used here, this
- * is not an issue.
- *
- * We keep two separate arrays, instead of a single array of pid/HANDLE
- * structs, to avoid having to re-create a handle array for
- * WaitForMultipleObjects on each call to win32_waitpid.
- */
-
- static void
- win32_AddChild(pid_t pid, HANDLE handle)
- {
- Assert(win32_childPIDArray && win32_childHNDArray);
- if (win32_numChildren < NUM_BACKENDARRAY_ELEMS)
- {
- win32_childPIDArray[win32_numChildren] = pid;
- win32_childHNDArray[win32_numChildren] = handle;
- ++win32_numChildren;
- }
- else
- ereport(FATAL,
- (errmsg_internal("no room for child entry with pid %lu",
- (unsigned long) pid)));
- }
-
- static void
- win32_RemoveChild(pid_t pid)
- {
- int i;
-
- Assert(win32_childPIDArray && win32_childHNDArray);
-
- for (i = 0; i < win32_numChildren; i++)
- {
- if (win32_childPIDArray[i] == pid)
- {
- CloseHandle(win32_childHNDArray[i]);
-
- /* Swap last entry into the "removed" one */
- --win32_numChildren;
- win32_childPIDArray[i] = win32_childPIDArray[win32_numChildren];
- win32_childHNDArray[i] = win32_childHNDArray[win32_numChildren];
- return;
- }
- }
-
- ereport(WARNING,
- (errmsg_internal("could not find child entry with pid %lu",
- (unsigned long) pid)));
- }
-
static pid_t
win32_waitpid(int *exitstatus)
{
/*
! * Note: Do NOT use WaitForMultipleObjectsEx, as we don't want to run
! * queued APCs here.
*/
! int index;
! DWORD exitCode;
! DWORD ret;
! unsigned long offset;
!
! Assert(win32_childPIDArray && win32_childHNDArray);
! elog(DEBUG3, "waiting on %lu children", win32_numChildren);
!
! for (offset = 0; offset < win32_numChildren; offset += MAXIMUM_WAIT_OBJECTS)
{
! unsigned long num = Min(MAXIMUM_WAIT_OBJECTS, win32_numChildren - offset);
!
! ret = WaitForMultipleObjects(num, &win32_childHNDArray[offset], FALSE, 0);
! switch (ret)
! {
! case WAIT_FAILED:
! ereport(LOG,
! (errmsg_internal("failed to wait on %lu of %lu children: error code %d",
! num, win32_numChildren, (int) GetLastError())));
! return -1;
!
! case WAIT_TIMEOUT:
! /* No children (in this chunk) have finished */
! break;
!
! default:
!
! /*
! * Get the exit code, and return the PID of, the respective
! * process
! */
! index = offset + ret - WAIT_OBJECT_0;
! Assert(index >= 0 && index < win32_numChildren);
! if (!GetExitCodeProcess(win32_childHNDArray[index], &exitCode))
! {
! /*
! * If we get this far, this should never happen, but, then
! * again... No choice other than to assume a catastrophic
! * failure.
! */
! ereport(FATAL,
! (errmsg_internal("failed to get exit code for child %lu",
! (unsigned long) win32_childPIDArray[index])));
! }
! *exitstatus = (int) exitCode;
! return win32_childPIDArray[index];
! }
}
- /* No children have finished */
return -1;
}
/*
! * Note! Code below executes on separate threads, one for
! * each child process created
*/
! static DWORD WINAPI
! win32_sigchld_waiter(LPVOID param)
{
! HANDLE procHandle = (HANDLE) param;
! DWORD r = WaitForSingleObject(procHandle, INFINITE);
! if (r == WAIT_OBJECT_0)
! pg_queue_signal(SIGCHLD);
! else
! write_stderr("could not wait on child process handle: error code %d\n",
! (int) GetLastError());
! CloseHandle(procHandle);
! return 0;
}
#endif /* WIN32 */
--- 4483,4542 ----
#ifdef WIN32
static pid_t
win32_waitpid(int *exitstatus)
{
+ DWORD dwd;
+ ULONG_PTR key;
+ OVERLAPPED* ovl;
+
/*
! * Check if there are any dead children. If there are, return the pid of the first one that died.
*/
! if (GetQueuedCompletionStatus(win32ChildQueue,&dwd,&key,&ovl,0))
{
! *exitstatus = (int)key;
! return dwd;
}
return -1;
}
/*
! * Note! Code below executes on a thread pool! All operations must
! * be thread safe! Note that elog() and friends must *not* be used.
*/
! static void WINAPI
! pgwin32_deadchild_callback(PVOID lpParameter, BOOLEAN TimerOrWaitFired)
{
! win32_deadchild_waitinfo *childinfo = (win32_deadchild_waitinfo *)lpParameter;
! DWORD exitcode;
! if (TimerOrWaitFired)
! return; /* timeout. Should never happen, since we use INFINITE as timeout value. */
! /* Remove handle from wait - required even though it's set to wait only once */
! UnregisterWaitEx(childinfo->waitHandle, NULL);
!
! if (!GetExitCodeProcess(childinfo->procHandle, &exitcode))
! {
! /*
! * Should never happen. Inform user and set a fixed exitcode.
! */
! write_stderr("could not read exitcode for process\n");
! exitcode = 255;
! }
!
! if (!PostQueuedCompletionStatus(win32ChildQueue,childinfo->procId,(ULONG_PTR)exitcode,NULL))
! write_stderr("could not post child completion status\n");
!
! /* Handle is per-process, so we close it here instead of in the originating thread */
! CloseHandle(childinfo->procHandle);
!
! free(childinfo);
!
! /* Queue SIGCHLD signal */
! pg_queue_signal(SIGCHLD);
}
#endif /* WIN32 */
Index: src/include/port/win32.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/port/win32.h,v
retrieving revision 1.76
diff -c -r1.76 win32.h
*** src/include/port/win32.h 25 Jul 2007 12:22:53 -0000 1.76
--- src/include/port/win32.h 26 Oct 2007 11:16:48 -0000
***************
*** 4,9 ****
--- 4,10 ----
#define WIN32_ONLY_COMPILER
#endif
+ #define _WIN32_WINNT 0x0500
/*
* Always build with SSPI support. Keep it as a #define in case
* we want a switch to disable it sometime in the future.
Magnus Hagander wrote:
Dave Page wrote:
Magnus Hagander wrote:
Right. You need to look at VM size in *process explorer*. VM size in
task manager has nothing to do with VM size, it's the private bytes :-S
And there is no way to see that info from task manager, I think. PE is
your friend.Anyway. Other than a refresher on those, I'd be interested in two other
important parts:
* How many threads does it reach when you have 300 active backends?
* Is there a handle leak? meaning once your 300 backends have exited,
does the number of handles in the process drop down to the same value it
had before?Without patch:
VM: 1,322,792K
Idle threads: 6
Peak threads: 306
Handles at start: 576
Handles at end: 576With patch:
VM: 98,088K
Idle threads: 3
Peak threads: 7
Handles at start: 576
Handles at end: 585 (585 again after second run).Ah, now we're talking. That's the kind of reduction I was looking for :-)
I think the difference in handles is because the threadpool keeps some
things around. As long as it stays at 585 and comes back down after a
second run, we're fine at that - there's no leak.Attached is an updated version of the patch, currently being tested by
both me and Dave. If it passes our tests, I'll apply this so it gets
included for broader testing in beta2.
So of course, for all good patches, problems turn up :-(
This patch doesn't work on mingw, at least not on all versions. The
reason is that, as usual, the mingw libraries are not complete. We've
dealt with this before, by dynamically loading the functions. We know
this works. But I don't have time to fix that tonight, and I'll be
offline much of this weekend.
Now, given these great improvements, I'd very much like this in beta2,
so it can get proper testing. This leaves us with a couple of choices:
1) Delay beta2 until the beginning of next week. I'll get this fixed
sunday evening or monday at the latest. I know how to fix it, it's just
that I don't have the time right now :( (This assumes that the plan
still is to wrap beta2 today)
2) Apply the patch as-is. Then beta2 will work fine with the msvc build,
which is used for the binary distribution. But it's broken on mingw
until fixed, which of course includes the buildfarm stuff. Again, we
know how to fix this.
2b) I apply this as-is, and someone else cleans up mingw before beta2 is
wrapped.
3) We don't apply this, and wait until beta3 to have it tested.
Depending on how many betas we end up with, that may leave us with very
little testing before release.
2b is of course the best here, but then someone has to step up and
volunteer to do that.
I'm leaning towards applying the patch now, and hoping for 2b to happen.
If it doesn't happen, the choice between 1 and 2 can be made when the
time to wrap the beta approaches (at which point I will be offline, so I
escape :-P)
The patch that would go in is the one previously posted plus a couple of
minor edits in comments as suggested on IM by Alvaro.
Comments?
//Magnus
Magnus Hagander wrote:
I'm leaning towards applying the patch now, and hoping for 2b to happen.
I think we should live with the mingw BF breakage for a day or two. The
patch is clearly an important improvement, but it should be as widely
tested as possible.
/D
On Fri, 26 Oct 2007 21:58:03 +0100
Dave Page <dpage@postgresql.org> wrote:
Magnus Hagander wrote:
I'm leaning towards applying the patch now, and hoping for 2b to
happen.I think we should live with the mingw BF breakage for a day or two.
The patch is clearly an important improvement, but it should be as
widely tested as possible.
I think this makes sense. I certainly don't want to hold back Beta2 and
this patch so far is an obvious improvement.
Sincerely,
Joshua D. Drake
/D
---------------------------(end of
broadcast)--------------------------- TIP 2: Don't 'kill -9' the
postmaster
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Dave Page <dpage@postgresql.org> writes:
Magnus Hagander wrote:
I'm leaning towards applying the patch now, and hoping for 2b to happen.
I think we should live with the mingw BF breakage for a day or two. The
patch is clearly an important improvement, but it should be as widely
tested as possible.
If Dave is intending to build the Windows installer as soon as beta2 is
wrapped, then I agree with this plan. But my understanding was we'd
missed his window for today and so that wouldn't happen till Monday.
Assuming that's true, the idea Bruce and I had discussed on the phone
was:
1. Wrap official beta2 tonight, so that other packagers can work from it
over the weekend;
2. Magnus fixes his patch Sunday and applies it then;
3. Dave builds the Windows installer Monday *from CVS tip*.
So the Windows version would be beta2-plus-a-little but we'd neither
hold up work on other platforms nor break anything in buildfarm.
Just an alternative thought. In any case I agree that we want
Windows testing of beta2 to include this patch.
regards, tom lane
Tom Lane wrote:
Dave Page <dpage@postgresql.org> writes:
Magnus Hagander wrote:
I'm leaning towards applying the patch now, and hoping for 2b to happen.
I think we should live with the mingw BF breakage for a day or two. The
patch is clearly an important improvement, but it should be as widely
tested as possible.If Dave is intending to build the Windows installer as soon as beta2 is
wrapped, then I agree with this plan. But my understanding was we'd
missed his window for today and so that wouldn't happen till Monday.
Assuming that's true, the idea Bruce and I had discussed on the phone
was:1. Wrap official beta2 tonight, so that other packagers can work from it
over the weekend;2. Magnus fixes his patch Sunday and applies it then;
3. Dave builds the Windows installer Monday *from CVS tip*.
So the Windows version would be beta2-plus-a-little but we'd neither
hold up work on other platforms nor break anything in buildfarm.Just an alternative thought. In any case I agree that we want
Windows testing of beta2 to include this patch.
If we do that, then what we label as beta2 in the installer is *not* the
same as someone who has built beta2 from source. Can't say I like that
one, and I know Dave doesn't like it (he said so before going offline).
I'd rather see msvc working and mingw broken for beta2 really, because
then we *know* that if someone says they're doing beta2 on mingw they're
misinformed...
//Magnus
Magnus Hagander <magnus@hagander.net> writes:
Attached is an updated version of the patch, currently being tested by
both me and Dave. If it passes our tests, I'll apply this so it gets
included for broader testing in beta2.
One question: what's this about?
+ #define _WIN32_WINNT 0x0500
This looks like it might be tightening our assumptions about which
Windows flavors we can run on. I'm not necessarily against that,
but it should be publicly discussed if it's happening.
regards, tom lane
Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
Attached is an updated version of the patch, currently being tested by
both me and Dave. If it passes our tests, I'll apply this so it gets
included for broader testing in beta2.One question: what's this about?
+ #define _WIN32_WINNT 0x0500
This looks like it might be tightening our assumptions about which
Windows flavors we can run on. I'm not necessarily against that,
but it should be publicly discussed if it's happening.
It enables Windows 2000-specific headers. We already require Windows
2000 to run, so it doesn't restrict us anymore than we already are. It
just exposes those parts of the header files.
//Magnus
Joshua D. Drake wrote:
On Fri, 26 Oct 2007 21:58:03 +0100
Dave Page <dpage@postgresql.org> wrote:Magnus Hagander wrote:
I'm leaning towards applying the patch now, and hoping for 2b to
happen.I think we should live with the mingw BF breakage for a day or two.
The patch is clearly an important improvement, but it should be as
widely tested as possible.I think this makes sense. I certainly don't want to hold back Beta2 and
this patch so far is an obvious improvement.
I read the consensus of this thread as "apply the patch as-is and let's
fix mingw as soon as we can", so this is what I've done. If I got it
wrong, feel free to back out :-)
//Magnus
On 10/26/07, I wrote:
On 10/26/07, Magnus Hagander <magnus@hagander.net> wrote:
Can you try the attached patch? See how many backends you can get up to.
This patch changes from using a single thread for each backend started to
using the builtin threadpool functionality. It also replaces the pid/handle
arrays with an i/o completion port. The net result is also, imho, much more
readable code :-)The patch looks good; I'm not set up to build yet, but I should be
able to test it sometime in the next week.
Sorry about the long delay; I retested with the 8.3-beta2 installer,
still Win2003 SP2 32bit.
I stopped the test at 824 connections because I was about to run out
of memory (1.25GB RAM + 3.75GB swap), but postmaster VM space usage
was only 191MB.
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.
Available RAM seems like a pretty reasonable limit to me ;)
------- Original Message -------
From: "Trevor Talbot" <quension@gmail.com>
To: "Magnus Hagander" <magnus@hagander.net>
Sent: 10/11/07, 23:17:13
Subject: Re: [HACKERS] 8.2.3: Server crashes on Windows using Eclipse/JunitAs for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.
That's interesting (and useful to know) - server is clearly not using nearly as much desktop heap when initialising user32 as XP.
Available RAM seems like a pretty reasonable limit to me ;)
Yup.
/D
Import Notes
Resolved by subject fallback
On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote:
On 10/26/07, I wrote:
On 10/26/07, Magnus Hagander <magnus@hagander.net> wrote:
Can you try the attached patch? See how many backends you can get up to.
This patch changes from using a single thread for each backend started to
using the builtin threadpool functionality. It also replaces the pid/handle
arrays with an i/o completion port. The net result is also, imho, much more
readable code :-)The patch looks good; I'm not set up to build yet, but I should be
able to test it sometime in the next week.Sorry about the long delay; I retested with the 8.3-beta2 installer,
still Win2003 SP2 32bit.I stopped the test at 824 connections because I was about to run out
of memory (1.25GB RAM + 3.75GB swap), but postmaster VM space usage
was only 191MB.
Great.
I'm thinking this change may be big enough to actually backport to 8.2 -
what to others feel about that?
Assuming it is, I still think we should wait at least until we've run 8.3
RC for a while - probably until 8.3 has been actually released and run for
a while, to make sure we have a *lot* of testing of it before we consider
backpatching.
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.
As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?
Dave, on your XP test, was that on a clean XP with nothing like AV or any
3rd party stuff on it?
Available RAM seems like a pretty reasonable limit to me ;)
Yeah, not much we can do about that one :-)
//Magnus
Magnus Hagander wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?Dave, on your XP test, was that on a clean XP with nothing like AV or any
3rd party stuff on it?
No, it was on my XP laptop which runs Sophos AV. I'm not convinced it's
AV related though - in my test code I proved pretty conclusively that
just initialising user32.dll ate the desktop heap.
/D
On Mon, Nov 12, 2007 at 10:01:09AM +0000, Dave Page wrote:
Magnus Hagander wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?Dave, on your XP test, was that on a clean XP with nothing like AV or any
3rd party stuff on it?No, it was on my XP laptop which runs Sophos AV. I'm not convinced it's
AV related though - in my test code I proved pretty conclusively that
just initialising user32.dll ate the desktop heap.
I'm certainly not convinved about that either, but we should make a test on
a VM at some point.
Sophos AV has plugins into for example the explorer (I assume - most AV
does, haven't used Sophos specifically myself), which may be done with
extra DLLs loading along with user32.dll (runtime linked) or something like
that. I just want to be sure we exclude that possibility.
//Magnus
Magnus Hagander wrote:
I'm certainly not convinved about that either, but we should make a test on
a VM at some point.Sophos AV has plugins into for example the explorer (I assume - most AV
does, haven't used Sophos specifically myself), which may be done with
extra DLLs loading along with user32.dll (runtime linked) or something like
that. I just want to be sure we exclude that possibility.
Yeah, iirc it does. I don't have time for that at the moment, but I can
fire you a copy of my test code if you do.
/D
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?
The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per
process, and it's not running anything invasive (AV or otherwise).
I've been trying to find out exactly what's in the desktop heap, but I
haven't had much luck so far. Apparently Microsoft changed the
implementation after Win2000, and didn't bother teaching the public
debugging tools about it. The details just don't seem to exist
anymore :(
On Mon, Nov 12, 2007 at 04:00:04AM -0800, Trevor Talbot wrote:
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per
process, and it's not running anything invasive (AV or otherwise).
Then I think we can claim that Server is just better than Workstation in
this regard. Maybe we should put that in the FAQ?
I've been trying to find out exactly what's in the desktop heap, but I
haven't had much luck so far. Apparently Microsoft changed the
implementation after Win2000, and didn't bother teaching the public
debugging tools about it. The details just don't seem to exist
anymore :(
Yeah, there are very little docs at all about the desktop heap AFAICT.
//Magnus
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Nov 12, 2007 at 04:00:04AM -0800, Trevor Talbot wrote:
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per
process, and it's not running anything invasive (AV or otherwise).Then I think we can claim that Server is just better than Workstation in
this regard. Maybe we should put that in the FAQ?
I think it's safe to claim 2003 is better than XP, but I'm not sure
that's enough to generalize into server vs workstation yet. It
implies 2000 Server would be better than 2000 Pro, which might not be
true. I'm also wondering whether 64bit XP behaves differently, since
IIRC it's based on the 2003 kernel. Then there's Vista...
Unfortunately I don't have access to any of these versions to test
with at the moment.
Trevor Talbot wrote:
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Nov 12, 2007 at 04:00:04AM -0800, Trevor Talbot wrote:
On 11/12/07, Magnus Hagander <magnus@hagander.net> wrote:
On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote:
As for desktop heap, only 65KB of the service heap was allocated, or
about 80 bytes per connection. No danger of hitting limits in the
kernel memory pools either.As Dave said, it could be that the server version uses a lot less heap per
process, which would be another good reason to use server rather than XP to
run postgresql. But might there also be other differences, such as some
third party (or non-core microsoft) product installed?The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per
process, and it's not running anything invasive (AV or otherwise).Then I think we can claim that Server is just better than Workstation in
this regard. Maybe we should put that in the FAQ?I think it's safe to claim 2003 is better than XP, but I'm not sure
that's enough to generalize into server vs workstation yet. It
implies 2000 Server would be better than 2000 Pro, which might not be
true. I'm also wondering whether 64bit XP behaves differently, since
IIRC it's based on the 2003 kernel. Then there's Vista...
Valid points, of course. Specifically, it'd be interesting to know where
Vista stands, and possibly 2008 server. I don't care that much about
2000, really.
I don't have installations of either one, though.. :-(
//Magnus
Hi,
I'm not sure if this is good netiquette, or not. I'm reviving a month-old
thread, because I'm trying to figure out how to resolve the issue.
To summarize: when I run unit tests with eclipse (and with Ant) on
Windows, at some point, I run out of available connections. I tried
increasing the maximum number of connections, but then I started seeing
the postgres server die and restart.
I'm trying to fix this, yet again, but I don't have a clear idea of what
to fix.
On Tue, 23 Oct 2007 20:07:22 +0200, Magnus Hagander wrote:
Rainer Bauer wrote:
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.
Does this allow creating more connections? At some point, the discussion
became too technical for me, and I no longer could tell if the answer was
for developers of for users.
I saw other messages dealing with semaphores/connection relations, etc.
But unless I really did not understand the discussion, none of them seemed
to address the issue I was seeing.
I'm thinking that the Java driver combined with Hibernate may be keeping
handles open for too long, because my tests aren't supposed to maintain
connections open for very long. I also would expect the connections to
either be closed or released once the statements are executed.
This part should go in the FAQ, I think. It's
valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.Dave, you're listed as maintainer :-P
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
--
Prenez la parole en public en étant Speak to an audience while being
moins nerveux et plus convaincant! less nervous and more convincing!
Abonnez-vous au bulletin gratuit! Sign up for the free newsletter!
http://www.duperval.com (514) 902-0186
On 11/29/07, Laurent Duperval <lduperval@yahoo.com> wrote:
To summarize: when I run unit tests with eclipse (and with Ant) on
Windows, at some point, I run out of available connections. I tried
increasing the maximum number of connections, but then I started seeing
the postgres server die and restart.
The conclusion was that under Windows XP, postgres is normally limited
to a maximum of 125-150 connections. Raising max_connections higher
than that will lead to the crashes you saw.
Rainer Bauer wrote:
After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.Alter the value of the following key
<HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows>The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.
Does this allow creating more connections? At some point, the discussion
became too technical for me, and I no longer could tell if the answer was
for developers of for users.
Yes. After making that change and restarting Windows, postgres will be
able to safely handle 250-300 connections.
I saw other messages dealing with semaphores/connection relations, etc.
But unless I really did not understand the discussion, none of them seemed
to address the issue I was seeing.
Right, we were just trying to find the precise resource limit that was
causing the crash.
I'm thinking that the Java driver combined with Hibernate may be keeping
handles open for too long, because my tests aren't supposed to maintain
connections open for very long. I also would expect the connections to
either be closed or released once the statements are executed.
This is where I would start on your problem. Increasing the max
connections is one thing, but having so very many simultaneous
operations in progress on your database is probably not productive, as
it's likely to spend more time juggling tasks than actually performing
them.
I'm not familiar with Java tools, so someone else will have to chime
in with specific suggestions. It may be something as simple as
limiting how many tests JUnit/Ant tries to run at the same time, or
some parameter buried in Hibernate or the driver.
Laurent Duperval wrote:
Does this allow creating more connections? At some point, the discussion
became too technical for me, and I no longer could tell if the answer was
for developers of for users.
Yeah, it did become something of an investigation into the problem which
probably should have been moved to -hackers.
I summarised the info in the FAQ
http://www.postgresql.org/docs/faqs.FAQ_windows.html#4.4 for user
consumption, and included a link to the MS Knowledgebase article that
shows what to tweak in the registry.
I saw other messages dealing with semaphores/connection relations, etc.
But unless I really did not understand the discussion, none of them seemed
to address the issue I was seeing.
Yes, that was all about how we were using threads to manage interprocess
communications. We found a far more efficient way to do that, but my
guess is that thats not your problem.
I'm thinking that the Java driver combined with Hibernate may be keeping
handles open for too long, because my tests aren't supposed to maintain
connections open for very long. I also would expect the connections to
either be closed or released once the statements are executed.
That could be an issue with Hibernate or the other code you're running,
but yes, if it's opening lots of connections and keeping them open that
could be what's wrong and I would suggest checking the FAQ above.
Regards, Dave