Re: [HACKERS] stats_block_level
\On Jul 29, 2007, at 9:37 AM, Tom Lane wrote: Erik Jones [EMAIL PROTECTED] writes: improvement that went into that release. I could test turning it back on this week if you like -- I certainly would like to have my blks_read/cach_hits stats back. Toggling stats_block_level will respond to a reload, yes? Yes, as long as you had stats_start_collector on at startup. regards, tom lane Ok, we finally had a day with both our sysadmin and me in the office, flipped stats_block_level back on and noticed no noticable change in our iostats. We're going to leave it on and if anything starts to go crazy in the next couple of days I'll be sure to let you know. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
On Tue, 2007-07-31 at 12:33 -0400, Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. So what are we going to call the one surviving GUC variable? collect_stats In the patch recently submitted, I opted for stats_collection. Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] stats_block_level
Simon Riggs wrote: On Tue, 2007-07-31 at 12:33 -0400, Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. So what are we going to call the one surviving GUC variable? collect_stats In the patch recently submitted, I opted for stats_collection. I think we tend to give emphasis to the verb rather than the noun, e.g. redirect_stderr, log_connections. FWIW I just noticed we have a variable named krb_caseins_users which I think is not such a great name for it. Prolly best to change it now while it's still in the oven. Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. Hum, but the order in postgresql.conf is arbitrary, right? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] stats_block_level
On Tue, 2007-07-31 at 13:06 -0400, Alvaro Herrera wrote: Simon Riggs wrote: On Tue, 2007-07-31 at 12:33 -0400, Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. So what are we going to call the one surviving GUC variable? collect_stats In the patch recently submitted, I opted for stats_collection. I think we tend to give emphasis to the verb rather than the noun, e.g. redirect_stderr, log_connections. FWIW I just noticed we have a variable named krb_caseins_users which I think is not such a great name for it. Prolly best to change it now while it's still in the oven. Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. Hum, but the order in postgresql.conf is arbitrary, right? Yes, though the order in 'show all' is alphabetical. However, I agree with your comment on verb first, so lets do collect_stats. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] stats_block_level
Alvaro Herrera [EMAIL PROTECTED] writes: Simon Riggs wrote: Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. Hum, but the order in postgresql.conf is arbitrary, right? I concur with Simon that it should have some relationship to stats_query_string. However, stats_collection doesn't appeal to me because that sounds like it would subsume stats_query_string (it seems like a master control toggle, as stats_start_collector used to be). Maybe something like stats_count_events? Or we could get radical and rename both of them... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] stats_block_level
Alvaro Herrera [EMAIL PROTECTED] writes: FWIW I just noticed we have a variable named krb_caseins_users which I think is not such a great name for it. Prolly best to change it now while it's still in the oven. You're two releases too late for that one :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: FWIW I just noticed we have a variable named krb_caseins_users which I think is not such a great name for it. Prolly best to change it now while it's still in the oven. You're two releases too late for that one :-( Doh, I thought it was new in the GSSAPI code. Sorry. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] stats_block_level
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Simon Riggs wrote: Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. Hum, but the order in postgresql.conf is arbitrary, right? I concur with Simon that it should have some relationship to stats_query_string. However, stats_collection doesn't appeal to me because that sounds like it would subsume stats_query_string (it seems like a master control toggle, as stats_start_collector used to be). Maybe something like stats_count_events? Or we could get radical and rename both of them... Well, it is a bit misleading to have the query_string stuff be named stats when it's not actually collected by pgstats at all. Maybe rename it to collect_query_string. With the other name being collect_stats, they would show up together in SHOW ALL. I am not sure about using plural/singular though: why isn't it stats_query_strings instead? (We do have log_connections etc). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet? (Mafalda) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stats_block_level
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Or we could get radical and rename both of them... Well, it is a bit misleading to have the query_string stuff be named stats when it's not actually collected by pgstats at all. Maybe rename it to collect_query_string. With the other name being collect_stats, they would show up together in SHOW ALL. query_string is pretty misleading these days too, since pg_stat_activity includes a lot more than the bare query string. If we were doing this on a blank slate I would suggest track_stats and track_activities, but that might be too different from what people are used to. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
Alvaro Herrera wrote: Well, it is a bit misleading to have the query_string stuff be named stats when it's not actually collected by pgstats at all. By now, the statistics collector is unnoticeable to most users, since it's always on and you never have to do anything about it. The fact that not all things called statistics are managed by it should be pretty irrelevant. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] stats_block_level
Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Or we could get radical and rename both of them... Well, it is a bit misleading to have the query_string stuff be named stats when it's not actually collected by pgstats at all. Maybe rename it to collect_query_string. With the other name being collect_stats, they would show up together in SHOW ALL. query_string is pretty misleading these days too, since pg_stat_activity includes a lot more than the bare query string. FWIW I find having both the stats collector and the stats that analyze generates (ie, stats target) confusing. Really stats doesn't describe what information it's gathering, just that it's gathering some kind of information. Perhaps we should think of a term that describes what kind of information that is. collect_io_stats or collect_event_stats or something like that? Or even something without the word stats at all. But I can't think of anything good without it. If we were doing this on a blank slate I would suggest track_stats and track_activities, but that might be too different from what people are used to. I like track_events or track_activity though perhaps people might get them confused with trace... Sigh... and I swore I wouldn't get involved in any more name games... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Simon Riggs wrote: Methinks it should be: stats_something, so that people find it in the same place as stats_query_string, which is still there. Hum, but the order in postgresql.conf is arbitrary, right? I concur with Simon that it should have some relationship to stats_query_string. However, stats_collection doesn't appeal to me because that sounds like it would subsume stats_query_string (it seems like a master control toggle, as stats_start_collector used to be). Maybe something like stats_count_events? stats_enable_counters, or just stats_counters? We should prefix it with stats. I understand the verb issue, but putting the same prefix for the same module is more important --- effectively it is stats.collection. Someday we might even use dots so we can have multiple levels of detail, e.g. stats.block.accumulate or something like that. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stats_block_level
On Jul 27, 2007, at 6:45 PM, Jim Nasby wrote: On Jul 26, 2007, at 2:03 PM, Tom Lane wrote: So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. IIRC, the guys at Emma have seen a performance difference with stats_block_level off and row_level on, presumable due in part to having 150k tables. Erik? Kim? Well, we turned it off at the same time we moved from 8.2.3 to 8.2.4 so the actual culprit may have been what prompted the stats collector improvement that went into that release. I could test turning it back on this week if you like -- I certainly would like to have my blks_read/cach_hits stats back. Toggling stats_block_level will respond to a reload, yes? Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] stats_block_level
Erik Jones [EMAIL PROTECTED] writes: improvement that went into that release. I could test turning it back on this week if you like -- I certainly would like to have my blks_read/cach_hits stats back. Toggling stats_block_level will respond to a reload, yes? Yes, as long as you had stats_start_collector on at startup. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stats_block_level
On Jul 26, 2007, at 2:03 PM, Tom Lane wrote: So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. IIRC, the guys at Emma have seen a performance difference with stats_block_level off and row_level on, presumable due in part to having 150k tables. Erik? Kim? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] stats_block_level
Tom Lane wrote: Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. Maybe we don't actually need stats_start_collector, but instead we start it always and just have one knob to turn collection on and off. I'm not sure whether the extra process would bother people if they're not collecting, but we have so many extra processes now, why would anyone care. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stats_block_level
Simon Riggs wrote: On Fri, 2007-07-27 at 04:29 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: Tom Lane wrote: Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. Maybe we don't actually need stats_start_collector, but instead we start it always and just have one knob to turn collection on and off. I'm not sure whether the extra process would bother people if they're not collecting, but we have so many extra processes now, why would anyone care. I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. Having a single idle process is not a problem to anyone. It just sleeps all the time. We are all used to having six useless getty processes and nobody cares. Yes, thats a great plan. It gets my vote. /D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stats_block_level
Peter Eisentraut wrote: Tom Lane wrote: Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. Maybe we don't actually need stats_start_collector, but instead we start it always and just have one knob to turn collection on and off. I'm not sure whether the extra process would bother people if they're not collecting, but we have so many extra processes now, why would anyone care. I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. Having a single idle process is not a problem to anyone. It just sleeps all the time. We are all used to having six useless getty processes and nobody cares. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] stats_block_level
On Fri, 2007-07-27 at 04:29 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: Tom Lane wrote: Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. Maybe we don't actually need stats_start_collector, but instead we start it always and just have one knob to turn collection on and off. I'm not sure whether the extra process would bother people if they're not collecting, but we have so many extra processes now, why would anyone care. I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. Having a single idle process is not a problem to anyone. It just sleeps all the time. We are all used to having six useless getty processes and nobody cares. Yes, thats a great plan. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
On Fri, 2007-07-27 at 10:15 +0100, Dave Page wrote: Simon Riggs wrote: On Fri, 2007-07-27 at 04:29 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: Tom Lane wrote: Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. Maybe we don't actually need stats_start_collector, but instead we start it always and just have one knob to turn collection on and off. I'm not sure whether the extra process would bother people if they're not collecting, but we have so many extra processes now, why would anyone care. I agree. Let's remove stats_start_collector and merge the other two into a single setting. Anything more than that is overkill. Having a single idle process is not a problem to anyone. It just sleeps all the time. We are all used to having six useless getty processes and nobody cares. Yes, thats a great plan. It gets my vote. Look to -patches for an implementation of the above. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stats_block_level
Dave Page [EMAIL PROTECTED] writes: Tom Lane wrote: So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] stats_block_level
I wrote: Simon Riggs [EMAIL PROTECTED] writes: Anybody got any objection to setting it on by default? Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. On second thought ... the cost of incrementing n_blocks_read etc is certainly negligible. The overhead comes from sending messages to the collector, having the collector maintain table entries, writing those entries out to a file, etc. And AFAICS all that overhead is expended per table: if you touch a relation during a transaction, the ensuing costs are identical no matter whether you have stats_block_level or stats_row_level or both turned on. Furthermore, it seems pretty likely that a transaction that creates any row-level counts for a table will also create block-level counts, and vice versa. So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] stats_block_level
Why is stats_block_level = off by default? Is there a measurable cost to enabling this? We already have stats_row_level = on, so presumably the overhead of setting stats_block_level to on cannot be any worse than that. Anybody got any objection to setting it on by default? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stats_block_level
Tom Lane wrote: So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. Any reason not to just fold them both into stats_start_collector ? Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] stats_block_level
Simon Riggs [EMAIL PROTECTED] writes: Anybody got any objection to setting it on by default? Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
Satoshi Nagayasu [EMAIL PROTECTED] writes: I think the stats stuff should be on by default even if it causes some performance penalty. Because when we have performance problems on the production system, it needs more performance penalty (about 5%~) to measure the stats by turning their params on. In real scenario, we always need the performance information, so we always need to turn. So I want the performance information can be taken by default. I don't really agree with this argument. I've been reading pgsql-performance for some years now, and I can't recall any incident whatsoever in which we asked somebody for their stats_block_level numbers. To be honest I think those numbers are just about useless. However, in the current state of the system it seems to be nearly free to collect them if we are collecting row-level stats, and since that's happening by default as of 8.3, it's probably worth simplifying the user-visible behavior by collecting both sets of stats if we collect either. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] stats_block_level
Tom Lane wrote: I wrote: Simon Riggs [EMAIL PROTECTED] writes: Anybody got any objection to setting it on by default? Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. On second thought ... the cost of incrementing n_blocks_read etc is certainly negligible. The overhead comes from sending messages to the collector, having the collector maintain table entries, writing those entries out to a file, etc. And AFAICS all that overhead is expended per table: if you touch a relation during a transaction, the ensuing costs are identical no matter whether you have stats_block_level or stats_row_level or both turned on. Furthermore, it seems pretty likely that a transaction that creates any row-level counts for a table will also create block-level counts, and vice versa. So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. Agreed. Jan had a tendency to add more GUCs than needed just in case, but usually case never happened. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stats_block_level
Tom, Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. I think the stats stuff should be on by default even if it causes some performance penalty. Because when we have performance problems on the production system, it needs more performance penalty (about 5%~) to measure the stats by turning their params on. In real scenario, we always need the performance information, so we always need to turn. So I want the performance information can be taken by default. Just my thought. Tom Lane wrote: I wrote: Simon Riggs [EMAIL PROTECTED] writes: Anybody got any objection to setting it on by default? Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. On second thought ... the cost of incrementing n_blocks_read etc is certainly negligible. The overhead comes from sending messages to the collector, having the collector maintain table entries, writing those entries out to a file, etc. And AFAICS all that overhead is expended per table: if you touch a relation during a transaction, the ensuing costs are identical no matter whether you have stats_block_level or stats_row_level or both turned on. Furthermore, it seems pretty likely that a transaction that creates any row-level counts for a table will also create block-level counts, and vice versa. So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-50-5546-2496 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
