Re: [HACKERS] More vacuum stats
Magnus Hagander writes: > On Mon, Aug 23, 2010 at 16:38, Tom Lane wrote: >> Magnus Hagander writes: >>> We could also store last_autovacuum_vacuum_duration - is that better >>> or worse than start and end time? >> >> No, I think you want to know the actual time not only the duration. > Well, you could calculate one from the other - especially if one takes > less size, per your comment above. With alignment considerations, adding a field is going to cost 8 bytes; whether it's a timestamp or a duration isn't going to matter. I'd be inclined to store the timestamp, it just seems more like the base datum. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
On Mon, Aug 23, 2010 at 16:38, Tom Lane wrote: > Magnus Hagander writes: >> On Mon, Aug 23, 2010 at 16:28, Greg Smith wrote: >>> What I actually want here is for the time that the last table autovacuum >>> started, adding to the finish time currently exposed by pg_stat_user_tables. > >> Now, that would be quite useful. That'd require another stats message, >> since we don't send anything on autovacuum start, but I don't think >> the overhead of that is anything we need to worry about - in >> comparison to an actual vacuum... > > No, you wouldn't really need an extra message, you could just send both > start and finish times in the completion message. I'm not sure that > having last start time update before last end time would be a good idea > anyway. Hmm, good point. We'd just need an extra field in that message. > But in any case it's true that an extra message wouldn't be a > significant cost. What I'd be more concerned about is the stats table > bloat from adding yet another per-table field. That could be a lot of > space on an installation with lots of tables. > >> We could also store last_autovacuum_vacuum_duration - is that better >> or worse than start and end time? > > No, I think you want to know the actual time not only the duration. Well, you could calculate one from the other - especially if one takes less size, per your comment above. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Magnus Hagander writes: > On Mon, Aug 23, 2010 at 16:28, Greg Smith wrote: >> What I actually want here is for the time that the last table autovacuum >> started, adding to the finish time currently exposed by pg_stat_user_tables. > Now, that would be quite useful. That'd require another stats message, > since we don't send anything on autovacuum start, but I don't think > the overhead of that is anything we need to worry about - in > comparison to an actual vacuum... No, you wouldn't really need an extra message, you could just send both start and finish times in the completion message. I'm not sure that having last start time update before last end time would be a good idea anyway. But in any case it's true that an extra message wouldn't be a significant cost. What I'd be more concerned about is the stats table bloat from adding yet another per-table field. That could be a lot of space on an installation with lots of tables. > We could also store last_autovacuum_vacuum_duration - is that better > or worse than start and end time? No, I think you want to know the actual time not only the duration. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
On Mon, Aug 23, 2010 at 16:28, Greg Smith wrote: > Tom Lane wrote: >> >> So I'd like to see a positive argument why this is important for users >> to know, rather than merely "we should expose every conceivable detail >> by default". Why wouldn't a user care more about last AV time for a >> specific table, which we already do expose? >> > > What I actually want here is for the time that the last table autovacuum > started, adding to the finish time currently exposed by pg_stat_user_tables. > "How long did the last {auto}vacuum on take to run?" is a FAQ on busy > systems here. If I could compute that from a pair of columns, it's a major > step toward answering even more interesting questions like "how does this > set of cost delay parameters turn into an approximate MB/s worth of > processing rate on my tables?". This is too important of a difficult tuning > exercise to leave to log scraping forever. Now, that would be quite useful. That'd require another stats message, since we don't send anything on autovacuum start, but I don't think the overhead of that is anything we need to worry about - in comparison to an actual vacuum... Do we want that for both vacuum and autovacuum? vacuum and analyze? We could also store last_autovacuum_vacuum_duration - is that better or worse than start and end time? > I'd rather have that and look at for "SELECT max(last_autovacuum_start) FROM > pg_stat_user_tables" to diagnose the sort of problems this patch seems to > aim at helping. Agreed. Consider this patch withdrawn. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Tom Lane wrote: So I'd like to see a positive argument why this is important for users to know, rather than merely "we should expose every conceivable detail by default". Why wouldn't a user care more about last AV time for a specific table, which we already do expose? What I actually want here is for the time that the last table autovacuum started, adding to the finish time currently exposed by pg_stat_user_tables. "How long did the last {auto}vacuum on take to run?" is a FAQ on busy systems here. If I could compute that from a pair of columns, it's a major step toward answering even more interesting questions like "how does this set of cost delay parameters turn into an approximate MB/s worth of processing rate on my tables?". This is too important of a difficult tuning exercise to leave to log scraping forever. I'd rather have that and look at for "SELECT max(last_autovacuum_start) FROM pg_stat_user_tables" to diagnose the sort of problems this patch seems to aim at helping. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Magnus Hagander writes: > On Sun, Aug 22, 2010 at 18:17, Tom Lane wrote: >> With the current AV launch algorithm, unless you have very serious >> system-wide issues there will be a worker launched into each database >> approximately every autovacuum_naptime seconds. AFAICS this does not >> tell you anything interesting about whether AV is getting its work done. > Well, if you have all your autovacuum workers tied up with vacuuming > large tables, then it wouldn't AFAIK. I'm not sure if that counts as > your "very serious system-wide issues", but it's certainly a case > that's interesting for the admin to know about. > But thinking more about that, you ca nfigure that out with a SELECT > count(*) FROM pg_stat_activity WHERE current_query LIKE 'autovacuum: > %' if I'm not mistaken. > It can also be used to find out if the launcher is somehoiw stuck, but > that would be a bug and we don't generally put counters in the stats > views to expose possible bugs, only to track interesting statistics. Yeah. Given the current worker-launch algorithm, these times just don't strike me as all that interesting in practice. If we were to change to a different algorithm, it's possible that it'd become worthwhile to expose them --- but it's equally possible that some other data would be useful instead. So my feeling remains that we should leave well enough alone. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
On Sun, Aug 22, 2010 at 18:17, Tom Lane wrote: > Magnus Hagander writes: >> On Sun, Aug 22, 2010 at 17:29, Tom Lane wrote: >>> So I'd like to see a positive argument why this is important for users >>> to know, rather than merely "we should expose every conceivable detail >>> by default". Why wouldn't a user care more about last AV time for a >>> specific table, which we already do expose? > >> You need to connect to every database to do that. If you have many >> databases, that's a lot of overhead particularly if you're doing tihs >> for regular monitoring. Plus, those views will only track when >> autovacuum actually *did* something. > > Well, the last-launch-time doesn't prove that autovacuum actually *did* > something ;-). Well, it would tell you it considered doing something ;) >> Being able to see that autovacuum hasn't even touched a database for >> too long would be an early-indicator that you have some issues with >> it. > > With the current AV launch algorithm, unless you have very serious > system-wide issues there will be a worker launched into each database > approximately every autovacuum_naptime seconds. AFAICS this does not > tell you anything interesting about whether AV is getting its work done. Well, if you have all your autovacuum workers tied up with vacuuming large tables, then it wouldn't AFAIK. I'm not sure if that counts as your "very serious system-wide issues", but it's certainly a case that's interesting for the admin to know about. But thinking more about that, you ca nfigure that out with a SELECT count(*) FROM pg_stat_activity WHERE current_query LIKE 'autovacuum: %' if I'm not mistaken. It can also be used to find out if the launcher is somehoiw stuck, but that would be a bug and we don't generally put counters in the stats views to expose possible bugs, only to track interesting statistics. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Magnus Hagander writes: > On Sun, Aug 22, 2010 at 17:29, Tom Lane wrote: >> So I'd like to see a positive argument why this is important for users >> to know, rather than merely "we should expose every conceivable detail >> by default". Why wouldn't a user care more about last AV time for a >> specific table, which we already do expose? > You need to connect to every database to do that. If you have many > databases, that's a lot of overhead particularly if you're doing tihs > for regular monitoring. Plus, those views will only track when > autovacuum actually *did* something. Well, the last-launch-time doesn't prove that autovacuum actually *did* something ;-). > Being able to see that autovacuum hasn't even touched a database for > too long would be an early-indicator that you have some issues with > it. With the current AV launch algorithm, unless you have very serious system-wide issues there will be a worker launched into each database approximately every autovacuum_naptime seconds. AFAICS this does not tell you anything interesting about whether AV is getting its work done. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
On Sun, Aug 22, 2010 at 17:29, Tom Lane wrote: > Magnus Hagander writes: >> I noticed that we were already tracking the information about when an >> autovacuum worker was last started in a database, but this information >> was not exposed. The attached patch puts this column in >> pg_stat_database. > >> Was there any particular reason why this wasn't exposed before that >> I've missed, making this a bad addition? :-) > > I think that's an implementation detail. If we expose it then we'll > be forced to track it forevermore, regardless of whether the AV launcher > actually needs it in the future. (In particular, the assumption that > this is tracked per-database and not per-something-else seems like an > artifact of the current AV launching algorithm.) That's a good point. OTOH, if we removed the feature, it seems it would be reasonable to remove the column from the statistics view as well. That *could* happen in other stats views as well. > So I'd like to see a positive argument why this is important for users > to know, rather than merely "we should expose every conceivable detail > by default". Why wouldn't a user care more about last AV time for a > specific table, which we already do expose? You need to connect to every database to do that. If you have many databases, that's a lot of overhead particularly if you're doing tihs for regular monitoring. Plus, those views will only track when autovacuum actually *did* something. Being able to see that autovacuum hasn't even touched a database for too long would be an early-indicator that you have some issues with it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Magnus Hagander writes: > I noticed that we were already tracking the information about when an > autovacuum worker was last started in a database, but this information > was not exposed. The attached patch puts this column in > pg_stat_database. > Was there any particular reason why this wasn't exposed before that > I've missed, making this a bad addition? :-) I think that's an implementation detail. If we expose it then we'll be forced to track it forevermore, regardless of whether the AV launcher actually needs it in the future. (In particular, the assumption that this is tracked per-database and not per-something-else seems like an artifact of the current AV launching algorithm.) So I'd like to see a positive argument why this is important for users to know, rather than merely "we should expose every conceivable detail by default". Why wouldn't a user care more about last AV time for a specific table, which we already do expose? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] More vacuum stats
Magnus Hagander escreveu: > Was there any particular reason why this wasn't exposed before that > I've missed, making this a bad addition? :-) > Not that I know of. Good catch. ;) -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] More vacuum stats
I noticed that we were already tracking the information about when an autovacuum worker was last started in a database, but this information was not exposed. The attached patch puts this column in pg_stat_database. Was there any particular reason why this wasn't exposed before that I've missed, making this a bad addition? :-) Oh, and this time, I *have* included updates to the regression tests. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ vac_db.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers