Обсуждение: Does max_standby_streaming_delay delay all WAL commits?
We're running into issues with canceled queries on a standby server and have be debating between enabling hot_standby_feedback and setting max_standby_streaming_delay to something large.
Using max_standby_streaming_delay seems to have the advantage of keeping the master independent of the standby, but will a long running query mean that all WAL commits are delayed? Or just those that affected by the long running query?
Thanks,Using max_standby_streaming_delay seems to have the advantage of keeping the master independent of the standby, but will a long running query mean that all WAL commits are delayed? Or just those that affected by the long running query?
On Fri, Mar 4, 2016 at 2:43 AM, Dave Johansen <davejohansen@gmail.com> wrote:
If you set max_standby_streaming_delay to a large value, WAL commits are going to continue until there is a conflict with the running queries on standby. If a WAL commit encounters a conflict, then the WAL update on standby will wait for "max_standby_streaming_delay" amount of time and until then all the subsequent WAL commits will be paused. In otherwords - entire standby recovery will be paused.We're running into issues with canceled queries on a standby server and have be debating between enabling hot_standby_feedback and setting max_standby_streaming_delay to something large.
Using max_standby_streaming_delay seems to have the advantage of keeping the master independent of the standby, but will a long running query mean that all WAL commits are delayed? Or just those that affected by the long running query?
Another alternative would be use "pg_xlog_replay_pause()" and "pg_xlog_replay_resume()" functions if that is possible with your application. Execute the "pg_xlog_replay_pause()" - then the query/job - then "pg_xlog_replay_resume()" function.
Regards,
Venkata B N
Fujitsu Australia
On Thu, Mar 3, 2016 at 8:15 PM, Venkata Balaji N <nag1010@gmail.com> wrote:
On Fri, Mar 4, 2016 at 2:43 AM, Dave Johansen <davejohansen@gmail.com> wrote:If you set max_standby_streaming_delay to a large value, WAL commits are going to continue until there is a conflict with the running queries on standby. If a WAL commit encounters a conflict, then the WAL update on standby will wait for "max_standby_streaming_delay" amount of time and until then all the subsequent WAL commits will be paused. In otherwords - entire standby recovery will be paused.We're running into issues with canceled queries on a standby server and have be debating between enabling hot_standby_feedback and setting max_standby_streaming_delay to something large.
Using max_standby_streaming_delay seems to have the advantage of keeping the master independent of the standby, but will a long running query mean that all WAL commits are delayed? Or just those that affected by the long running query?
That's very helpful and based on that, I think we'll go with hot_standby_feedback.
Another alternative would be use "pg_xlog_replay_pause()" and "pg_xlog_replay_resume()" functions if that is possible with your application. Execute the "pg_xlog_replay_pause()" - then the query/job - then "pg_xlog_replay_resume()" function.
Unfortunately, that's not an option for our application.
Thanks,
Dave