stats_block_level

"Simon Riggs" <simon@2ndquadrant.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@2ndquadrant.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@postgresql.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@2ndquadrant.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@nttdata.co.jp>
Phone: +81-50-5546-2496

Tom Lane wrote:

I wrote:

"Simon Riggs" <simon@2ndquadrant.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@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Satoshi Nagayasu <nagayasus@nttdata.co.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@nasby.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@myemma.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@myemma.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@commandprompt.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@commandprompt.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@commandprompt.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@commandprompt.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.