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

"Simon Riggs" <simon(at)2ndquadrant(dot)com> 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

I wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> 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

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.

Dave Page <dpage(at)postgresql(dot)org> 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

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" <simon(at)2ndquadrant(dot)com> 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 <nagayasus(at)nttdata(dot)co(dot)jp>
Phone: +81-50-5546-2496

Tom Lane wrote:
> I wrote:
> > "Simon Riggs" <simon(at)2ndquadrant(dot)com> 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> 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

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/

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 Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

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

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 jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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®
erik(at)myemma(dot)com
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

Erik Jones <erik(at)myemma(dot)com> 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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 2007-07-31 at 12:33 -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

Simon Riggs wrote:
> On Tue, 2007-07-31 at 12:33 -0400, Alvaro Herrera wrote:
> > Tom Lane wrote:
> > > Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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 Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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 <alvherre(at)commandprompt(dot)com> 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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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)

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

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/

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

\On Jul 29, 2007, at 9:37 AM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> 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®
erik(at)myemma(dot)com
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