Обсуждение: BUG #5851: ROHS (read only hot standby) needs to be restarted manually in somecases.
The following bug has been logged online: Bug reference: 5851 Logged by: Mark Email address: dvlhntr@gmail.com PostgreSQL version: 9.0.2 x86_64 Operating system: CentOS release 5.5 (Final) | 2.6.18-194.17.1.el5 #1 SMP X86_64 Description: ROHS (read only hot standby) needs to be restarted manually in somecases. Details: getting a break down in streaming rep. my current work around is to restart the PG instance on the ROHS. doesn't seem to affect the master any. doesn't require a re-rsync of the base to get replication going again. has happened with 9.0.2 twice now in a month. 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: could not receive data from client: Connection reset by peer 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: unexpected EOF on standby connection this was all I have in the master's log with the level set to debug 1, I have reset it to debug 5 and will just wait till it dies again and hopefully get a better idea of what is going on. nothing is being logged to the standby. I can't find anything else to grab that shows this break down in streaming rep that won't start back up. This is a somewhat *long* distance replication over a 100mbit metro line. we have had routing issues in the past and see replication fall behind but once connectivity is restored we see it catch up, without a restart of the standby. probably only ships a few gig of changes a day. these are production machines so I can't do too much playing around to try and induce "issues" PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit (1 row) is this a known issue ? I didn't see anything when I have the mailing list archive a quick glance search that looked like this. is there somewhere else I should be looking for more details into why this is happening ? I can post the configs if you all want them but nothing special is happening w/ regards to them. thank you, Mark
On Wed, Jan 26, 2011 at 8:24 PM, Mark <dvlhntr@gmail.com> wrote: > getting a break down in streaming rep. my current work around is to resta= rt > the PG instance on the ROHS. doesn't seem to affect the master any. doesn= 't > require a re-rsync of the base to get replication going again. has happen= ed > with 9.0.2 twice now in a month. > > > > 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: =A0could not receive > data > from client: Connection reset by peer > 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: =A0unexpected EOF on > standby connection > > this was all I have in the master's log with the level set to debug 1, I > have reset it to debug 5 and will just wait till it dies again and hopefu= lly > get a better idea of what is going on. nothing is being logged to the > standby. Maybe a break in network connectivity is leading the master to think that the slave is dead, while the slave still thinks it's connected. You might need to adjust the TCP keepalive parameters the slave uses to connect to the master. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
When showing the setting on the slave or master all tcp_keepalive settings (idle, interval and count) are showing 0; The config file shows interval and count commented out, but idle in the config file is set to 2100.=20 Possible that "show tcp_keepalive_idle;" isn't reporting accurately ? (or a value that high isn't be accepted?) I have reloaded configs and still seeing 0's I assume you would suggest I turn that number down... a lot.=20 ..: Mark > -----Original Message----- > From: Robert Haas [mailto:robertmhaas@gmail.com] > Sent: Friday, January 28, 2011 6:48 AM > To: Mark > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5851: ROHS (read only hot standby) needs to be > restarted manually in somecases. >=20 > On Wed, Jan 26, 2011 at 8:24 PM, Mark <dvlhntr@gmail.com> wrote: > > getting a break down in streaming rep. my current work around is to > restart > > the PG instance on the ROHS. doesn't seem to affect the master any. > doesn't > > require a re-rsync of the base to get replication going again. has > happened > > with 9.0.2 twice now in a month. > > > > > > > > 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: =A0could not > receive > > data > > from client: Connection reset by peer > > 2011-01-26 08:35:42 MST :: (postgres@10.80.2.89) LOG: =A0unexpected EOF > on > > standby connection > > > > this was all I have in the master's log with the level set to debug > 1, I > > have reset it to debug 5 and will just wait till it dies again and > hopefully > > get a better idea of what is going on. nothing is being logged to the > > standby. >=20 > Maybe a break in network connectivity is leading the master to think > that the slave is dead, while the slave still thinks it's connected. > You might need to adjust the TCP keepalive parameters the slave uses > to connect to the master. >=20 > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
On Fri, Jan 28, 2011 at 1:03 PM, mark <dvlhntr@gmail.com> wrote: > When showing the setting on the slave or master all tcp_keepalive settings > (idle, interval and count) are showing 0; > > The config file shows interval and count commented out, but idle in the > config file is set to 2100. > > Possible that "show tcp_keepalive_idle;" isn't reporting accurately ? (or a > value that high isn't be accepted?) > > I have reloaded configs and still seeing 0's > > > > I assume you would suggest I turn that number down... a lot. Yeah, the defaults are way too long for our purposes. The way to get this set correctly, I think, is to set it in the primary_conninfo stream on the slave. You end up with something like this: primary_conninfo='host=blahblah user=bob keepalives_idle=XX keepalives_interval=XX keepalives_count=XX' I'm of the opinion that we really need an application-level keepalive here, but the above is certainly a lot better than nothing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> -----Original Message----- > From: Robert Haas [mailto:robertmhaas@gmail.com] > Sent: Sunday, January 30, 2011 12:19 PM > To: mark > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5851: ROHS (read only hot standby) needs to be > restarted manually in somecases. > > On Fri, Jan 28, 2011 at 1:03 PM, mark <dvlhntr@gmail.com> wrote: > > When showing the setting on the slave or master all tcp_keepalive > settings > > (idle, interval and count) are showing 0; > > > > The config file shows interval and count commented out, but idle in > the > > config file is set to 2100. > > > > Possible that "show tcp_keepalive_idle;" isn't reporting accurately ? > (or a > > value that high isn't be accepted?) > > > > I have reloaded configs and still seeing 0's > > > > > > > > I assume you would suggest I turn that number down... a lot. > > Yeah, the defaults are way too long for our purposes. The way to get > this set correctly, I think, is to set it in the primary_conninfo > stream on the slave. You end up with something like this: > > primary_conninfo='host=blahblah user=bob keepalives_idle=XX > keepalives_interval=XX keepalives_count=XX' > Thanks I will try this on Monday and will report back if it fixes the problem. (however since I can't reproduce the issue on demand it might be a waiting game. Might not know for a month or so tho) -Mark > I'm of the opinion that we really need an application-level keepalive > here, but the above is certainly a lot better than nothing. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
On Sun, Jan 30, 2011 at 12:45 PM, mark <dvlhntr@gmail.com> wrote: > > >> -----Original Message----- >> From: Robert Haas [mailto:robertmhaas@gmail.com] >> Sent: Sunday, January 30, 2011 12:19 PM >> To: mark >> Cc: pgsql-bugs@postgresql.org >> Subject: Re: [BUGS] BUG #5851: ROHS (read only hot standby) needs to be >> restarted manually in somecases. >> >> On Fri, Jan 28, 2011 at 1:03 PM, mark <dvlhntr@gmail.com> wrote: >> > When showing the setting on the slave or master all tcp_keepalive >> settings >> > (idle, interval and count) are showing 0; >> > >> > The config file shows interval and count commented out, but idle in >> the >> > config file is set to 2100. >> > >> > Possible that "show tcp_keepalive_idle;" isn't reporting accurately ? >> (or a >> > value that high isn't be accepted?) >> > >> > I have reloaded configs and still seeing 0's >> > >> > >> > >> > I assume you would suggest I turn that number down... a lot. >> >> Yeah, the defaults are way too long for our purposes. =A0The way to get >> this set correctly, I think, is to set it in the primary_conninfo >> stream on the slave. =A0You end up with something like this: >> >> primary_conninfo=3D'host=3Dblahblah user=3Dbob keepalives_idle=3DXX >> keepalives_interval=3DXX keepalives_count=3DXX' >> > Thanks I will try this on Monday and will report back if it fixes the > problem. (however since I can't reproduce the issue on demand it might be= a > waiting game. Might not know for a month or so tho) > > -Mark > > >> I'm of the opinion that we really need an application-level keepalive >> here, but the above is certainly a lot better than nothing. my streaming replication woes continue. I made those changes in the recovery.conf file but I am still having streaming replication stay broken after any sort of network interruption until someone manaully comes along and fixes things by restarting the standby or if it's been too long resynchronizing the base. I think it's a network interruption that is triggering the break down, but I don't have anything to prove it. wal_keep_segments are set to 250, which was supposed to give us a few hours to fix the issue but it seems we blew through that many last night and such when someone got around to fixing it the standby was too far behind. my #1 problem with this right now is I can't seem to reproduce on demand with virtual machines in our development area. this is the recovery.conf file, see any problems with it? maybe I didn't do some syntax right right ? [postgres@<redacted> data9.0]$ cat recovery.conf standby_mode =3D 'on' primary_conninfo =3D 'host=3D<redacted> port=3D5432 user=3Dpostgres keepalives_idle=3D30 keepalives_interval=3D30 keepalives_count=3D30' thanks ..: Mark p.s. looking forward to 9.1 where a standby can be started with streaming from scratch. that sounds nice. >> >> -- >> Robert Haas >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company > >
On Wed, Feb 9, 2011 at 6:36 AM, mark <dvlhntr@gmail.com> wrote: > this is the recovery.conf file, see any problems with it? maybe I > didn't do some syntax right right ? > > [postgres@<redacted> data9.0]$ cat recovery.conf > standby_mode = 'on' > primary_conninfo = 'host=<redacted> port=5432 user=postgres > keepalives_idle=30 keepalives_interval=30 keepalives_count=30' This setting would lead TCP keepalive to take about 930 seconds (= 30 + 30 * 30) to detect the network outage. If you want to stop replication as soon as the outage happens, you need to decrease the keepalive setting values. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
> -----Original Message----- > From: Fujii Masao [mailto:masao.fujii@gmail.com] > Sent: Tuesday, February 08, 2011 4:00 PM > To: mark > Cc: Robert Haas; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5851: ROHS (read only hot standby) needs to be > restarted manually in somecases. > > On Wed, Feb 9, 2011 at 6:36 AM, mark <dvlhntr@gmail.com> wrote: > > this is the recovery.conf file, see any problems with it? maybe I > > didn't do some syntax right right ? > > > > [postgres@<redacted> data9.0]$ cat recovery.conf > > standby_mode = 'on' > > primary_conninfo = 'host=<redacted> port=5432 user=postgres > > keepalives_idle=30 keepalives_interval=30 keepalives_count=30' > > This setting would lead TCP keepalive to take about 930 seconds > (= 30 + 30 * 30) to detect the network outage. If you want to stop > replication as soon as the outage happens, you need to decrease > the keepalive setting values. What numbers would you suggest? I have been guessing and probably doing a very poor job of it. I am turning knobs and not getting any meaningful changes with respect to in my problem. So either I am not turning them correctly, or they are not the right knobs for my problem. Trying to fix my own ignorance here. (should I move this off the bugs list, since maybe it's not a bug?) The settings have been unspecified in the recovery file, it's been specified in the recovery file, and I have tried the following in the recovery file: (~two weeks and it died) keepalives_idle=0 keepalives_interval=0 keepalives_count=0 (~two weeks and it dies) keepalives_idle=30 keepalives_interval=30 keepalives_count=30 (this didn't work either, don't recall how long this lasted, maybe a month) keepalives_idle=2100 keepalives_interval=0 keepalives_count=0 Background is basically this: trying to do streaming replication over a WAN, probably ship about 5GB of changes per day, hardware on both ends can easily keep up with that. Running over a shared metro line and have about 3-5MBytes per second depending on the time of day that I can count on. I have wal_keep segments at 250 (I don't care about the disk overhead for this, since I wanted to not have to use wal archiving). The link is being severed more often than usually lately while some network changes are being made so while I would expect that improve in the future this isn't exactly the most reliable connection. so getting whatever as right as I can is of value to me. Typically I see the streaming replication break down for good completely a few hours after something that causes a interruption in networking. Nagios notifications lag some but not hours and has to go through a few people before I find out about it. When checking the nagios pages on their logs I don't see pages about the distance between the master and the standby getting bigger during this time, and then once I see the first unexpected EOF then the distance between the master and standby gets further and further until it gets fixed or we have to re-sync the whole base over. Again I can't seem to duplicate this problem on demand with virtual machines, I startup a master and standby, setup streaming rep, kickoff a multi hour or day pg bench run and start messing with networking. Every time I try and duplicate this synthetically the standby picks right back where it left off and catches back up. I am at a loss so I do appreciate everyone's help. Thanks in advance -Mark > > Regards, > > -- > Fujii Masao > NIPPON TELEGRAPH AND TELEPHONE CORPORATION > NTT Open Source Software Center
On Tue, Feb 8, 2011 at 7:23 PM, mark <dvlhntr@gmail.com> wrote: > (~two weeks and it dies) > keepalives_idle=30 > keepalives_interval=30 > keepalives_count=30 Maybe something like this: keepalives_idle=60 keepalives_interval=5 keepalives_count=10 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> -----Original Message----- > From: Robert Haas [mailto:robertmhaas@gmail.com] > Sent: Thursday, March 03, 2011 9:04 AM > To: mark > Cc: Fujii Masao; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5851: ROHS (read only hot standby) needs to be > restarted manually in somecases. > > On Tue, Feb 8, 2011 at 7:23 PM, mark <dvlhntr@gmail.com> wrote: > > (~two weeks and it dies) > > keepalives_idle=30 > > keepalives_interval=30 > > keepalives_count=30 > > Maybe something like this: > > keepalives_idle=60 > keepalives_interval=5 > keepalives_count=10 > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company Thank you ! Things have been more stable with regards to our WAN links. I am sure that won't last for very long and expect to find out sooner than later if these changes mitigate the issue I had been seeing. Again, thank you ~Mark