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. > > 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. [...] > 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. 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 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. If you cannot find a sweet spot there, you have no alternative but getting better I/O (which, as I said in the beginning, would be the correct solution anyway). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
