Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?

Поиск
Список
Период
Сортировка
От rihad
Тема Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Дата
Msg-id 69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru
обсуждение исходный текст
Ответы Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Список pgsql-general

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.


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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "[Quipsy] Markus Karg"
Дата:
Сообщение: AW: AW: Cast INTEGER to BIT confusion
Следующее
От: "Abraham, Danny"
Дата:
Сообщение: Base files compatibility between PG11 and PG15