On 8/17/23 13:01, rihad wrote:
Hi, all. After calling pg_stat_reset all statistics used by autovacuum
got zeroed, and started accumulating from scratch. Some tables get
acted upon properly, some don't.
Self-replying: yup, it seems there's an arbitrary limit of 100K of
n_live_tup after which autovac/analyze kicks in, or it seems so.
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.
foo=> select
relname,n_live_tup,n_dead_tup,cast(round(n_live_tup*cast(current_setting('autovacuum_vacuum_scale_factor')
as float)+current_setting('autovacuum_vacuum_threshold')::int) as
int)-n_dead_tup as left, n_mod_since_analyz
e,cast(round(n_live_tup*cast(current_setting('autovacuum_analyze_scale_factor')
as float)+current_setting('autovacuum_analyze_threshold')::int) as
int)-n_mod_since_analyze as
left,n_ins_since_vacuum,last_autovacuum,last_autoanalyze from p
g_stat_all_tables where n_live_tup>0 and
n_dead_tup>=current_setting('autovacuum_vacuum_threshold')::int order
by 8 nulls first, 4;
relname | n_live_tup | n_dead_tup | left |
n_mod_since_analyze | left | n_ins_since_vacuum |
last_autovacuum | last_autoanalyze
--------------------------+------------+------------+----------+---------------------+---------+--------------------+-------------------------------+-------------------------------
fooooooooooo | 32781 | 240663 | -234057 |
513265 | -509937 | 270291 |
|
fooo | 40 | 24891 | -24833 |
49822 | -49768 | 24931 |
|
foooo | 46 | 18991 | -18932 |
19099 | -19044 | 46 |
|
fooooo | 1 | 12687 | -12637 |
40795 | -40745 | 1 |
|
fooooooooooooo | 2393 | 11115 | -10586 |
137599 | -137310 | 2393 |
|
fooooooooo | 9465 | 11919 | -9976 |
352888 | -351892 | 9466 |
|
fooooooooooooooooooooooo | 26 | 2558 | -2503 |
188 | -135 | 2584 |
|
user_sessions | 118 | 1231 | -1157 |
19114 | -19052 | 118 |
|
fooooooooooooooooo | 32 | 562 | -506 |
226 | -173 | 594 |
|
fooooooo | 53 | 537 | -476 |
644 | -589 | 53 |
|
fooooooooooooo | 327 | 524 | -409 |
804 | -721 | 520 |
|
foooooooooooooooo | 46 | 104 | -45 |
457 | -402 | 183 |
|
foooooooooooooooooo | 34 | 93 | -36 |
158 | -105 | 34 |
|
foooooooooooooooo | 47 | 95 | -36 |
364 | -309 | 47 |
|
fooooooooooooooooo | 84 | 91 | -24 |
177 | -119 | 84 |
|
foooooooo | 290504401 | 9540832 | 48560098 |
26663449 | 2387041 | 8319194 | 2023-08-17
08:03:19.159161+00 | 2023-08-17 07:43:54.982375+00
fooooooooooooooooo | 43449 | 3823 | 4917 |
4190 | 205 | 377 | 2023-08-17
08:31:14.5573+00 | 2023-08-17 08:31:15.19454+00
fooooooooooooooooooooo | 3913 | 715 | 118 |
200 | 241 | 0 | 2023-08-17
08:44:04.721272+00 | 2023-08-17 08:47:04.875214+00
foooooooooooooooo | 73 | 63 | 2 |
31 | 26 | 35 | 2023-08-17
08:45:05.115824+00 | 2023-08-17 08:47:05.032251+00
foooooooooooo | 790249 | 126240 | 31860 |
4149 | 74926 | 119413 | 2023-08-17
08:46:10.625275+00 | 2023-08-17 08:49:04.867481+00
pg_shdepend | 1885 | 286 | 141 |
116 | 122 | 270 | 2023-08-17
08:47:03.638639+00 | 2023-08-17 08:48:42.903096+00
pg_index | 993 | 79 | 170 |
10 | 139 | 72 | 2023-08-17
08:48:03.67267+00 | 2023-08-17 08:49:03.723851+00
pg_depend | 9779 | 1027 | 979 |
130 | 898 | 923 | 2023-08-17
08:48:03.743417+00 | 2023-08-17 08:49:03.740913+00
foooooooooooooo | 43699 | 2352 | 6438 |
3527 | 893 | 1175 | 2023-08-17
08:48:03.84116+00 | 2023-08-17 08:48:03.93689+00
pg_attribute | 12478 | 432 | 2114 |
480 | 818 | 288 | 2023-08-17
08:49:03.558385+00 | 2023-08-17 08:49:03.652786+00
foooooooooooooooo | 3717 | 890 | -97 |
893 | -471 | 370 | 2023-08-17
08:49:04.941033+00 | 2023-08-17 08:49:04.94695+00
Juding by columns named "left", tables having negative values should
be vacuumed or analyzed, but they aren't.
The threshold for vacuuming is calculated as n_live_tup multiplied by
autovacuum_vacuum_scale_factore plus autovacuum_vacuum_threshold minus
n_dead_tup.
Accordingly for the analyze threshold.
Is there some kind of minimum n_live_tup that it must reach before
acing on the table? Those values are very low, they don't reflect the
real table size until after vacuum or analyze actually runs.
Thanks for any tips.
