Re: Is Autovacuum running?

On Tue, Feb 21, 2023 at 11:58 AM Christophe Pettus <xof(at)thebuild(dot)com> wrote:

>
>
> > On Feb 21, 2023, at 09:54, Brad White <b55white(at)gmail(dot)com> 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