|
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?
[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 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@xxxxxxxxx>
Date: Monday, November 23, 2015 4:08 PM To: Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> Cc: "Williams, Alex" <awilliams@xxxxxxxxxxx>, "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx> Subject: Re: Question on Hot Standby in PostgreSQL On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe
<scott_ribe@xxxxxxxxxxxxxxxx> wrote:
On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams@xxxxxxxxxxx> wrote: 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).
|
