how to slow down parts of Pg

Started by Kevin Brannenalmost 6 years ago20 messagesgeneral

KBrannen@efji.com

almost 6 years ago

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.

Michael Lewis

mlewis@entrata.com

almost 6 years ago

In reply to: Kevin Brannen (#1)

Re: how to slow down parts of Pg

You say 12.2 is in testing but what are you using now? Have you tuned
configs much? Would you be able to implement partitioning such that your
deletes become truncates or simply a detaching of the old partition?
Generally if you are doing a vacuum full, you perhaps need to tune
autovacuum to be more aggressive. Consider pg_repack at least to avoid
taking an exclusive lock for the entire duration. If partitioning is not an
option, could you delete old records hourly rather than daily?

Show quoted text

Michael Loftis

mloftis@wgops.com

almost 6 years ago

In reply to: Kevin Brannen (#1)

Re: how to slow down parts of Pg

drbdsetup allows you to control the sync rates.

On Tue, Apr 21, 2020 at 14:30 Kevin Brannen <KBrannen@efji.com> 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.

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Michael Lewis (#2)

RE: how to slow down parts of Pg

From: Michael Lewis <mlewis@entrata.com>

You say 12.2 is in testing but what are you using now? Have you tuned configs much? Would you be able to implement partitioning such that your deletes become truncates or simply a detaching of the old partition? Generally if you are doing a vacuum full, you perhaps need to tune autovacuum to be more aggressive. Consider pg_repack at least to avoid taking an exclusive lock for the entire duration. If partitioning is not an option, could you delete old records hourly rather than daily?

Good questions, it's always hard to know how much to include. 😊

Current production is 9.6, so things like partitioning aren't available there, but will be in the future.

We've tuned the configs some and don't having any issues with Pg at the moment. This does need to be relooked at; I have a few notes of things to revisit as our hardware changes.

Partitioning our larger tables by time is on the ToDo list. I hadn't thought about that helping with maintenance, so thanks for bringing that up. I'll increase the priority of this work as I can see this helping with the archiving part.

I don't particularly like doing the vacuum full, but when it will release 20-50% of disk space for a large table, then it's something we live with. As I understand, a normal vacuum won't release all the old pages that a "full" does, hence why we have to do that. It's painful enough I've restricted it to once quarter; I'd do it only once a year if I thought I could get away with it. Still this is something I'll put on the list to go research with practical trials. I don't think the lock for the vacuuming hurts us, but I've heard of pg_repack and I'll look into that too.

I have considered (like they say with vacuuming) that more often might be better. Of course that would mean doing some of this during the day when the DB is busier. Hmm, maybe 1000/minute wouldn't hurt and that would shorten the nightly run significantly. I may have to try that and see if it just adds to background noise or causes problems.

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.

Michael Loftis

mloftis@wgops.com

almost 6 years ago

In reply to: Kevin Brannen (#4)

Re: how to slow down parts of Pg

On Tue, Apr 21, 2020 at 15:05 Kevin Brannen <KBrannen@efji.com> wrote:

*From:* Michael Lewis <mlewis@entrata.com>

You say 12.2 is in testing but what are you using now? Have you tuned

configs much? Would you be able to implement partitioning such that your
deletes become truncates or simply a detaching of the old partition?
Generally if you are doing a vacuum full, you perhaps need to tune
autovacuum to be more aggressive. Consider pg_repack at least to avoid
taking an exclusive lock for the entire duration. If partitioning is not an
option, could you delete old records hourly rather than daily?

Good questions, it's always hard to know how much to include. 😊

Current production is 9.6, so things like partitioning aren't available
there, but will be in the future.

We've tuned the configs some and don't having any issues with Pg at the
moment. This does need to be relooked at; I have a few notes of things to
revisit as our hardware changes.

Partitioning our larger tables by time is on the ToDo list. I hadn't
thought about that helping with maintenance, so thanks for bringing that
up. I'll increase the priority of this work as I can see this helping with
the archiving part.

I don't particularly like doing the vacuum full, but when it will release
20-50% of disk space for a large table, then it's something we live with.
As I understand, a normal vacuum won't release all the old pages that a
"full" does, hence why we have to do that. It's painful enough I've
restricted it to once quarter; I'd do it only once a year if I thought I
could get away with it. Still this is something I'll put on the list to go
research with practical trials. I don't think the lock for the vacuuming
hurts us, but I've heard of pg_repack and I'll look into that too.

Why do vacuum full at all? A functional autovacuum will return the free
pages to be reused. You just won’t see the reduction in disk usage at the
OS level. Since the pages are clearly going to be used it doesn’t really
make sense to do a vacuum full at all. Let autovacuum do it’s job or if
that’s not keeping up a normal vacuum without the full. The on dusk sizes
will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables.

I have considered (like they say with vacuuming) that more often might be
better. Of course that would mean doing some of this during the day when
the DB is busier. Hmm, maybe 1000/minute wouldn't hurt and that would
shorten the nightly run significantly. I may have to try that and see if it
just adds to background noise or causes problems.

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.

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Michael Loftis (#3)

RE: how to slow down parts of Pg

From: Michael Loftis <mloftis@wgops.com>

From: Kevn Brannen
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...

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. :)

I can & will do this, maybe at 90% of our bandwidth, so thanks for the suggestion. Still, this is sort of a last resort thing as I believe controlling the DB to be the ultimate need.

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.

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Michael Loftis (#5)

RE: how to slow down parts of Pg

From: Michael Loftis <mloftis@wgops.com>

From: Kevn Brannen
I don't particularly like doing the vacuum full, but when it will release 20-50% of disk space for a large table, then it's something we live with. As I understand, a normal vacuum won't release all the old pages that a "full" does, hence why we have to do that. It's painful enough I've restricted it to once quarter; I'd do it only once a year if I thought I could get away with it. Still this is something I'll put on the list to go research with practical trials. I don't think the lock for the vacuuming hurts us, but I've heard of pg_repack and I'll look into that too.

Why do vacuum full at all? A functional autovacuum will return the free pages to be reused. You just won’t see the reduction in disk usage at the OS level. Since the pages are clearly going to be used it doesn’t really make sense to do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping up a normal vacuum without the full. The on dusk sizes will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables.

Sometimes I need the disk space back. It also makes me feel better. (OK, this may not a good reason but there is a hint of truth in this.) What this probably means is that I need to get a better understanding of vacuuming.

Virendra Kumar

viru_7683@yahoo.com

almost 6 years ago

In reply to: Kevin Brannen (#7)

Re: how to slow down parts of Pg

Autovacuum does takes care of dead tuples and return space to table's allocated size and can be re-used by fresh incoming rows or any updates.

Index bloat is still not being taken care of by autovacuum process. You should use pg_repack to do index rebuild. Keep in mind that pg_repack requires double the space of indexes, since there will be two indexes existing during rebuild processes.

Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 2:26:11 PM PDT, Kevin Brannen <kbrannen@efji.com> wrote:

From: Michael Loftis <mloftis@wgops.com>

From: Kevn Brannen

I don't particularly like doing the vacuum full, but when it will release 20-50% of disk space for a large table, then it's something we live with. As I understand, a normal vacuum won't release all the old pages that a "full" does, hence why we have to do that. It's painful enough I've restricted it to once quarter; I'd do it only once a year if I thought I could get away with it. Still this is something I'll put on the list to go research with practical trials. I don't think the lock for the vacuuming hurts us, but I've heard of pg_repack and I'll look into that too.

Why do vacuum full at all? A functional autovacuum will return the free pages to be reused. You just won’t see the reduction in disk usage at the OS level. Since the pages are clearly going to be used it doesn’t really make sense to do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping up a normal vacuum without the full. The on dusk sizes will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables.

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.

David G. Johnston

david.g.johnston@gmail.com

almost 6 years ago

In reply to: Kevin Brannen (#7)

Re: how to slow down parts of Pg

On Tue, Apr 21, 2020 at 2:25 PM Kevin Brannen <KBrannen@efji.com> wrote:

Sometimes I need the disk space back. It also makes me feel better. (OK,
this may not a good reason but there is a hint of truth in this.) What this
probably means is that I need to get a better understanding of vacuuming.

Imagine you have an auto-expanding array and also that individual cells can
be reused if the data in them is removed first. Deletion marks a cell as
needing to be cleaned up (i.e., data removed). Vacuum actually performs
the cleaning. Insertion causes the system to either provide an existing,
cleaned/empty, cell OR to add a new cell to the end of the array and
provide that. The longer the deleted cells go uncleaned the more cells
that are added onto the end of the array and the more physical space the
array takes up. If you clean up the deleted cells more frequently they can
be reused in lieu of expanding the array.

Vacuum full counts the number of non-empty cells in the array, creates a
new array with that many cells, and writes the non-empty values into it -
then removes the old array.

Immediately after you perform a normal vacuum you have lots of empty cells
- but you know that the space is going to be filled in again soon so it
doesn't normally make sense to "resize the array".

David J.

#10

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Virendra Kumar (#8)

RE: how to slow down parts of Pg

From: Virendra Kumar <viru_7683@yahoo.com>

Autovacuum does takes care of dead tuples and return space to table's allocated size and can be re-used by fresh incoming rows or any updates.

Index bloat is still not being taken care of by autovacuum process. You should use pg_repack to do index rebuild. Keep in mind that pg_repack requires double the space of indexes, since there will be two indexes existing during rebuild processes.

Ha! I knew there was a reason I was doing the full, I just couldn't remember indexes was why. Pg_repack needs to move higher on the ToDo list too. I need a clone to do all of this. :)

From: David G. Johnston <david.g.johnston@gmail.com>

Imagine you have an auto-expanding array and also that individual cells can be reused if the data in them is removed first…

Yes, the concepts aren't that hard, the issue is how to apply them in the most effective manner. Still, nice explanation, I'll use that when explaining the work to the group so I can pass the info along.

#11

Michael Lewis

mlewis@entrata.com

almost 6 years ago

In reply to: Kevin Brannen (#10)

Re: how to slow down parts of Pg

Reviewing pg_stat_user_tables will give you an idea of how often autovacuum
is cleaning up those tables that "need" that vacuum full on a quarterly
basis. You can tune individual tables to have a lower threshold ratio of
dead tuples so the system isn't waiting until you have 20% dead rows before
vacuuming a table with millions of rows that occupies a GB or more on disk.
You might consider changing your nightly analyze to a nightly vacuum
analyze, at least for the tables you know can be problematic. The more
dense a table is packed, the better cache_hits and other such metrics. Like
making dinner, cleanup as you go.

One thing that I think is interesting is that the default cost_delay has
been updated with PG12 from 20ms down to 2ms such that all things being
equal, much much more work is done by autovacuum in a given second. It may
be worth taking a look at.

Another great thing coming to you in PG12 is the option to do reindex
concurrently. Then there's no need for pg_repack on indexes.

Good luck sir.

#12

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Virendra Kumar (#8)

Re: how to slow down parts of Pg

On 4/21/20 2:32 PM, Virendra Kumar wrote:

Autovacuum does takes care of dead tuples and return space to table's
allocated size and can be re-used by fresh incoming rows or any updates.

Index bloat is still not being taken care of by autovacuum process. You
should use pg_repack to do index rebuild. Keep in mind that pg_repack
requires double the space of indexes, since there will be two indexes
existing during rebuild processes.

You sure about that? On Postgres 12:

--2020-04-21 15:47:27.452 PDT-0DEBUG: plant1: vac: 5154 (threshold
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG: autovac_balance_cost(pid=18701
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200,
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG: scanned index "p_no_pkey" to
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG: scanned index "common_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG: scanned index "genus_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: "plant1": removed 5114 row
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "p_no_pkey" now contains
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "common_idx" now contains
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "genus_idx" now contains
5154 row versions in 47 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.518 PDT-0DEBUG: "plant1": found 5154 removable,
5154 nonremovable row versions in 195 out of 195 pages
--2020-04-21 15:47:27.518 PDT-0DETAIL: 0 dead row versions cannot be
removed yet, oldest xmin: 9715
There were 256 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s.
--2020-04-21 15:47:27.518 PDT-0LOG: automatic vacuum of table
"production.public.plant1": index scans: 1
pages: 0 removed, 195 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 5154 removed, 5154 remain, 0 are dead but not yet
removable, oldest xmin: 9715
buffer usage: 753 hits, 0 misses, 255 dirtied
avg read rate: 0.000 MB/s, avg write rate: 30.586 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s

Regards,
Virendra Kumar

--
Adrian Klaver
adrian.klaver@aklaver.com

#13

Virendra Kumar

viru_7683@yahoo.com

almost 6 years ago

In reply to: Adrian Klaver (#12)

Re: how to slow down parts of Pg

Hi Adrian,

Here is test case, basically when autovacuum runs it did release the space to disk since it had may be continuous blocks which can be released to disk but the space used by index is still being held until I ran the reindex on the table (I assume reindex for index would work as well). Subsequent insert statement did not utilize the blocks in index segment as we can see below so index bloats are still not addressed or may be I am doing something wrong:

postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-apple-darwin18.7.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit
(1 row)

postgres=#
postgres=# CREATE TABLE validate_pg_repack
postgres-# (
postgres(#    effectivedate                                  timestamp,
postgres(#    masterentityid                                 integer not null,
postgres(#    primaryissueid                                 varchar(65535),
postgres(#    longshortindicator                             varchar(65535),
postgres(#    pg_repack_id                                   varchar(65535)
postgres(# );
CREATE TABLE
postgres=# CREATE SEQUENCE validate_pg_repack_masterentityid_seq INCREMENT 1 START 1 OWNED BY validate_pg_repack.masterentityid;
CREATE SEQUENCE
postgres=#
postgres=# CREATE unique INDEX idx_pg_repack_masterentityid ON validate_pg_repack USING btree (masterentityid);
CREATE INDEX
postgres=#
postgres=# CREATE INDEX idx_pg_repack_effectivedate ON validate_pg_repack USING btree (effectivedate);
CREATE INDEX
postgres=#
postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(1, 900000);
INSERT 0 900000
postgres=#
postgres=# select pg_sleep(30);
pg_sleep
----------

(1 row)

postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 900000 | 0 | 2020-04-21 19:34:09.579475-07 | 1
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)

postgres=# delete from validate_pg_repack where masterentityid > 450000;
DELETE 450000
postgres=# select pg_sleep(30);
pg_sleep
----------

(1 row)

postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
40 MB
(1 row)

postgres=# INSERT INTO validate_pg_repack (effectivedate,masterentityid,primaryissueid,longshortindicator,pg_repack_id) SELECT
postgres-# now() + round(random() * 1000) * '1 second' :: interval,
postgres-# nextval('validate_pg_repack_masterentityid_seq'),
postgres-# 'some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(450000, 900000);
INSERT 0 450001
postgres=# select pg_sleep(120);
pg_sleep
----------

(1 row)
postgres=#
postgres=# select relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze | autoanalyze_count
--------------------+-----------+-----------+-------------------------------+-------------------
validate_pg_repack | 1350001 | 450000 | 2020-04-21 19:37:10.829261-07 | 3
(1 row)

postgres=#
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)

postgres=# reindex table CONCURRENTLY validate_pg_repack;
REINDEX
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
----------------
80 MB
(1 row)

postgres=#
postgres=# drop table validate_pg_repack cascade;
DROP TABLE
postgres=#
postgres=#
postgres=#

Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 3:54:13 PM PDT, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 4/21/20 2:32 PM, Virendra Kumar wrote:

Autovacuum does takes care of dead tuples and return space to table's
allocated size and can be re-used by fresh incoming rows or any updates.

Index bloat is still not being taken care of by autovacuum process. You
should use pg_repack to do index rebuild. Keep in mind that pg_repack
requires double the space of indexes, since there will be two indexes
existing during rebuild processes.

You sure about that? On Postgres 12:

Regards,
Virendra Kumar

--
Adrian Klaver
adrian.klaver@aklaver.com

#14

Laurenz Albe

laurenz.albe@cybertec.at

almost 6 years ago

In reply to: Kevin Brannen (#1)

Re: how to slow down parts of Pg

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

#15

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Laurenz Albe (#14)

RE: how to slow down parts of Pg

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 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.

#16

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Virendra Kumar (#13)

Re: how to slow down parts of Pg

On 4/21/20 7:43 PM, Virendra Kumar wrote:

Hi Adrian,

Here is test case, basically when autovacuum runs it did release the
space to disk since it had may be continuous blocks which can be
released to disk but the space used by index is still being held until I
ran the reindex on the table (I assume reindex for index would work as
well). Subsequent insert statement did not utilize the blocks in index
segment as we can see below so index bloats are still not addressed or
may be I am doing something wrong:

Well I learned something. I replicated your commands on my 12 instance
and got the exact same results. I should have read this:

https://www.postgresql.org/docs/12/routine-reindex.html

before.

--
Adrian Klaver
adrian.klaver@aklaver.com

#17

Ron

ronljohnsonjr@gmail.com

almost 6 years ago

In reply to: Kevin Brannen (#1)

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.

#18

Peter J. Holzer

hjp-pgsql@hjp.at

almost 6 years ago

In reply to: Kevin Brannen (#6)

Re: how to slow down parts of Pg

On 2020-04-21 21:16:57 +0000, Kevin Brannen wrote:

From: Michael Loftis <mloftis@wgops.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.

#19

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Peter J. Holzer (#18)

RE: how to slow down parts of Pg

From: Peter J. Holzer <hjp-pgsql@hjp.at>
On 2020-04-21 21:16:57 +0000, Kevin Brannen wrote:

From: Michael Loftis <mloftis@wgops.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.

#20

Kevin Brannen

KBrannen@efji.com

almost 6 years ago

In reply to: Ron (#17)

RE: how to slow down parts of Pg

From: Ron <ronljohnsonjr@gmail.com>

What you need is async replication instead of synchronous replication.

The only way I can think of to do that in our present situation would be to
buy DRBD-Proxy, which becomes a single-point-of-failure and goes against the
idea of HA (it seems like a good product for disaster recovery but that's not
the goal). In addition, since we're trying to move away from DRBD and go to
WAL streaming, that doesn't seem like the best use of time and money. :)

If you'd like to expound on other ways/tools to do that, I'd love to hear about it,
although this might be better off-list.

how to slow down parts of Pg

Attachments: