RE: how to slow down parts of Pg

>From: Peter J. Holzer <hjp-pgsql(at)hjp(dot)at>
>On 2020-04-21 21:16:57 +0000, Kevin Brannen wrote:
>> From: Michael Loftis <mloftis(at)wgops(dot)com>
>> > drbdsetup allows you to control the sync rates.
>>
>> I was hoping not to have to do that, but the more I think about this
>> I'm realizing that it won't hurt because the network cap is
>> effectively limiting me anyway. :)
>
>Alternatively you might consider traffic shaping. DRBD can only set a fixed limit (because it knows only about its own traffic). Traffic shaping can adjust the limit depending on other traffic (it can also prioritize traffic, etc.). However, to be effective, it needs to run on a router as close to the bottleneck as possible - typically that means either the border router or the firewall. So it is something the customer's network guy should set up.

Traffic shaping was actually my very first thought. :) It has its upsides
and downsides like any other solution. The biggest downside is that it's not
up to us to control, so we have to find their network person (usually harder
than it should be) and then adjust ... probably multiple times and always
manually.

For any who are wondering what this thread has done for me, other than create
a list of things to research... :)

At this point in time, I think the plan is to (roughly in this order):

0. limit DRBD's rate (I think I can script this & I probably only need to do this during the maintenance work);
1. make autovac more aggressive on the larger logging tables;
2. change the "vacuum full" to just reindexing (either with pg_repack or "reindex concurrently");
3. partition the bigger logging tables.

I'm tempted to also do the archiving in very small amounts all thru the day
(sort of like how autovac works) to spread that load and not have such a huge
hit once per day. For the moment, this is going in my back pocket to pull out
only if the above doesn't do enough.

Then we move to WAL streaming which I believe will be the biggest
help of all -- or so I hope. It will also have the largest learning curve,
but it'll be good for me to learn that.

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.