pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Question on Hot Standby in PostgreSQL
7+ messages / 4 participants
[nested] [flat]
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-20 19:50 Williams, Alex <awilliams@teamdrg.com>
0 siblings, 1 reply; 7+ messages in thread
From: Williams, Alex @ 2015-11-20 19:50 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a question concerning using the Postgres Hot Standby feature. We
are currently using that feature to sync any changes from one master to
one slave. The slave role is as a reporting db server with queries
constantly and concurrently running (some in ms, some in seconds,
some in minutes,)
We have adjusted these two params to allow long queries on the hot standby:
[http://www.postgresql.org/docs/current/static/hot-standby.html]
max_standby_archive_delay = -1 # max delay before canceling queries
max_standby_streaming_delay = -1 # max delay before canceling
queries
And looking at an archived mail question similar to ours:
http://www.postgresql.org/message-id/AANLkTinLg+bpzcjzdndsnGGNFC=D1OsVh+hKb
85A-s=n@mail.gmail.com<mailto:85A-s=n@mail.gmail.com>
response:
http://www.postgresql.org/message-id/201102272005.00234.jens@wilke.org
It looks like WAL updates cannot be applied while queries are running.
I've tested that to be true. In our case, the reporting server has a
good chance of not being synced for hours or possible longer because
of all the queries that are running. Is it possible to run long running
queries while having WAL updates applied to the slave?
I understand the concept of preventing WAL updates not being applied to
slaves while queries are running. However, I thought with the use of MVCC,
an active query on the slave (long running, 30 seconds+) can run reading
from row xid versions/snapshot, while the WAL update is being applied, so
subsequent queries will get the WAL updates when that WAL transaction is
committed. I haven't fully digested the MVCC model used in PostgreSQL yet
[http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html and
https://devcenter.heroku.com/articles/postgresql-concurrency], so this is
just my assumption -- that even if a table is dropped / truncated during a
WAL update, the current running query should still work as it's using a
version/snapshot of the table(s) it's querying?
Is there any way (even with a third party extension) we can sync slaves
from a master and have those updates from the master be applied to the
slave right away while letting queries of any execution time continue to
run till they complete on the standby/slave? If Hot Standby can't do that,
what would you recommend for this situation? Our scenario is that we are
hitting postgres with queries constantly and concurrently running (some in
ms, some in seconds, some in minutes,) leaving almost no time for a WAL
update to be applied.
Any help would be greatly appreciated.
Thanks,
Alex
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-23 20:24 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Williams, Alex <awilliams@teamdrg.com>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Ribe @ 2015-11-23 20:24 UTC (permalink / raw)
To: Williams, Alex <awilliams@teamdrg.com>; +Cc: pgsql-admin
On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams@teamdrg.com> wrote:
>
> However, I thought with the use of MVCC,
> an active query on the slave (long running, 30 seconds+) can run reading
> from row xid versions/snapshot...
The problem is that the master could well make changes that would remove rows in that snapshot because the master does not know anything about the rows that need preserving for the replica's query.
That said, IIRC there may be something in 9.4 or 9.5 which provides for the replica to send that info to the server in order to coordinate things, but I haven't used that and so cannot offer more.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-23 21:08 Yaser Raja <yrraja@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yaser Raja @ 2015-11-23 21:08 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Williams, Alex <awilliams@teamdrg.com>; pgsql-admin
On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams@teamdrg.com>
> wrote:
> >
> > However, I thought with the use of MVCC,
> > an active query on the slave (long running, 30 seconds+) can run reading
> > from row xid versions/snapshot...
>
> The problem is that the master could well make changes that would remove
> rows in that snapshot because the master does not know anything about the
> rows that need preserving for the replica's query.
>
hot_standby_feedback addresses this problem.
Here is the detail from the PG docs:
"Specifies whether or not a hot standby will send feedback to the primary
or upstream standby about queries currently executing on the standby. This
parameter can be used to eliminate query *cancels caused by cleanup records*,
but *can cause database bloat* on the primary for some workloads. Feedback
messages will not be sent more frequently than once per
wal_receiver_status_interval. The default value is off. This parameter can
only be set in the postgresql.conf file or on the server command line."
http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
Note that this will only avoid the conflicts caused by cleanup records
(which normally is the majority of conflicts).
>
> That said, IIRC there may be something in 9.4 or 9.5 which provides for
> the replica to send that info to the server in order to coordinate things,
> but I haven't used that and so cannot offer more.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-24 16:28 Williams, Alex <awilliams@teamdrg.com>
parent: Yaser Raja <yrraja@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Williams, Alex @ 2015-11-24 16:28 UTC (permalink / raw)
To: Yaser Raja <yrraja@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Williams, Alex <awilliams@teamdrg.com>; pgsql-admin
Thank you both, I really appreciate your responses, and hopefully this may help others who may run into this.
I noticed on the link to that ref. it was 9.3, we have 9.2 (which can use that param too,) but I didn't realize I was looking at the 9.0 docs and didn't see that param there.
Concerning these two params below, when using hot_standby_feedback, what would you set them if you had the situation where you had queries constantly running on the standby, some quick and some long, practically all day/night, everyday?
max_standby_archive_delay (integer)
max_standby_streaming_delay (integer)
[http://www.postgresql.org/docs/9.2/static/hot-standby.html]
Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby. max_standby_archive_delay must be kept large in this case, because delayed WAL files might already contain entries that conflict with the desired standby queries.
Should I still keep them as -1 (infinite)? If I do, will the WAL updates still be applied while current queries are running, for example:
1. Query1 starts SELECT (10 minute query) @ 10:00:00 AM (current row version xid's)
2. The WAL update is applied to the standby (2 seconds) starts at 10:00:08 AM, completes at 10:00:10 AM
3. Query2 starts SELECT @ 10:00:15 AM (starts 5 seconds after the previous WAL update was applied; will it get the new data -- row versions xids -- just applied from the WAL update from #2? It looks like the xids are preserved on the primary when that param is set: hot_standby_feedback, but just want to confirm, that while the primary maybe more bloated now with dead rows, the new and existing (which could be dead on the primary) are there for the standby to see?)
And sorry, just one last question (I'll be going over the docs thoroughly today and will be testing these in two VM's before I do anything in production) and that is: for the vacuuming of dead rows on the master, I assume that will now occur once there are no queries running on the standby? We have a lot of cpu power/space/mem, so that won't be an issue, but I'm just curious what will trigger that, I just assume no active queries running would do that -- pls let me know if I'm wrong.
I've seen this, but prefer not to set that because it may still kill long running queries on the standby.
"Another option is to increase vacuum_defer_cleanup_age<http://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#GUC-VACUUM-DEFER-CLEANUP-A...; on the primary server, so that dead rows will not be cleaned up as quickly as they normally would be. This will allow more time for queries to execute before they are canceled on the standby, without having to set a high max_standby_streaming_delay. However it is difficult to guarantee any specific execution-time window with this approach, since vacuum_defer_cleanup_age is measured in transactions executed on the primary server."
Again, thank you both.
Alex
From: Yaser Raja <yrraja@gmail.com<mailto:yrraja@gmail.com>>
Date: Monday, November 23, 2015 4:08 PM
To: Scott Ribe <scott_ribe@elevated-dev.com<mailto:scott_ribe@elevated-dev.com>>
Cc: "Williams, Alex" <awilliams@teamdrg.com<mailto:awilliams@teamdrg.com>>, "pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>" <pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>>
Subject: Re: [ADMIN] Question on Hot Standby in PostgreSQL
On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe <scott_ribe@elevated-dev.com<mailto:scott_ribe@elevated-dev.com>> wrote:
On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams@teamdrg.com<mailto:awilliams@teamdrg.com>> wrote:
>
> However, I thought with the use of MVCC,
> an active query on the slave (long running, 30 seconds+) can run reading
> from row xid versions/snapshot...
The problem is that the master could well make changes that would remove rows in that snapshot because the master does not know anything about the rows that need preserving for the replica's query.
hot_standby_feedback addresses this problem.
Here is the detail from the PG docs:
"Specifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby. This parameter can be used to eliminate query cancels caused by cleanup records, but can cause database bloat on the primary for some workloads. Feedback messages will not be sent more frequently than once per wal_receiver_status_interval. The default value is off. This parameter can only be set in the postgresql.conf file or on the server command line."
http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
Note that this will only avoid the conflicts caused by cleanup records (which normally is the majority of conflicts).
That said, IIRC there may be something in 9.4 or 9.5 which provides for the replica to send that info to the server in order to coordinate things, but I haven't used that and so cannot offer more.
--
Scott Ribe
scott_ribe@elevated-dev.com<mailto:scott_ribe@elevated-dev.com>
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567<tel:%28303%29%20722-0567> voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-25 08:43 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Williams, Alex <awilliams@teamdrg.com>
0 siblings, 1 reply; 7+ messages in thread
From: Albe Laurenz @ 2015-11-25 08:43 UTC (permalink / raw)
To: 'Williams, Alex *EXTERN*' <awilliams@teamdrg.com>; Yaser Raja <yrraja@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Alex Williams wrote:
> Concerning these two params below, when using hot_standby_feedback, what would you set them if you
> had the situation where you had queries constantly running on the standby, some quick and some long,
> practically all day/night, everyday?
>
> max_standby_archive_delay (integer)
> max_standby_streaming_delay (integer)
> Should I still keep them as -1 (infinite)?
I'd say set them both to 0.
If you have hot_standby_feedback=on, the primary will not remove any rows still
needed on the standby, so there should never be a need to delay WAL application.
The drawback is that you may get considerable bloat on the primary if many
DELETE/UPDATE operation are going on and queries on the standby take a long time.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-25 20:57 Yaser Raja <yrraja@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 7+ messages in thread
From: Yaser Raja @ 2015-11-25 20:57 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Williams, Alex *EXTERN* <awilliams@teamdrg.com>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
On Wed, Nov 25, 2015 at 3:43 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
> Alex Williams wrote:
> > Concerning these two params below, when using hot_standby_feedback,
> what would you set them if you
> > had the situation where you had queries constantly running on the
> standby, some quick and some long,
> > practically all day/night, everyday?
> >
> > max_standby_archive_delay (integer)
> > max_standby_streaming_delay (integer)
>
> > Should I still keep them as -1 (infinite)?
>
> I'd say set them both to 0.
>
> If you have hot_standby_feedback=on, the primary will not remove any rows
> still
> needed on the standby, so there should never be a need to delay WAL
> application.
>
hot_standby_feedback will significantly reduce the conflicts but it will
not totally eliminate them. For example if a new column is added to a
table, any query using this table on slave will be cancelled to apply this
change.
IF query cancellation is totally not acceptable then you will have to set
these delays to -1.
>
> The drawback is that you may get considerable bloat on the primary if many
> DELETE/UPDATE operation are going on and queries on the standby take a
> long time.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question on Hot Standby in PostgreSQL
@ 2015-11-25 21:12 Williams, Alex <awilliams@teamdrg.com>
parent: Yaser Raja <yrraja@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Williams, Alex @ 2015-11-25 21:12 UTC (permalink / raw)
To: Yaser Raja <yrraja@gmail.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Williams, Alex <awilliams@teamdrg.com>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Hi Laurenz and Raja,
Thank you both for your feedback and insight, I really appreciate it. I think I'll leave it with -1 (since we may add columns in the future and other possible DDL changes,) so we'll schedule downtime on the standby at least 1x a week for cleaning up the master.
Again, I thank everyone for your help, this really helped my situation.
Happy Thanksgiving and Best Regards,
Alex
P.S. If anyone is ever in NJ, let me know, lunch is on me :)
From: Yaser Raja <yrraja@gmail.com<mailto:yrraja@gmail.com>>
Date: Wednesday, November 25, 2015 3:57 PM
To: Albe Laurenz <laurenz.albe@wien.gv.at<mailto:laurenz.albe@wien.gv.at>>
Cc: "Williams, Alex" <awilliams@teamdrg.com<mailto:awilliams@teamdrg.com>>, Scott Ribe <scott_ribe@elevated-dev.com<mailto:scott_ribe@elevated-dev.com>>, "pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>" <pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>>
Subject: Re: [ADMIN] Question on Hot Standby in PostgreSQL
On Wed, Nov 25, 2015 at 3:43 AM, Albe Laurenz <laurenz.albe@wien.gv.at<mailto:laurenz.albe@wien.gv.at>> wrote:
Alex Williams wrote:
> Concerning these two params below, when using hot_standby_feedback, what would you set them if you
> had the situation where you had queries constantly running on the standby, some quick and some long,
> practically all day/night, everyday?
>
> max_standby_archive_delay (integer)
> max_standby_streaming_delay (integer)
> Should I still keep them as -1 (infinite)?
I'd say set them both to 0.
If you have hot_standby_feedback=on, the primary will not remove any rows still
needed on the standby, so there should never be a need to delay WAL application.
hot_standby_feedback will significantly reduce the conflicts but it will not totally eliminate them. For example if a new column is added to a table, any query using this table on slave will be cancelled to apply this change.
IF query cancellation is totally not acceptable then you will have to set these delays to -1.
The drawback is that you may get considerable bloat on the primary if many
DELETE/UPDATE operation are going on and queries on the standby take a long time.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2015-11-25 21:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-20 19:50 Re: Question on Hot Standby in PostgreSQL Williams, Alex <awilliams@teamdrg.com>
2015-11-23 20:24 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-11-23 21:08 ` Yaser Raja <yrraja@gmail.com>
2015-11-24 16:28 ` Williams, Alex <awilliams@teamdrg.com>
2015-11-25 08:43 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-11-25 20:57 ` Yaser Raja <yrraja@gmail.com>
2015-11-25 21:12 ` Williams, Alex <awilliams@teamdrg.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox