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(dot)klaver(at)aklaver(dot)com