Обсуждение: Can you please suggest how to configure hot_standby_feedback?
Hi all,
Can you please suggest how to configure hot_standby_feedback?
Regards
Tarkeshwar
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
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.