Re: [GENERAL] hot_standby_feedback
On 2016-11-28 22:14:55 +0100, Torsten Förtsch wrote: > Hi, > > I am in the process of reviewing our configs for a number of 9.3 databases > and found a replica with hot_standby_feedback=on. I remember when we set it > long ago we were fighting cancelled queries. I also remember that it never > really worked for us. In the end we set up 2 replicas, one suitable for > short queries where we prefer low replication lag, and another one where we > allow for long running queries but sacrifice timeliness > (max_standby_*_delay=-1). There's a few kind of conflicts against which hs_feedback doesn't protect. E.g. exclusive locks on tables that are in use and such (e.g. by vacuum truncating a table or an explicit drop table). There's a table with some information about the causes of cancellations, pg_stat_database_conflicts - did you check that? > I have a hunch why hot_standby_feedback=on didn't work. But I never > verified it. So, here it is. The key is this sentence: > > "Feedback messages will not be sent more frequently than once per > wal_receiver_status_interval." > > That interval is 10 sec. So, assuming a transaction on the replica uses a > row right after the message has been sent. Then there is a 10 sec window in > which the master cannot know that the row is needed on the replica and can > vacuum it. If then the transaction on the replica takes longer than > max_standby_*_delay, the only option is to cancel it. > > Is that explanation correct? No. That just means that we don't update the value more frequently. The value reported is a "horizon" meaning that nothing older than the reported value can be accessed. Greetings, Andres Freund -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hot_standby_feedback
Hi, I am in the process of reviewing our configs for a number of 9.3 databases and found a replica with hot_standby_feedback=on. I remember when we set it long ago we were fighting cancelled queries. I also remember that it never really worked for us. In the end we set up 2 replicas, one suitable for short queries where we prefer low replication lag, and another one where we allow for long running queries but sacrifice timeliness (max_standby_*_delay=-1). I have a hunch why hot_standby_feedback=on didn't work. But I never verified it. So, here it is. The key is this sentence: "Feedback messages will not be sent more frequently than once per wal_receiver_status_interval." That interval is 10 sec. So, assuming a transaction on the replica uses a row right after the message has been sent. Then there is a 10 sec window in which the master cannot know that the row is needed on the replica and can vacuum it. If then the transaction on the replica takes longer than max_standby_*_delay, the only option is to cancel it. Is that explanation correct? What is the correct way to use hot_standby_feedback to prevent cancellations reliably? (and accepting the bloat) Thanks, Torsten
Re: [GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Thank you for the guidance! So far so good with max_standby_archive/streaming_delay, no cancellations.
Re: [GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Steve Kehlet wrote: > Our queries on our Standby are getting cancelled and so we're investigating > how to prevent this. The standby is for running periodic reporting queries > only, we don't care if it falls behind a little bit, we just set this guy > up to reduce the load on the Primary. > > While researching there appear to be several different parameters that can > help solve this. It sounds like we don't need hot_standby_feedback or > vacuum_defer_cleanup_age, we really just want to pause things on the > standby to let it run its queries. So we're going to try > applying max_standby_archive_delay and max_standby_streaming_delay to 1h or > so. We're also looking at pg_xlog_replay_pause(), although this is less > desirable because we don't want to have to ask the people running reports > to remember to pause and resume things. Note that not all standby cancellations are the same. For instance if you're grabbing access exclusive locks on the master and the salve is, say, taking pg_dump for a long time, no amount of standby feedback or vacuum deferring will save you. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
On 8/21/2014 1:44 PM, Steve Kehlet wrote: Our queries on our Standby are getting cancelled and so we're investigating how to prevent this. The standby is for running periodic reporting queries only, we don't care if it falls behind a little bit, we just set this guy up to reduce the load on the Primary. While researching there appear to be several different parameters that can help solve this. It sounds like we don't need hot_standby_feedback or vacuum_defer_cleanup_age, we really just want to pause things on the standby to let it run its queries. So we're going to try applying max_standby_archive_delay and max_standby_streaming_delay to 1h or so. We're also looking at pg_xlog_replay_pause(), although this is less desirable because we don't want to have to ask the people running reports to remember to pause and resume things. Can anyone confirm we're on the right track or provide further guidance? Thanks so much. you will need sufficient wal archiving and/or wal_keep_segments on the server to cover the worst case period that the slave will get behind due to pause or whatever. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
On 08/21/2014 03:44 PM, Steve Kehlet wrote: So we're going to try applying max_standby_archive_delay and max_standby_streaming_delay to 1h or so. We're also looking at pg_xlog_replay_pause(), although this is less desirable because we don't want to have to ask the people running reports to remember to pause and resume things. Looks right to me. Though with these settings, keep in mind that these are not on a per-query basis. You will have queries canceled that may have only been running for a few seconds, if other queries have caused a long cumulative delay. You can fiddle with these until you stop getting cancellations though. Especially if, as you say, it's a slave that can fall behind safely. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Our queries on our Standby are getting cancelled and so we're investigating how to prevent this. The standby is for running periodic reporting queries only, we don't care if it falls behind a little bit, we just set this guy up to reduce the load on the Primary. While researching there appear to be several different parameters that can help solve this. It sounds like we don't need hot_standby_feedback or vacuum_defer_cleanup_age, we really just want to pause things on the standby to let it run its queries. So we're going to try applying max_standby_archive_delay and max_standby_streaming_delay to 1h or so. We're also looking at pg_xlog_replay_pause(), although this is less desirable because we don't want to have to ask the people running reports to remember to pause and resume things. Can anyone confirm we're on the right track or provide further guidance? Thanks so much.
Re: [GENERAL] hot_standby_feedback
On Thu, Aug 29, 2013 at 2:44 PM, Tatsuo Ishii wrote: > I have a question about hot_standby_feedback parameter. In my > understanding, if this parameter is on, a long running transaction on > standby will not be canceled even if the transaction conflicts. > As you can see vacuum on the primary removes all the rows in t1. I > thought vacuum will not make the page entriely empty because > of the effect of hot_standby_feedback. > > After while, on standby: > test=# select * from t1; > FATAL: terminating connection due to conflict with recovery > DETAIL: User was holding a relation lock for too long. > HINT: In a moment you should be able to reconnect to the database and repeat > your command. > > Again, this is not what I expected. Am I missing something? I also expected this behavior, and suffer similar problems with PostgreSQL 9.1. I reported this as BUG #7546 with a test case and raised it a few times on the mailing lists, but am no closer to a solution. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hot_standby_feedback
I have a question about hot_standby_feedback parameter. In my understanding, if this parameter is on, a long running transaction on standby will not be canceled even if the transaction conflicts. So I have primary PostgreSQL and standby PostgreSQL running 9.2.4. On primary: create table t1(i int); insert into t1 values(1),(2),(3); On standby: begin; select * from t1; i --- 1 2 3 (3 rows) On primary: delete from t1; On standby: select * from t1; i --- (0 rows) On primary: vacuum verbose t1; INFO: vacuuming "public.t1" INFO: "t1": removed 3 row versions in 1 pages INFO: "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "t1": truncated 1 to 0 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM As you can see vacuum on the primary removes all the rows in t1. I thought vacuum will not make the page entriely empty because of the effect of hot_standby_feedback. After while, on standby: test=# select * from t1; FATAL: terminating connection due to conflict with recovery DETAIL: User was holding a relation lock for too long. HINT: In a moment you should be able to reconnect to the database and repeat your command. Again, this is not what I expected. Am I missing something? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
