>From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx> > >>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 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. > >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 their choice repeatedly, but their reply is always "budgets", and I reply as politely as I can, "faster network or live with the slowness as 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 an optimization 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 create the 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, 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.
