pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhere does data in pg_stat_user_tables come from?
11+ messages / 5 participants
[nested] [flat]
* Where does data in pg_stat_user_tables come from?
@ 2010-09-13 23:06 Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 01:40 ` Re: Where does data in pg_stat_user_tables come from? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2010-09-13 23:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-13 23:41 ` Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2010-09-13 23:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
@ 2010-09-13 23:47 ` Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2010-09-13 23:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-14 00:12 ` Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Joshua D. Drake @ 2010-09-14 00:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
@ 2010-09-14 00:53 ` Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Re: Where does data in pg_stat_user_tables come from? Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2010-09-14 00:53 UTC (permalink / raw)
To: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-14 03:57 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-09-16 18:39 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2010-09-14 03:57 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Re: Where does data in pg_stat_user_tables come from? Alvaro Herrera <alvherre@commandprompt.com>
@ 2010-09-16 18:39 ` Josh Berkus <josh@agliodbs.com>
2010-09-16 19:02 ` Re: Where does data in pg_stat_user_tables come from? Guillaume Lelarge <guillaume@lelarge.info>
2010-09-16 19:14 ` Re: Where does data in pg_stat_user_tables come from? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2010-09-16 18:39 UTC (permalink / raw)
To: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Re: Where does data in pg_stat_user_tables come from? Alvaro Herrera <alvherre@commandprompt.com>
2010-09-16 18:39 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-16 19:02 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2010-09-16 19:02 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Re: Where does data in pg_stat_user_tables come from? Alvaro Herrera <alvherre@commandprompt.com>
2010-09-16 18:39 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-16 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-16 19:34 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2010-09-16 19:14 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Re: Where does data in pg_stat_user_tables come from? Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Re: Where does data in pg_stat_user_tables come from? Alvaro Herrera <alvherre@commandprompt.com>
2010-09-16 18:39 ` Re: Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-16 19:14 ` Re: Where does data in pg_stat_user_tables come from? Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-16 19:34 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2010-09-16 19:34 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where does data in pg_stat_user_tables come from?
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
@ 2010-09-14 01:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2010-09-14 01:40 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-09-16 19:34 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-13 23:06 Where does data in pg_stat_user_tables come from? Josh Berkus <josh@agliodbs.com>
2010-09-13 23:41 ` Joshua D. Drake <jd@commandprompt.com>
2010-09-13 23:47 ` Josh Berkus <josh@agliodbs.com>
2010-09-14 00:12 ` Joshua D. Drake <jd@commandprompt.com>
2010-09-14 00:53 ` Josh Berkus <josh@agliodbs.com>
2010-09-14 03:57 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-09-16 18:39 ` Josh Berkus <josh@agliodbs.com>
2010-09-16 19:02 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-09-16 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-16 19:34 ` Josh Berkus <josh@agliodbs.com>
2010-09-14 01:40 ` Tom Lane <tgl@sss.pgh.pa.us>
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