Re: [PATCHES] pg_autovacuum Win32 Service startup delay
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 24 January 2005 23:58 To: Dave Page Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] pg_autovacuum Win32 Service startup delay Dave Page dpage@vale-housing.co.uk writes: When starting as a service at boot time on Windows, pg_autovacuum may fail to start because the PostgreSQL service is still starting up. This patch causes the service to attempt a second connection 30 seconds after the initial connection failure before giving up entirely. Hm. In event that the system crashed beforehand, it could require much more than 30 seconds to finish replaying the old WAL log. So the above doesn't seem super robust to me. Would it be reasonable to try every 30 seconds for five minutes, or some such? (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) OK, revised patch attached. This version tries every 30 seconds for 5 minutes then gives up. Regards, Dave. startup_delay.diff Description: startup_delay.diff ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Matthew T. O'Connor schrieb: In the windows service world, is there any reason pg_autovacuum should ever give up? The reason I had it give up was so that it didn't accidently run against a different postgresql instance. I don't think that will happen in the windows service world. I think it should keep trying to do it's job until it's told to exit. A never giving up pg_autovacuum seems a little bit rude to me. It's like the salesman who keeps trying to sell me something I have clearly no use. Especially if in setting up og_autovacuum sth. goes wrong: wrong user, wrong password. Service keeps running, service keeps using ressources, seems perfectly normal... but: nothing happens. (and if everything looks perfect, checking the logs is not the first you do, do you?) So: I think a reasonable compromise is to keep pg_autovacuum trying for some time (maybe 5 minutes as Tom recommended) and after that give up. Harald begin:vcard fn:Harald Armin Massa n:Massa;Harald Armin org:GHUM Harald Massa adr;quoted-printable;quoted-printable:;;Reinsburgstra=C3=9Fe 202b;Stuttgart;Baden-W=C3=BCrttemberg;70197;Deutschland email;internet:[EMAIL PROTECTED] title:Chef tel;work:0711/6587982 tel;home:0711/6571322 tel;cell:0173/9409607 x-mozilla-html:FALSE url:http://www.ghum.de version:2.1 end:vcard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: In the windows service world, is there any reason pg_autovacuum should ever give up? I was a bit worried about the scenario in which J Random Luser tries to start the server twice and ends up with two autovacuum daemons attached to the same postmaster. I'm not sure if this is possible, probable, or dangerous ... but it seems like a point to consider. It is a good point to consider. Let me be a little more detailed in my explanation and see if that helps: * A never give up pg_autovacuum would only be used when run as a windows service. * The windows service control manager can still kill pg_autovacuum, so you shouldn't be able to start more than one that way. * You have always been able to run multiple pg_autovacuums, it's not advisable, and it's only bad side effect would be excessive, or more than expected, vacuum commands. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Matthew T. O'Connor matthew@zeut.net writes: Tom Lane wrote: I was a bit worried about the scenario in which J Random Luser tries to start the server twice and ends up with two autovacuum daemons attached to the same postmaster. I'm not sure if this is possible, probable, or dangerous ... but it seems like a point to consider. It is a good point to consider. Let me be a little more detailed in my explanation and see if that helps: * A never give up pg_autovacuum would only be used when run as a windows service. * The windows service control manager can still kill pg_autovacuum, so you shouldn't be able to start more than one that way. * You have always been able to run multiple pg_autovacuums, it's not advisable, and it's only bad side effect would be excessive, or more than expected, vacuum commands. OK, that seems to take care of my worries above. I agree with the point someone else made that if the service keeps trying to start forever, it wouldn't be obvious to the user that it wasn't working. So a limited time window seems best ... but I think it needs to be at least five minutes. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] pg_autovacuum Win32 Service startup delay
When starting as a service at boot time on Windows, pg_autovacuum may fail to start because the PostgreSQL service is still starting up. This patch causes the service to attempt a second connection 30 seconds after the initial connection failure before giving up entirely. Regards, Dave startup_delay.diff Description: startup_delay.diff ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Dave Page dpage@vale-housing.co.uk writes: When starting as a service at boot time on Windows, pg_autovacuum may fail to start because the PostgreSQL service is still starting up. This patch causes the service to attempt a second connection 30 seconds after the initial connection failure before giving up entirely. Hm. In event that the system crashed beforehand, it could require much more than 30 seconds to finish replaying the old WAL log. So the above doesn't seem super robust to me. Would it be reasonable to try every 30 seconds for five minutes, or some such? (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Jan 24, 2005 at 06:57:54PM -0500, Tom Lane wrote: (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) Hmm, I remember Mark Wong from OSDL saying that it took to replay the logs after a crash more than the six hours it had taken to generate them. Six hours? Did he have checkpoints disabled somehow? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
On Mon, Jan 24, 2005 at 06:57:54PM -0500, Tom Lane wrote: (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) Hmm, I remember Mark Wong from OSDL saying that it took to replay the logs after a crash more than the six hours it had taken to generate them. Simon commented that it was unexpected, but there was no further comment on the issue. (On his test the server is generating the logs as fast as it can, so it may not be important, but anyway ... ) -- Alvaro Herrera ([EMAIL PROTECTED]) Ciencias políticas es la ciencia de entender por qué los políticos actúan como lo hacen (netfunny.com) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Dave Page wrote: When starting as a service at boot time on Windows, pg_autovacuum may fail to start because the PostgreSQL service is still starting up. This patch causes the service to attempt a second connection 30 seconds after the initial connection failure before giving up entirely. In the windows service world, is there any reason pg_autovacuum should ever give up? The reason I had it give up was so that it didn't accidently run against a different postgresql instance. I don't think that will happen in the windows service world. I think it should keep trying to do it's job until it's told to exit. Matthew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Jan 24, 2005 at 06:57:54PM -0500, Tom Lane wrote: (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) Hmm, I remember Mark Wong from OSDL saying that it took to replay the logs after a crash more than the six hours it had taken to generate them. Six hours? Did he have checkpoints disabled somehow? No, I remember they were talking about recovery from backup using PITR. (i.e. not simple crash recovery, but replaying the logs from the whole benchmark session) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Matthew T. O'Connor matthew@zeut.net writes: In the windows service world, is there any reason pg_autovacuum should ever give up? I was a bit worried about the scenario in which J Random Luser tries to start the server twice and ends up with two autovacuum daemons attached to the same postmaster. I'm not sure if this is possible, probable, or dangerous ... but it seems like a point to consider. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match