Обсуждение: Where does data in pg_stat_user_tables come from?
All,
I've been looking at pg_stat_user_tables (in 8.3, because of a project I
have), and it appears that autovacuum, and only autovaccum, updates the
data for this view. This means that one can never have data in
pg_stat_user_tables which is completely up-to-date, and if autovacuum is
off, the view is useless.
Am I reading this correctly? If so, shouldn't this be a TODO -- or is
it fixed already in 9.0?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote: > All, > > I've been looking at pg_stat_user_tables (in 8.3, because of a project I > have), and it appears that autovacuum, and only autovaccum, updates the > data for this view. This means that one can never have data in > pg_stat_user_tables which is completely up-to-date, and if autovacuum is > off, the view is useless. As I recall its kept in shared_buffers (in some kind of counter) and updated only when it is requested or when autovacuum fires. This was done because we used to write stats every 500ms and it was a bottleneck. (IIRC) Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On 9/13/10 4:41 PM, Joshua D. Drake wrote:
> On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote:
>> All,
>>
>> I've been looking at pg_stat_user_tables (in 8.3, because of a project I
>> have), and it appears that autovacuum, and only autovaccum, updates the
>> data for this view. This means that one can never have data in
>> pg_stat_user_tables which is completely up-to-date, and if autovacuum is
>> off, the view is useless.
>
> As I recall its kept in shared_buffers (in some kind of counter) and
> updated only when it is requested or when autovacuum fires. This was
> done because we used to write stats every 500ms and it was a bottleneck.
> (IIRC)
Yes, looks like it only gets updated on SELECT or on autovacuum.
Thing is, a full VACUUM ANALYZE on the database, or even just ANALYZE,
should update some of the counters. And currently it doesnt, resulting
in pg_class.reltuples often being far more up to date than
pg_stat_user_tables.n_live_tup. And frankly, no way to reconcile those
two stats.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On Mon, 2010-09-13 at 16:47 -0700, Josh Berkus wrote: > On 9/13/10 4:41 PM, Joshua D. Drake wrote: > > On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote: > >> All, > >> > >> I've been looking at pg_stat_user_tables (in 8.3, because of a project I > >> have), and it appears that autovacuum, and only autovaccum, updates the > >> data for this view. This means that one can never have data in > >> pg_stat_user_tables which is completely up-to-date, and if autovacuum is > >> off, the view is useless. > > > > As I recall its kept in shared_buffers (in some kind of counter) and > > updated only when it is requested or when autovacuum fires. This was > > done because we used to write stats every 500ms and it was a bottleneck. > > (IIRC) > > Yes, looks like it only gets updated on SELECT or on autovacuum. > > Thing is, a full VACUUM ANALYZE on the database, or even just ANALYZE, > should update some of the counters. And currently it doesnt, resulting > in pg_class.reltuples often being far more up to date than > pg_stat_user_tables.n_live_tup. And frankly, no way to reconcile those > two stats. If you select from pg_stat_user_tables, the counters should be reasonably close unless your default_statistics_target is way off and then pg_class.reltuples would be wrong. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> If you select from pg_stat_user_tables, the counters should be
> reasonably close unless your default_statistics_target is way off and
> then pg_class.reltuples would be wrong.
At least in 8.3, running ANALYZE does not update pg_stat_user_tables in
any way. Does it in later versions?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> I've been looking at pg_stat_user_tables (in 8.3, because of a project I
> have), and it appears that autovacuum, and only autovaccum, updates the
> data for this view.
Um ... it updates the last_autovacuum and last_autoanalyze columns,
but the others are not its responsibility.
regards, tom lane
Excerpts from Josh Berkus's message of lun sep 13 20:53:51 -0400 2010: > > > If you select from pg_stat_user_tables, the counters should be > > reasonably close unless your default_statistics_target is way off and > > then pg_class.reltuples would be wrong. > > At least in 8.3, running ANALYZE does not update pg_stat_user_tables in > any way. Does it in later versions? It's been pure nonsense in this thread. Please show an example of what's not working. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote: > All, > > I've been looking at pg_stat_user_tables (in 8.3, because of a project I > have), and it appears that autovacuum, and only autovaccum, updates the > data for this view. This means that one can never have data in > pg_stat_user_tables which is completely up-to-date, and if autovacuum is > off, the view is useless. As I recall its kept in shared_buffers (in some kind of counter) and updated only when it is requested or when autovacuum fires. This was done because we used to write stats every 500ms and it was a bottleneck. (IIRC) Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Mon, 2010-09-13 at 16:47 -0700, Josh Berkus wrote: > On 9/13/10 4:41 PM, Joshua D. Drake wrote: > > On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote: > >> All, > >> > >> I've been looking at pg_stat_user_tables (in 8.3, because of a project I > >> have), and it appears that autovacuum, and only autovaccum, updates the > >> data for this view. This means that one can never have data in > >> pg_stat_user_tables which is completely up-to-date, and if autovacuum is > >> off, the view is useless. > > > > As I recall its kept in shared_buffers (in some kind of counter) and > > updated only when it is requested or when autovacuum fires. This was > > done because we used to write stats every 500ms and it was a bottleneck. > > (IIRC) > > Yes, looks like it only gets updated on SELECT or on autovacuum. > > Thing is, a full VACUUM ANALYZE on the database, or even just ANALYZE, > should update some of the counters. And currently it doesnt, resulting > in pg_class.reltuples often being far more up to date than > pg_stat_user_tables.n_live_tup. And frankly, no way to reconcile those > two stats. If you select from pg_stat_user_tables, the counters should be reasonably close unless your default_statistics_target is way off and then pg_class.reltuples would be wrong. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> It's been pure nonsense in this thread. Please show an example of
> what's not working.
1) Init a postgresql 8.3 with autovacuum disabled.
2) Load a backup of a database into that PostgreSQL.
3) Check pg_stat_user_tables. n_live_tup for all tables will be 0.
4) VACUUM ANALYZE the whole database.
5) n_live_tup will *still* be 0. Whereas reltuples in pg_class will be
reasonable accurate.
> Um ... it updates the last_autovacuum and last_autoanalyze columns,
> but the others are not its responsibility.
Right. I'm contending that ANALYZE *should* update those columns.
Current behavior is unintuitive and makes the stats in
pg_stat_user_tables almost useless, since you can never get even
approximately a coherent snapshot of data for all tables.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Le 16/09/2010 20:39, Josh Berkus a écrit : > >> It's been pure nonsense in this thread. Please show an example of >> what's not working. > > 1) Init a postgresql 8.3 with autovacuum disabled. > > 2) Load a backup of a database into that PostgreSQL. > > 3) Check pg_stat_user_tables. n_live_tup for all tables will be 0. > > 4) VACUUM ANALYZE the whole database. > > 5) n_live_tup will *still* be 0. Whereas reltuples in pg_class will be > reasonable accurate. > Did all your steps (except the fourth one). Works great (meaning n_live_tup is updated as it should be). I have to agree with Alvarro, this is complete nonsense. VACUUM ANALYZE doesn't change the pg_stat_*_tables columns value, the stats collector does. If your n_live_tup didn't get updated, I'm quite sure you have track_counts to off in your postgresql.conf file. >> Um ... it updates the last_autovacuum and last_autoanalyze columns, >> but the others are not its responsibility. > > Right. I'm contending that ANALYZE *should* update those columns. The postgres process executing ANALYZE surely sent this information to the stats collector (once again, if track_counts is on). Tried it tonight, works great too. > Current behavior is unintuitive and makes the stats in > pg_stat_user_tables almost useless, since you can never get even > approximately a coherent snapshot of data for all tables. > Get a look at your track_count setting. -- Guillaume http://www.postgresql.fr http://dalibo.com
Josh Berkus <josh@agliodbs.com> writes:
>> It's been pure nonsense in this thread. Please show an example of
>> what's not working.
> 1) Init a postgresql 8.3 with autovacuum disabled.
> 2) Load a backup of a database into that PostgreSQL.
> 3) Check pg_stat_user_tables. n_live_tup for all tables will be 0.
Really? It works for me. You sure this installation hasn't got stats
disabled? Check the beginning of the postmaster log to see if there
are any bleats about failing to start the stats collector.
> 4) VACUUM ANALYZE the whole database.
> 5) n_live_tup will *still* be 0. Whereas reltuples in pg_class will be
> reasonable accurate.
It's possible you are seeing the effects of the fact that pre-9.0,
vacuum and analyze wouldn't create a stats entry for a table that
didn't have one already. However, it's entirely not clear why you
wouldn't have one already. Also, if you didn't, you wouldn't see any
row at all in the pg_stat_user_tables, not a row with n_live_tup = 0.
In any case, it's clear that your installation is not operating as
intended, and as 8.3 does work for me here. Better look for something
interfering with stats collection.
regards, tom lane
On 9/16/10 12:14 PM, Tom Lane wrote:
> In any case, it's clear that your installation is not operating as
> intended, and as 8.3 does work for me here. Better look for something
> interfering with stats collection.
OK, will do. Thanks!
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com