Search Postgresql Archives

Re: how to slow down parts of Pg

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux