Re: statistics for shared catalogs not updated when autovacuum is off

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: statistics for shared catalogs not updated when autovacuum is off
Date: 2016-05-21 22:43:54
Message-ID: CAB7nPqR+0EqQEcZBsT7reaFqVYe7D+=SYfxroTSv_ujWXU2QVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Feb 2, 2016 at 10:38 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 2/1/16 7:20 PM, Peter Eisentraut wrote:
>> That's probably right. Even with autovacuum on, the statistics for
>> shared catalogs do not appear as updated right away. That is, if you
>> run VACUUM and then look at pg_stat_sys_tables right away, you will see
>> the stats for shared catalogs to be slightly out of date until the
>> minutely autovacuum check causes them to update.
>>
>> So the problem exists in general, but the autovacuum launcher papers
>> over it every minute.
>
> I suspect the issue is in backend_read_statsfile(). Presumably the if just
> needs a call to AutoVacuumingActive() added:
>
> The interesting thing is that we always start the launcher one time, to
> protect against wraparound, but apparently that path doesn't call anything
> that calls backend_read_statsfile() (which is static).

The problem is different I think. Since 9.3, database-related
statistics are located on separate files. And the statistics of shared
tables is visibly located in a file with database name set as
InvalidOid, leading to the presence of db_0.stat in pg_stat_tmp. So
the fix for shared relations is to make sure that
backend_read_statsfile can load the file dedicated to shared objects
when data from it is needed, like pg_database stats. So making
backend_read_statsfile a bit smarter looks like the good answer to me.

At the same time I am not getting why pgstat_fetch_stat_tabentry needs
to be that complicated. Based on the relation OID we can know if it is
a shared relation or not, there is no point in doing two times the
same lookup in the pgstat hash table.

Attached is a patch that fixes the issue here:
=# show autovacuum;
autovacuum
------------
off
(1 row)
=# select seq_scan from pg_stat_sys_tables where relname = 'pg_database';
seq_scan
----------
2
(1 row)
=# select count(*) from pg_database;
count
-------
4
(1 row)
=# select seq_scan from pg_stat_sys_tables where relname = 'pg_database';
seq_scan
----------
3
(1 row)
--
Michael

Attachment Content-Type Size
pgstat-shared-catalogs.patch application/x-download 5.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2016-05-21 22:47:43 Re: Latent cache flush hazard in RelationInitIndexAccessInfo
Previous Message Michael Paquier 2016-05-21 22:21:10 Re: Parallel safety tagging of extension functions