RE: how to slow down parts of Pg - Mailing list pgsql-general

>From: Laurenz Albe <laurenz.albe@cybertec.at>
>
>>On Tue, 2020-04-21 at 20:30 +0000, 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.
>>
>> [...] 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
vacuumfull 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.
>
>The obvious and best answer is: get a faster network, or choose a different storage solution.

I believe I mention originally that the network is controlled by others (the customer). I've pointed out the results of
theirchoice
 
repeatedly, but their reply is always "budgets", and I reply as politely as I can, "faster network or live with the
slownessas I've
 
done all I can for now". It's a somewhat frustrating conversation as you can imagine.


>Other than that, you can try to make the maintainance operations less resource intense:
>
>- partition the tables so that you can get rid of old data with DROP TABLE.
>  The ANALYZE won't hurt, if you treat only the required tables.
>- use "pg_basebackup" with the "--max-rate" option

Yes, this was the heart of the post, how to use less resources. I'd always thought of partitioning the larger tables as
anoptimization
 
for running reports, but my eyes have been opened that it has other benefits too.

I'm not sure changing the backup program will help when it's sitting on top of DRBD, but I can limit DRBD's rate to
createthe same effect.
 
Still, it doesn't hurt to spend a little time researching this. OTOH, you did just prompt an idea, so that's helpful
too.


>About VACUUM, you may have a problem.  Avoid the need for VACUUM (FULL) at any price.
>That usually requires tuning autovacuum to be faster, which means using more I/O.

OK, I've never really liked doing a "full", but I perceived it as helpful to us. I'll see about making autovacuum more
aggressive.

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,
youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or
attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately
notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them
todisk. Thank you.