Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
Josh Berkus j...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where does data in pg_stat_user_tables come from?
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
