Обсуждение: Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
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
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.
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
* 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).
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
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 <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
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
Вложения
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.
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 ;)
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