On 8/21/23 20:00, Adrian Klaver wrote:
On 8/20/23 22:31, Rihad wrote:
On 8/21/23 00:15, Adrian Klaver wrote:
On 8/20/23 12:10, Rihad wrote:
On 8/20/23 20:22, Adrian Klaver wrote:
On 8/18/23 22:35, Rihad wrote:
On 8/17/23 13:01, rihad wrote:
Hard to say without seeing the actual settings in postgresql.conf
that match:
https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR
Most importantly:
autovacuum
and
track_counts
https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS
They are both on and set as per default. Autovac/analyze continue
running on some tables after pg_stat_reset. Just not on all of them,
even thought they should judging by live/dead tuples calculation.
foo=> show track_counts;
track_counts
--------------
on
(1 row)
foo=> show autovacuum;
autovacuum
------------
on
(1 row)
How about the rest of the settings at?:
https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
Have the storage parameters for the tables been changed per?:
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
In psql you can do:
\d+ <table_name>
The setting if changed will show up as Options: <setting>
Also are there include directives in use per?:
https://www.postgresql.org/docs/current/config-setting.html#CONFIG-INCLUDES
You can see by looking at the sourcefile field in pg_settings:
https://www.postgresql.org/docs/current/view-pg-settings.html
Thanks for the detailed reply, no tables have custom settings.
I need to make it clear once again that all autovac/analyze work as
expected when n_live_tup matches reality, i.e. when analyze has been run
on them since last reset.
A way to fix this is to simply analyze the whole database. Before doing
that, while n_live_tup starts from basically 0 and grows based on DB
activity, these usual calculations of 10-20% table size for
vacuum/analyze don't work. They don't trigger autovac for most tables,
or do it much much later.
There are still many tables waiting for their turn, which is long
due.
Although there are some tables having only 60-70 (not 60-70K)
n_live_tup that have had autovacuum run on them. Weird.
