Re: Is Autovacuum running?

[Brad White <b55white@xxxxxxxxx>]

On Tue, Feb 21, 2023 at 11:58 AM Christophe Pettus <xof@xxxxxxxxxxxx> wrote:

> On Feb 21, 2023, at 09:54, Brad White <b55white@xxxxxxxxx> wrote:
> Any suggestions on how to proceed?

First, look at pg_stat_user_tables to see how many inserts etc. have occurred on the tables that are not showing an autovacuum; they may have simply not reached the threshold yet.  If they have, do a VACUUM VERBOSE across the database and make sure that you can manually vacuum them, or if it reports that it can't remove tuples.

Running the table_bloat_check query from here 

https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql 

shows some tables with over 20MB and over 20% bloat while my threshold is set to 0.1.

Before

tablename   | est_rows | pct_bloat | mb_bloat | table_mb
Order Items | 169234   | 33        | 25.98    | 79.695

DocumentLog | 189670   | 23        | 5.52     | 23.648

After

Order Items | 179860   | 28        | 22.6     | 79.695

DocumentLog | 197537   | 23        | 5.78     | 24.672

These two now show up as vacuumed in 

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

So at least that table is working.

tl;dr  Looks to me like vacuum removed  2215 rows from the "Order Items" indexes.

"There were 166068 unused item pointers."

I don't see anything that looks like an error.

[SQL]vacuum verbose "Order Items"

INFO:  vacuuming "public.Order Items"

INFO:  scanned index "Order Items_pkey" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.

INFO:  scanned index "Order Items_AuthorityNum" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.14 sec.

INFO:  scanned index "Order Items_CopySrcID" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.13 sec.

INFO:  scanned index "Order Items_DelivMethFID" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.01u sec elapsed 0.12 sec.

INFO:  scanned index "Order Items_Delivery State FID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_Delivery Zip Code" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.03u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_DiscountFID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.03u sec elapsed 0.10 sec.

INFO:  scanned index "Order Items_EntityWAuthorityFID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.06 sec.

INFO:  scanned index "Order Items_ID" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.05 sec.

INFO:  scanned index "Order Items_InsertFlag" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.10 sec.

INFO:  scanned index "Order Items_ItemFlow2" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_LastSerCaptureTypeID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_MastInv_FID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.11 sec.

INFO:  scanned index "Order Items_OpCarrFID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.03u sec elapsed 0.06 sec.

INFO:  scanned index "Order Items_OrderDate" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.06 sec.

INFO:  scanned index "Order Items_OrderFID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.

INFO:  scanned index "Order Items_PowerU_FID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_Representative" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.12 sec.

INFO:  scanned index "Order Items_SelValidUseFID" to remove 2215 row versions
DETAIL:  CPU 0.03s/0.01u sec elapsed 0.06 sec.

INFO:  scanned index "Order Items_SerialAuthCode" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.03u sec elapsed 0.10 sec.

INFO:  scanned index "Order Items_SerialNum" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.10 sec.

INFO:  scanned index "Order Items_SourceID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.11 sec.

INFO:  scanned index "Order Items_StatusReasonFID" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.05 sec.

INFO:  scanned index "Order Items_SupplyOrderKey" to remove 2215 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.11 sec.

INFO:  scanned index "Order Items_TrailerU_FID" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.09 sec.

INFO:  scanned index "Order Items_VendorFID" to remove 2215 row versions
DETAIL:  CPU 0.01s/0.01u sec elapsed 0.08 sec.

INFO:  "Order Items": removed 2215 row versions in 1289 pages
DETAIL:  CPU 0.00s/0.04u sec elapsed 0.03 sec.

INFO:  index "Order Items_pkey" now contains 149465 row versions in 961 pages
DETAIL:  162 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_AuthorityNum" now contains 149465 row versions in 3182 pages
DETAIL:  2215 index row versions were removed.
5 index pages have been deleted, 5 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_CopySrcID" now contains 149465 row versions in 3020 pages
DETAIL:  2215 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_DelivMethFID" now contains 149465 row versions in 3022 pages
DETAIL:  2215 index row versions were removed.
2 index pages have been deleted, 2 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_Delivery State FID" now contains 149465 row versions in 2426 pages
DETAIL:  2215 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_Delivery Zip Code" now contains 149465 row versions in 2285 pages
DETAIL:  2215 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_DiscountFID" now contains 149465 row versions in 3015 pages
DETAIL:  2215 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_EntityWAuthorityFID" now contains 149465 row versions in 1503 pages
DETAIL:  2215 index row versions were removed.
27 index pages have been deleted, 27 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_ID" now contains 149465 row versions in 1130 pages
DETAIL:  316 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_InsertFlag" now contains 149465 row versions in 2980 pages
DETAIL:  1616 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_ItemFlow2" now contains 149465 row versions in 2267 pages
DETAIL:  2215 index row versions were removed.
30 index pages have been deleted, 29 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_LastSerCaptureTypeID" now contains 149465 row versions in 2618 pages
DETAIL:  2215 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_MastInv_FID" now contains 149465 row versions in 1514 pages
DETAIL:  1883 index row versions were removed.
69 index pages have been deleted, 69 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_OpCarrFID" now contains 149465 row versions in 1496 pages
DETAIL:  2215 index row versions were removed.
29 index pages have been deleted, 29 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_OrderDate" now contains 149465 row versions in 1529 pages
DETAIL:  240 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_OrderFID" now contains 149465 row versions in 1224 pages
DETAIL:  161 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_PowerU_FID" now contains 149465 row versions in 1851 pages
DETAIL:  2072 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_Representative" now contains 149465 row versions in 3506 pages
DETAIL:  2215 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_SelValidUseFID" now contains 149465 row versions in 1753 pages
DETAIL:  1995 index row versions were removed.
14 index pages have been deleted, 14 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_SerialAuthCode" now contains 149465 row versions in 3023 pages
DETAIL:  2215 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_SerialNum" now contains 149465 row versions in 2636 pages
DETAIL:  2215 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_SourceID" now contains 149465 row versions in 2948 pages
DETAIL:  2215 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_StatusReasonFID" now contains 149465 row versions in 1467 pages
DETAIL:  313 index row versions were removed.
51 index pages have been deleted, 51 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_SupplyOrderKey" now contains 149465 row versions in 2916 pages
DETAIL:  2215 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_TrailerU_FID" now contains 149465 row versions in 2549 pages
DETAIL:  2177 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "Order Items_VendorFID" now contains 149465 row versions in 1693 pages
DETAIL:  2215 index row versions were removed.
7 index pages have been deleted, 7 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "Order Items": found 64 removable, 53611 nonremovable row versions in 2591 out of 10201 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 166068 unused item pointers.
0 pages are entirely empty.
CPU 0.14s/0.34u sec elapsed 2.42 sec.

INFO:  vacuuming "pg_toast.pg_toast_402310"

INFO:  index "pg_toast_402310_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "pg_toast_402310": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

Time: 2.569s

Affected rows: 0