Re: [HACKERS] [GENERAL] autoanalyze criteria
On Wed, May 15, 2013 at 2:33 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 24/02/13 10:51, Mark Kirkwood wrote: On 24/02/13 10:12, Stefan Andreatta wrote: On 02/23/2013 09:30 PM, Jeff Janes wrote: Moved discussion from General To Hackers. On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: On 02/23/2013 05:10 PM, Jeff Janes wrote: Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a secret data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Sounds like a very good idea to me - any way I could help to make such a thing happen? It should be fairly easy to implement because the other columns are already there to show you the way, and if you want to try your hand at hacking pgsql it would be a good introduction to doing so. Look at each instance in the code of n_dead_dup and pg_stat_get_dead_tuples, and those are the places where changes_since_analyze also need to be addressed, in an analogous manner (assuming it is isn't already there.) git grep 'n_dead_tup' It looks like we would need to add an SQL function to retrieve the data, then incorporate that function into the view definitions that make up the pg_stat_user_tables etc. views. and of course update the regression test and the documentation. Other than implementing it, we would need to convince other hackers that this is desirable to have. I'm not sure how hard that would be. I've looked in the archives to see if this idea was already considered but rejected, but I don't see any indication that it was previously considered. (http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us). Cheers, Jeff Not being a developer, I am afraid, I will not be going to implement it myself - nor would anybody wish so ;-) I also searched the archives, but the closest I found is a discussion on the Admin List starting here: http://www.postgresql.org/message-id/626919622.7634700.1351695913466.javamail.r...@alaloop.com On the other hand, there is quite a lot of discussion about making autoanalyze more (or less) aggressive - which seems a difficult task to me, when you cannot even check what's triggering your autoanalyze. Anybody else interested? I was asked about this exact thing the other day - it would be very nice to have the information visible. I may take a look at doing it (I've done some hacking on the stats system previously). However don't let that put anyone else off - as I'll have to find the time to start :-) I happened to be looking at the whole autovacuum/analyze setup in another context - which reminded me about volunteering to take a look at a patch for adding changes_since_analyze. So with probably impeccably poor timing (smack in the middle of 9.3 beta), here is a patch that does that (so it is probably an early 9.4 addition). I've called the column n_changes_since_analyze - I can sense that there might be discussion about how to maybe shorten that :-) , and added a doc line for the view + updated the regression test expected input. Applied, with the changs suggested by Laurenz Albe in his review. Thanks! -- 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] [GENERAL] autoanalyze criteria
On 24/02/13 10:51, Mark Kirkwood wrote: On 24/02/13 10:12, Stefan Andreatta wrote: On 02/23/2013 09:30 PM, Jeff Janes wrote: Moved discussion from General To Hackers. On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: On 02/23/2013 05:10 PM, Jeff Janes wrote: Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a secret data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Sounds like a very good idea to me - any way I could help to make such a thing happen? It should be fairly easy to implement because the other columns are already there to show you the way, and if you want to try your hand at hacking pgsql it would be a good introduction to doing so. Look at each instance in the code of n_dead_dup and pg_stat_get_dead_tuples, and those are the places where changes_since_analyze also need to be addressed, in an analogous manner (assuming it is isn't already there.) git grep 'n_dead_tup' It looks like we would need to add an SQL function to retrieve the data, then incorporate that function into the view definitions that make up the pg_stat_user_tables etc. views. and of course update the regression test and the documentation. Other than implementing it, we would need to convince other hackers that this is desirable to have. I'm not sure how hard that would be. I've looked in the archives to see if this idea was already considered but rejected, but I don't see any indication that it was previously considered. (http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us). Cheers, Jeff Not being a developer, I am afraid, I will not be going to implement it myself - nor would anybody wish so ;-) I also searched the archives, but the closest I found is a discussion on the Admin List starting here: http://www.postgresql.org/message-id/626919622.7634700.1351695913466.javamail.r...@alaloop.com On the other hand, there is quite a lot of discussion about making autoanalyze more (or less) aggressive - which seems a difficult task to me, when you cannot even check what's triggering your autoanalyze. Anybody else interested? I was asked about this exact thing the other day - it would be very nice to have the information visible. I may take a look at doing it (I've done some hacking on the stats system previously). However don't let that put anyone else off - as I'll have to find the time to start :-) I happened to be looking at the whole autovacuum/analyze setup in another context - which reminded me about volunteering to take a look at a patch for adding changes_since_analyze. So with probably impeccably poor timing (smack in the middle of 9.3 beta), here is a patch that does that (so it is probably an early 9.4 addition). I've called the column n_changes_since_analyze - I can sense that there might be discussion about how to maybe shorten that :-) , and added a doc line for the view + updated the regression test expected input. Regards Mark diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b37b6c3..0ebce4e 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -951,6 +951,11 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re entryEstimated number of dead rows/entry /row row + entrystructfieldn_changes_since_analyze//entry + entrytypebigint//entry + entryEstimated number of row changes (inserts + updates + deletes) since the last analyze/entry +/row +row entrystructfieldlast_vacuum//entry entrytypetimestamp with time zone//entry entryLast time at which this table was manually vacuumed diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index a03bfa6..05bba74 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -405,6 +405,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(C.oid) AS n_live_tup, pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, +pg_stat_get_changes_since_analyze(C.oid) AS n_changes_since_analyze, pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, pg_stat_get_last_analyze_time(C.oid) as last_analyze, diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 8c1a767..8803996 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -34,6 +34,7 @@ extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS);
Re: [HACKERS] [GENERAL] autoanalyze criteria
Moved discussion from General To Hackers. On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta s.andrea...@synedra.comwrote: On 02/23/2013 05:10 PM, Jeff Janes wrote: Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a secret data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Sounds like a very good idea to me - any way I could help to make such a thing happen? It should be fairly easy to implement because the other columns are already there to show you the way, and if you want to try your hand at hacking pgsql it would be a good introduction to doing so. Look at each instance in the code of n_dead_dup and pg_stat_get_dead_tuples, and those are the places where changes_since_analyze also need to be addressed, in an analogous manner (assuming it is isn't already there.) git grep 'n_dead_tup' It looks like we would need to add an SQL function to retrieve the data, then incorporate that function into the view definitions that make up the pg_stat_user_tables etc. views. and of course update the regression test and the documentation. Other than implementing it, we would need to convince other hackers that this is desirable to have. I'm not sure how hard that would be. I've looked in the archives to see if this idea was already considered but rejected, but I don't see any indication that it was previously considered. (http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us). Cheers, Jeff
Re: [HACKERS] [GENERAL] autoanalyze criteria
On 02/23/2013 09:30 PM, Jeff Janes wrote: Moved discussion from General To Hackers. On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: On 02/23/2013 05:10 PM, Jeff Janes wrote: Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a secret data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Sounds like a very good idea to me - any way I could help to make such a thing happen? It should be fairly easy to implement because the other columns are already there to show you the way, and if you want to try your hand at hacking pgsql it would be a good introduction to doing so. Look at each instance in the code of n_dead_dup and pg_stat_get_dead_tuples, and those are the places where changes_since_analyze also need to be addressed, in an analogous manner (assuming it is isn't already there.) git grep 'n_dead_tup' It looks like we would need to add an SQL function to retrieve the data, then incorporate that function into the view definitions that make up the pg_stat_user_tables etc. views. and of course update the regression test and the documentation. Other than implementing it, we would need to convince other hackers that this is desirable to have. I'm not sure how hard that would be. I've looked in the archives to see if this idea was already considered but rejected, but I don't see any indication that it was previously considered. (http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us). Cheers, Jeff Not being a developer, I am afraid, I will not be going to implement it myself - nor would anybody wish so ;-) I also searched the archives, but the closest I found is a discussion on the Admin List starting here: http://www.postgresql.org/message-id/626919622.7634700.1351695913466.javamail.r...@alaloop.com On the other hand, there is quite a lot of discussion about making autoanalyze more (or less) aggressive - which seems a difficult task to me, when you cannot even check what's triggering your autoanalyze. Anybody else interested? Regards, Stefan
Re: [HACKERS] [GENERAL] autoanalyze criteria
On 24/02/13 10:12, Stefan Andreatta wrote: On 02/23/2013 09:30 PM, Jeff Janes wrote: Moved discussion from General To Hackers. On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: On 02/23/2013 05:10 PM, Jeff Janes wrote: Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a secret data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Sounds like a very good idea to me - any way I could help to make such a thing happen? It should be fairly easy to implement because the other columns are already there to show you the way, and if you want to try your hand at hacking pgsql it would be a good introduction to doing so. Look at each instance in the code of n_dead_dup and pg_stat_get_dead_tuples, and those are the places where changes_since_analyze also need to be addressed, in an analogous manner (assuming it is isn't already there.) git grep 'n_dead_tup' It looks like we would need to add an SQL function to retrieve the data, then incorporate that function into the view definitions that make up the pg_stat_user_tables etc. views. and of course update the regression test and the documentation. Other than implementing it, we would need to convince other hackers that this is desirable to have. I'm not sure how hard that would be. I've looked in the archives to see if this idea was already considered but rejected, but I don't see any indication that it was previously considered. (http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us). Cheers, Jeff Not being a developer, I am afraid, I will not be going to implement it myself - nor would anybody wish so ;-) I also searched the archives, but the closest I found is a discussion on the Admin List starting here: http://www.postgresql.org/message-id/626919622.7634700.1351695913466.javamail.r...@alaloop.com On the other hand, there is quite a lot of discussion about making autoanalyze more (or less) aggressive - which seems a difficult task to me, when you cannot even check what's triggering your autoanalyze. Anybody else interested? I was asked about this exact thing the other day - it would be very nice to have the information visible. I may take a look at doing it (I've done some hacking on the stats system previously). However don't let that put anyone else off - as I'll have to find the time to start :-) Regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
