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 |
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 |