Thread: some queries on standby preventing replication updates
Hi,
I have a master and a slave database.
I've got hot_standby_feedback turned on, max_standby_streaming_delay=-1. I've configured the master and slave to keep a few days of WALs around.
I've noticed that when some large queries are run on the standby machine (ones that take more than a minute or so), replication updates are paused. Is there a way to fix this?
Thanks,
Joe
El 23/10/14 a las 17:40, Joe Van Dyk escibió: > Hi, > > I have a master and a slave database. > > I've got hot_standby_feedback turned on, > max_standby_streaming_delay=-1. I've configured the master and slave > to keep a few days of WALs around. > > I've noticed that when some large queries are run on the standby > machine (ones that take more than a minute or so), replication updates > are paused. Is there a way to fix this? > You may need to set a value on max_standby_streaming_delay, which controls the time before cancelling the standby queries when a conflict occurs on a wal-records-about-to-be-applied. Source: http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html -- -- Emanuel Calvo http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo <emanuel.calvo@2ndquadrant.com> wrote:
El 23/10/14 a las 17:40, Joe Van Dyk escibió:You may need to set a value on max_standby_streaming_delay, which> Hi,
>
> I have a master and a slave database.
>
> I've got hot_standby_feedback turned on,
> max_standby_streaming_delay=-1. I've configured the master and slave
> to keep a few days of WALs around.
>
> I've noticed that when some large queries are run on the standby
> machine (ones that take more than a minute or so), replication updates
> are paused. Is there a way to fix this?
>
controls the time
before cancelling the standby queries when a conflict occurs on a
wal-records-about-to-be-applied.
Source:
http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
I'm using -1 for that option, would using something different be better?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 El 28/10/14 a las 15:37, Joe Van Dyk escibió: > On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo < > emanuel.calvo@2ndquadrant.com> wrote: > >> >> El 23/10/14 a las 17:40, Joe Van Dyk escibió: >>> Hi, >>> >>> I have a master and a slave database. >>> >>> I've got hot_standby_feedback turned on, >>> max_standby_streaming_delay=-1. I've configured the master and >>> slave to keep a few days of WALs around. >>> >>> I've noticed that when some large queries are run on the >>> standby machine (ones that take more than a minute or so), >>> replication updates are paused. Is there a way to fix this? >>> >> You may need to set a value on max_standby_streaming_delay, which >> controls the time before cancelling the standby queries when a >> conflict occurs on a wal-records-about-to-be-applied. >> >> Source: >> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html > >> >> > > I'm using -1 for that option, would using something different be > better? > Actually that means it will wait that the queries run forever. You should think which is your maximum threshold regarding query duration when conflicting with writes. - -- - -- Emanuel Calvo http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services Bs. As., Argentina (GMT-3) -----BEGIN PGP SIGNATURE----- Version: GnuPG/MacGPG2 v2.0.18 (Darwin) Comment: GPGTools - http://gpgtools.org iQIcBAEBCgAGBQJUT+Z7AAoJEIBeI/HMagHmBjwQAKpLJBXzaeRy1zf/fOYR0sce joaIcyKXIJSwV9Eikc2LPbskfFVnZFoCdSfxL5/JmPNwBgramwinzoosDl/QbLwU sY1OvTbP7BBMiKGQyLR2zmqHtKBJ+BI92zLSay8qTjOWfZARakMH9CMNK+zqj2OZ ZTIBa5mtDsrmueWHchjtOx+hNsXfMKo1La2qPz3UIoAns1ti+ZtFDObP6kkYzhhL 4kpSxtHjksG0Rdp7drVs0LC2NgPl71Fy9BznNHAL53LGec1IniS4fkSQSo7XQKNc XZzq13VDLWFvyaNQ9eFlYujJlEwTKE3c7ygATjLodVAlz4UcY7mBXQOfAxUMKMsh EgjAfETdy2vE3qgSV0FcPnt/mdgcGjfBg6wqn/JhMuuk7MUovmu96vJ98Ksqm2sH yYx3bikaHUkE+OL0zfWUGYsIYuqMFTBmnEepvoXW/O6IeAv+b6qzTWtN4lP4K+s1 P5OI/SN/2shuAAvVr1Vhmc3cXYSWKIuu9c8MbTviULwiARl727m6oDU2vqiK8dGa KgdPsLHkiuxINouu86auE4fbeK68bYIM8fCK19meCLI50L0v1WLbfW+OKIvYnO8K tT3dE3yejjmWBj00UxQ2PD+3yeLySs6WJrGdYhzY9HtgWocrY3HLDtv5J8+ZB4ww EbYXrR2Yl5UKGjIlGfu+ =nIRk -----END PGP SIGNATURE-----
Joe Van Dyk wrote > On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo < > emanuel.calvo@ >> wrote: > >> >> El 23/10/14 a las 17:40, Joe Van Dyk escibió: >> > Hi, >> > >> > I have a master and a slave database. >> > >> > I've got hot_standby_feedback turned on, >> > max_standby_streaming_delay=-1. I've configured the master and slave >> > to keep a few days of WALs around. >> > >> > I've noticed that when some large queries are run on the standby >> > machine (ones that take more than a minute or so), replication updates >> > are paused. Is there a way to fix this? >> > >> You may need to set a value on max_standby_streaming_delay, which >> controls the time >> before cancelling the standby queries when a conflict occurs on a >> wal-records-about-to-be-applied. >> >> Source: >> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html > > I'm using -1 for that option, would using something different be better? Why did you choose -1? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/some-queries-on-standby-preventing-replication-updates-tp5824090p5824675.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 28/10/14 19:37, Joe Van Dyk wrote: > On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo > <emanuel.calvo@2ndquadrant.com <mailto:emanuel.calvo@2ndquadrant.com>> > wrote: > > > El 23/10/14 a las 17:40, Joe Van Dyk escibió: > > Hi, > > > > I have a master and a slave database. > > > > I've got hot_standby_feedback turned on, > > max_standby_streaming_delay=-1. I've configured the master and slave > > to keep a few days of WALs around. > > > > I've noticed that when some large queries are run on the standby > > machine (ones that take more than a minute or so), replication updates > > are paused. Is there a way to fix this? > > > You may need to set a value on max_standby_streaming_delay, which > controls the time > before cancelling the standby queries when a conflict occurs on a > wal-records-about-to-be-applied. > > Source: > http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html > > > I'm using -1 for that option, would using something different be better? It depends on what you want to achieve. If you want to sacrifice your long-running query to keep replication going, set the value to >0. If you (like me) are using the slave to run analytical queries that can take many hours or even days, I'd rather live with the current behaviour. When the long-running query is over the wal receiver automatically reconnects to the master. The only thing you should make sure is to keep enough wal segments. With 9.4 even that gets easier. There you can assign a replication slot to the replica and the master then knows which segments are still needed when the slave reconnects. Torsten