Re: how to slow down parts of Pg

What you need is async replication instead of synchronous replication.

On 4/21/20 3:30 PM, Kevin Brannen wrote:
>
> I have an unusual need:  I need Pg to slow down. I know, we all want our
> DB to go faster, but in this case it's speed is working against me in 1 area.
>
> We have systems that are geo-redundant for HA, with the redundancy being
> handled by DRBD to keep the disks in sync, which it does at the block
> level. For normal operations, it actually works out fairly well. That
> said, we recognize that what we really need to do is one of the forms of
> streaming (ch 26 of the manual) which I believe would help this problem a
> lot if not solve it -- but we don't have the time to do that at the
> moment. I plan and hope to get there by the end of the year. The part that
> hurts so bad is when we do maintenance operations that are DB heavy, like
> deleting really old records out of archives (weekly), moving older records
> from current tables to archive tables plus an analyze (every night),
> running pg_backup (every night), other archiving (weekly), and vacuum full
> to remove bloat (once a quarter). All of this generates a lot of disk
> writes, to state the obvious.
>
> The local server can handle it all just fine, but the network can't handle
> it as it tries to sync to the other server. Sometimes we can add network
> bandwidth, many times we can't as it depends on others. To borrow a phrase
> from the current times, we need to flatten the curve. 😊
>
> A few parts of our maintenance process I've tamed by doing "nice -20" on
> the process (e.g. log rotation); but I can't really do that for Pg because
> the work gets handed off to a background process that's not a direct child
> process … and I don't want to slow the DB as a whole because other work is
> going on (like handling incoming data).
>
> Part of the process I've slowed down by doing the work in chunks of 10K
> rows at a time with a pause between each chunk to allow the network to
> catch up (instead of an entire table in 1 statement). This sort of works,
> but some work/SQL is between hard to next-to-impossible to break up like
> that. That also produces some hard spikes, but that's better than the
> alternative (next sentence). Still, large portions of the process are hard
> to control and just punch the network to full capacity and hold it there
> for far too long.
>
> So, do I have any other options to help slow down some of the Pg
> operations? Or maybe some other short-term mitigations we can do with Pg
> configurations? Or is this a case where we've already done all we can do
> and the only answer is move to WAL streaming as fast as possible?
>
> If it matters, this is being run on Linux servers. Pg 12.2 is in final
> testing and will be rolled out to production soon -- so feel free to offer
> suggestions that only apply to 12.x.
>
> Thanks,
>
> Kevin
>
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you are
> not the intended recipient, or a person responsible for delivering it to
> the intended recipient, you are hereby notified that any disclosure,
> distribution, review, copy or use of any of the information contained in
> or attached to this message is STRICTLY PROHIBITED. If you have received
> this transmission in error, please immediately notify us by reply e-mail,
> and destroy the original transmission and its attachments without reading
> them or saving them to disk. Thank you.

--
Angular momentum makes the world go 'round.