From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Sebastien Arod <sbnarod@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: pg_stat_all_tables not updated when VACUUM ANALYZE execution finishes
Date: Mon, 9 Apr 2018 06:42:35 -0700
Message-ID: <21460d07-3dd0-cce1-8201-883cb1fa0a76@aklaver.com> (raw)
In-Reply-To: <CADd42iFm8k_JvuoaY8kX+uZ5Hbgntpz=PYy0BfuyiNJiU552yg@mail.gmail.com>
References: <CADd42iFm8k_JvuoaY8kX+uZ5Hbgntpz=PYy0BfuyiNJiU552yg@mail.gmail.com>
On 04/09/2018 03:37 AM, Sebastien Arod wrote:
> Hi,
>
> I face a surprising behaviour with VACUUM ANALYZE.
>
> For a table with a structure like like this (and few records):
> create table my_table (
> my_column numeric
> );
>
> When I run the following:
> VACUUM ANALYZE my_table;
> SELECT relname, last_analyze, last_vacuum FROM pg_stat_all_tables where
> relname='my_table';
>
> The select returns null values for last_analyze and last_vacuum.
>
> However if I wait a little between the end of VACUUM command and the
> execution of the select the last_xxx columns have non null values.
>
> So it looks like something is done asynchronously here but I'm not sure
> what?
https://www.postgresql.org/docs/10/static/monitoring-stats.html
28.2.2. Viewing Statistics
"When using the statistics to monitor collected data, it is important to
realize that the information does not update instantaneously. Each
individual server process transmits new statistical counts to the
collector just before going idle; so a query or transaction still in
progress does not affect the displayed totals. Also, the collector
itself emits a new report at most once per PGSTAT_STAT_INTERVAL
milliseconds (500 ms unless altered while building the server). So the
displayed information lags behind actual activity. However,
current-query information collected by track_activities is always
up-to-date.
"
> * Is it the vacuum itself that run asyncrhonously or the update of or
> the content of the pg_stat_all_tables view?
> * If I execute another query right after "VACUUM ANALYZE" is it expected
> that this other query will benefit from the analyze done in the VACUUM
> ANALYZE call?
> * Is this a bug or a normal behaviour? (I'm using postgresql 9.6)
> * Is there a way to wait for pg_stat_all_tables to be up to date? To
> give a bit of context I was planning to use this information in the
> assertion part of a test case I wrote to check vacuum were executed as
> expected but my test is flaky because of this behaviour.
>
>
> -Seb
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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: adrian.klaver@aklaver.com, sbnarod@gmail.com, pgsql-general@lists.postgresql.org
Subject: Re: pg_stat_all_tables not updated when VACUUM ANALYZE execution finishes
In-Reply-To: <21460d07-3dd0-cce1-8201-883cb1fa0a76@aklaver.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