Re: how to slow down parts of Pg
От | Adrian Klaver |
---|---|
Тема | Re: how to slow down parts of Pg |
Дата | |
Msg-id | 655239f9-7ece-8f18-816b-68ed421d34e2@aklaver.com обсуждение исходный текст |
Ответ на | Re: how to slow down parts of Pg (Virendra Kumar <viru_7683@yahoo.com>) |
Ответы |
Re: how to slow down parts of Pg
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: