On 4/13/13 12:44 PM, Tomas Vondra wrote:
I'm currently struggling with (quite uncommon) deployments where databases are created/dropped regularly (not to mention tables and indexes), and it's surprisingly difficult to process such stats to get reasonable values.
Yes, it's a pain. If you aggregate the table level data available now, you'll end up with some missing activity. Work done between the last snapshot and when the drop happened is gone right now, whereas your aggregated stats view would preserve that activity. The real fun is if the new table has the same name as the old one, which gives you all the negative value headaches a pg_stat_reset introduces too.
It's possible to make a case for why database level aggregate statistics are useful. I don't know that yours is compelling enough to justify itself though, given that as you say this is an uncommon situation. In something similar to your setup I've just accepted that I have to save the snapshots into a table, will occasionally lose some mid-snapshot data, and I use a combination of correction updates to that table and SQL window functions to provide a useful view. It's a pain to do and I end up having to customize this approach for seemingly every install, but it can be made accurate enough for most uses.
The gigantic hole in this area I was most interested in for 9.4 development was the lack of write statistics. Not having pg_stat_database.blks_write, pg_statio_user_tables.heap_blks_write or pg_statio_user_indexes.idx_blks_write is a crippling operations/planning limitation of the database. From that perspective, now isn't quite the right time to add more aggregation on top of that data, since the aggregation will make adding additional counters a bit more complicated. It's not a big difference, but thinking in that direction doesn't help your suggestion.
-- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
