Can you please suggest how to configure hot_standby_feedback?
Am 31.10.19 um 06:21 schrieb M Tarkeshwar Rao:
Can you please suggest how to configure hot_standby_feedback?
turn it on if you want execute long running queries on the standby, keep
in mind it can lead to more bloat on the master.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
I have a follow up question on *hot_standby_feedback*.
If I have 3 read replicas that all have this set to ON, and someone runs a
very long query on *one* of the replicas, can all three replicas fall
behind until the query is finished? This is the behavior I observed more
than once, but I can’t seem to be able to explain it based on the
documented behavior of this parameter.
A related question is — *how can you avoid doing auto vacuum during the
busy peak hours, and do more aggressive auto vacuuming at night during low
traffic?* Is that possible?
I tried achieving that with a manual vacuum, but that causes all replicas
to get far behind while it’s running, which is also not acceptable.
Thanks so much!
On Thu, Oct 31, 2019 at 1:02 AM Andreas Kretschmer <andreas@a-kretschmer.de>
wrote:
Am 31.10.19 um 06:21 schrieb M Tarkeshwar Rao:
Can you please suggest how to configure hot_standby_feedback?
turn it on if you want execute long running queries on the standby, keep
in mind it can lead to more bloat on the master.Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com--
Konstantin Gredeskoul
CEO & Principal Consultant ● ReinventONE Inc ● https://reinvent.one/ ●
(415) 265-1054
On Sat, Nov 2, 2019 at 8:52 PM Konstantin Gredeskoul <kigster@gmail.com>
wrote:
A related question is — *how can you avoid doing auto vacuum during the
busy peak hours, and do more aggressive auto vacuuming at night during low
traffic?* Is that possible?I tried achieving that with a manual vacuum, but that causes all replicas
to get far behind while it’s running, which is also not acceptable.
What is the main concern here? Disk IO? So we want to allow autovacuum
workers to hit our disks less during busy hours and more during quiet
hours, right?
If so, then one of the ideas here could be the changing quotas for the
autovacuum workers dynamically using cron or its alternative:
1) throttle autovacuum before peak hours, changing
autovacuum_vacuum_cost_delay (or autovacuum_vacuum_cost_limit). The pre-12
default settings (_cost_delay 20ms and cost_limit 200) will give all the
workers the shared "quota" that means, roughly, 8 MiB/s for reads, less for
writes (good article:
https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/), and in 12
(where _cost_delay was reduced to 2ms) it will be ~80 MiB/s. Monitoring +
logs analysis is needed to understand real IO though. Individually tuned
tables will have individual quotas.
2) change those settings back to normal after busy hours.
In any case, I highly recommend to monitor "autovacuum queue" -- the list
of tables that should be already autovacuumed, but they are not, due to
some reasons. Having proper graphs in monitoring and alerts if the size of
this "queue" exceeds some reasonable number (say, 2 *
autovacuum_max_workers) will help a lot to understand the autovacuum
behavior for the concrete system. Here is an excellent example of how to do
it, from Avito:
https://github.com/avito-tech/dba-utils/blob/master/munin/vacuum_queue, and
here is my version, with some improvements:
https://gitlab.com/snippets/1889668.