pg.ddx.io pgsql-general@postgresql.org mailing list archive help / color / mirror / Atom feed From: Rihad <grihad@gmail.com>
To: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Date: Sat, 19 Aug 2023 09:35:25 +0400
Message-ID: <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com> (raw )
In-Reply-To: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru >
References: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru >
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.
>
view thread (13+ messages) latest in thread
Message-ID: <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com>
Permalink: ../18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com/
Also on: postgresql.org/message-id/18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: grihad@gmail.com
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
In-Reply-To: <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox