Thread: pg_stats how-to?

Anyone know of a pg_stats howto? I'd appreciate any direction.

Yudhvir

> Anyone know of a pg_stats howto? I'd appreciate any direction.

Let me know if you find one! :)

It isn't a HOWTO,  but I have collected some notes regarding the
performance views in a document -
http://docs.opengroupware.org/Members/whitemice/wmogag/file_view
 - see the chapter on PostgreSQL.  Hopefully this will continue to
expand.

The information is pretty scattered [ coming from a DB2 / Informix
background there there are specific performance guide],  try -
http://book.itzero.com/read/others/0508/Sams.PostgreSQL.2nd.Edition.Jul.2005_html/0672327562/ch04lev1sec2.html
http://www.postgresql.org/docs/8.0/interactive/monitoring-stats.html

Also just trolling on this list is useful.

--
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org

I am trying to use them. I have set these values in my conf file:

stats_start_collector	TRUE
stats_reset_on_server_start	FALSE
stats_command_string	TRUE

now what?

Yudhvir
==========




--
Yudhvir Singh Sidhu
408 375 3134 cell

Have you either re-loaded the config or restarted the server since
making those changes?

On Mon, May 14, 2007 at 09:16:54AM -0700, Y Sidhu wrote:
> I am trying to use them. I have set these values in my conf file:
>  stats_start_collector TRUE  stats_reset_on_server_start FALSE
> stats_command_string TRUE
> now what?
>
> Yudhvir
> ==========
>
> On 5/13/07, Shoaib Mir <shoaibmir@gmail.com> wrote:
> >
> >Can you be a little more specific? What exactly are you trying to achieve
> >with pg_stats?
> >
> >You can always get help for documentation at -->
> >http://www.postgresql.org/docs/8.2/static/view-pg-stats.html
> >
> >
> >--
> >Shoaib Mir
> >EnterpriseDB (www.enterprisedb.com)
> >
> >On 5/13/07, Yudhvir Singh Sidhu < ysidhu@gmail.com> wrote:
> >>
> >> Anyone know of a pg_stats howto? I'd appreciate any direction.
> >>
> >> Yudhvir
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 1: if posting/reading through Usenet, please send an appropriate
> >>        subscribe-nomail command to majordomo@postgresql.org so that your
> >>        message can get through to the mailing list cleanly
> >>
> >
> >
>
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell

--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Please include the list in your replies...

Ok, so you've got stats collection turned on. What's the question then?
And are stats_block_level and stats_row_level also enabled?

On Mon, May 14, 2007 at 09:28:46AM -0700, Y Sidhu wrote:
> yes
>
> Yudhvir
> ===
>
> On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:
> >
> >Have you either re-loaded the config or restarted the server since
> >making those changes?
> >
> >On Mon, May 14, 2007 at 09:16:54AM -0700, Y Sidhu wrote:
> >> I am trying to use them. I have set these values in my conf file:
> >>  stats_start_collector TRUE  stats_reset_on_server_start FALSE
> >> stats_command_string TRUE
> >> now what?
> >>
> >> Yudhvir
> >> ==========
> >>
> >> On 5/13/07, Shoaib Mir <shoaibmir@gmail.com> wrote:
> >> >
> >> >Can you be a little more specific? What exactly are you trying to
> >achieve
> >> >with pg_stats?
> >> >
> >> >You can always get help for documentation at -->
> >> >http://www.postgresql.org/docs/8.2/static/view-pg-stats.html
> >> >
> >> >
> >> >--
> >> >Shoaib Mir
> >> >EnterpriseDB (www.enterprisedb.com)
> >> >
> >> >On 5/13/07, Yudhvir Singh Sidhu < ysidhu@gmail.com> wrote:
> >> >>
> >> >> Anyone know of a pg_stats howto? I'd appreciate any direction.
> >> >>
> >> >> Yudhvir
> >> >>
> >> >> ---------------------------(end of
> >broadcast)---------------------------
> >> >> TIP 1: if posting/reading through Usenet, please send an appropriate
> >> >>        subscribe-nomail command to majordomo@postgresql.org so that
> >your
> >> >>        message can get through to the mailing list cleanly
> >> >>
> >> >
> >> >
> >>
> >>
> >> --
> >> Yudhvir Singh Sidhu
> >> 408 375 3134 cell
> >
> >--
> >Jim Nasby                                      decibel@decibel.org
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
>
>
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell

--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

The stats_block_level and stats_row_level are NOT enabled. The question is how to use pg_stats. Do I access/see them via the ANALYZE command? or using SQL. I cannot find any document which will get me started on this.

Yudhvir
--------------




--
Yudhvir Singh Sidhu
408 375 3134 cell

On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote:
> The stats_block_level and stats_row_level are NOT enabled. The question is
> how to use pg_stats. Do I access/see them via the ANALYZE command? or using
> SQL. I cannot find any document which will get me started on this.

Ok, we're both confused I think... I thought you were talking about the
pg_stat* views, which depend on the statistics collector (that's what
the stats_* parameters control).

That actually has nothing at all to do with pg_stats or pg_statistics.
Those deal with statistics about the data in the database, and not about
statistics from the engine (which is what the pg_stat* views do...).

If you want to know about pg_stats, take a look at
http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ...
but normally you shouldn't need to worry yourself about that. Are you
trying to debug something?

Information about the backend statistics can be found at
http://www.postgresql.org/docs/8.2/interactive/monitoring.html
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

I am sorry about this Jim, please understand that I am a newbie and am trying to solve long vacuum time problems and get a handle on speeding up queries/reports. I was pointed to pg_stats and that's where I am at now. I have added this into my conf file:

stats_start_collector	TRUE
stats_reset_on_server_start	FALSE
stats_command_string	TRUE

However, these being production servers, I have not enabled these:

stats_row_level

stats_block_level

Yes, I have re-started the server(s). It seems like I query tables to get the info. If so, are there any queries written that I can use?

Thanks for following up on this with me.

Yudhvir

===
On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:


--
Yudhvir Singh Sidhu
408 375 3134 cell

On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote:
> I am sorry about this Jim, please understand that I am a newbie and am
> trying to solve long vacuum time problems and get a handle on speeding up
> queries/reports. I was pointed to pg_stats and that's where I am at now. I

Well, I have no idea what that person was trying to convey then. What
are you trying to look up? Better yet, what's your actual problem?

> have added this into my conf file:
>  stats_start_collector TRUE  stats_reset_on_server_start FALSE
> stats_command_string TRUE
> However, these being production servers, I have not enabled these:
>  stats_row_level  stats_block_level
FYI, stats_command_string has a far larger performance overhead than any
of the other stats commands prior to 8.2.

> Yes, I have re-started the server(s). It seems like I query tables to get
> the info. If so, are there any queries written that I can use?
>
> Thanks for following up on this with me.
>
> Yudhvir
>
> ===
> On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:
> >
> >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote:
> >> The stats_block_level and stats_row_level are NOT enabled. The question
> >is
> >> how to use pg_stats. Do I access/see them via the ANALYZE command? or
> >using
> >> SQL. I cannot find any document which will get me started on this.
> >
> >Ok, we're both confused I think... I thought you were talking about the
> >pg_stat* views, which depend on the statistics collector (that's what
> >the stats_* parameters control).
> >
> >That actually has nothing at all to do with pg_stats or pg_statistics.
> >Those deal with statistics about the data in the database, and not about
> >statistics from the engine (which is what the pg_stat* views do...).
> >
> >If you want to know about pg_stats, take a look at
> >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ...
> >but normally you shouldn't need to worry yourself about that. Are you
> >trying to debug something?
> >
> >Information about the backend statistics can be found at
> >http://www.postgresql.org/docs/8.2/interactive/monitoring.html
> >--
> >Jim Nasby                                      decibel@decibel.org
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
>
>
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell

--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

My immediate problem is to decrease vacuum times.

Yudhvir
=======




--
Yudhvir Singh Sidhu
408 375 3134 cell

In response to "Y Sidhu" <ysidhu@gmail.com>:

> My immediate problem is to decrease vacuum times.

Don't take this as being critical, I'm just trying to point out a slight
difference between what you're doing and what you think you're doing:

Your problem is not decreasing vacuum times.  You _think_ that the solution
to your problem is decreasing vacuum times.  We don't know what your
actual problem is, and "decreasing vacuum times" may not be the correct
solution to it.

Please describe the _problem_.  Is vacuum causing performance issues while
it's running?  I mean, if vacuum takes a long time to run, so what -- what
is the actual _problem_ caused by vacuum taking long to run.

You may benefit by enabling autovacuum, or setting vacuum_cost_delay to
allow vacuum to run with less interference to other queries (for example).

Some details on what you're doing and what's happening would be helpful,
such as the output of vacuum verbose, details on the size of your database,
your hardware, how long vacuum is taking, what you feel is an acceptable
length of time, your PG config.

> On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:
> >
> > On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote:
> > > I am sorry about this Jim, please understand that I am a newbie and am
> > > trying to solve long vacuum time problems and get a handle on speeding
> > up
> > > queries/reports. I was pointed to pg_stats and that's where I am at now.
> > I
> >
> > Well, I have no idea what that person was trying to convey then. What
> > are you trying to look up? Better yet, what's your actual problem?
> >
> > > have added this into my conf file:
> > >  stats_start_collector TRUE  stats_reset_on_server_start FALSE
> > > stats_command_string TRUE
> > > However, these being production servers, I have not enabled these:
> > >  stats_row_level  stats_block_level
> > FYI, stats_command_string has a far larger performance overhead than any
> > of the other stats commands prior to 8.2.
> >
> > > Yes, I have re-started the server(s). It seems like I query tables to
> > get
> > > the info. If so, are there any queries written that I can use?
> > >
> > > Thanks for following up on this with me.
> > >
> > > Yudhvir
> > >
> > > ===
> > > On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:
> > > >
> > > >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote:
> > > >> The stats_block_level and stats_row_level are NOT enabled. The
> > question
> > > >is
> > > >> how to use pg_stats. Do I access/see them via the ANALYZE command? or
> > > >using
> > > >> SQL. I cannot find any document which will get me started on this.
> > > >
> > > >Ok, we're both confused I think... I thought you were talking about the
> > > >pg_stat* views, which depend on the statistics collector (that's what
> > > >the stats_* parameters control).
> > > >
> > > >That actually has nothing at all to do with pg_stats or pg_statistics.
> > > >Those deal with statistics about the data in the database, and not
> > about
> > > >statistics from the engine (which is what the pg_stat* views do...).
> > > >
> > > >If you want to know about pg_stats, take a look at
> > > >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ...
> > > >but normally you shouldn't need to worry yourself about that. Are you
> > > >trying to debug something?
> > > >
> > > >Information about the backend statistics can be found at
> > > >http://www.postgresql.org/docs/8.2/interactive/monitoring.html



--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill,

I suspect it is fragmentation of some sort. Vacuum times sometimes shoot up, it may be table fragmentation. What kind of tables? We have 2 of them which experience lots of adds and deletes only. No updates. So a typical day experiences record adds a few dozen times on the order of 2.5 million. And deletes once daily. Each of these tables has about 3 btree indexes. So, I am suspecting fragmentation, whatever that means, of the tables and indexes. I recover a couple of percentage points of a 73 GB SCSI disk when I run a REINDEX n those tables.


Yudhvir
=========



--
Yudhvir Singh Sidhu
408 375 3134 cell

"Y Sidhu" <ysidhu@gmail.com> writes:
> it may be table fragmentation. What kind of tables? We have 2 of them which
> experience lots of adds and deletes only. No updates. So a typical day
> experiences record adds a few dozen times on the order of 2.5 million. And
> deletes once daily. Each of these tables has about 3 btree indexes.

With an arrangement like that you should vacuum once daily, shortly
after the deletes --- there's really no point in doing it on any other
schedule.  Note "shortly" not "immediately" --- you want to be sure that
any transactions old enough to see the deleted rows have ended.

            regards, tom lane

On Mon, May 14, 2007 at 08:20:49PM -0400, Tom Lane wrote:
> "Y Sidhu" <ysidhu@gmail.com> writes:
> > it may be table fragmentation. What kind of tables? We have 2 of them which
> > experience lots of adds and deletes only. No updates. So a typical day
> > experiences record adds a few dozen times on the order of 2.5 million. And
> > deletes once daily. Each of these tables has about 3 btree indexes.
>
> With an arrangement like that you should vacuum once daily, shortly
> after the deletes --- there's really no point in doing it on any other
> schedule.  Note "shortly" not "immediately" --- you want to be sure that
> any transactions old enough to see the deleted rows have ended.

Also, think about ways you might avoid the deletes altogether. Could you
do a truncate instead? Could you use partitioning? If you are using
deletes then look at CLUSTERing the table some time after the deletes
(but be aware that prior to 8.3 CLUSTER doesn't fully obey MVCC).

To answer your original question, a way to take a look at how bloated
your tables are would be to ANALYZE, divide reltuples by relpages from
pg_class (gives how many rows per page you have) and compare that to 8k
/ average row size. The average row size for table rows would be the sum
of avg_width from pg_stats for the table + 24 bytes overhead. For
indexes, it would be the sum of avg_width for all fields in the index
plus some overhead (8 bytes, I think).

An even simpler alternative would be to install contrib/pgstattuple and
use the pgstattuple function, though IIRC that does read the entire
relation from disk.
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On 5/15/07, Jim C. Nasby <decibel@decibel.org> wrote:
Here are my results:

a.  SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class;

I get 66

b.  SELECT (8000/(sum(avg_width)+24)) as  table_stat FROM pg_stats;

I get 1


Yudhvir

Can you be a little more specific? What exactly are you trying to achieve with pg_stats?

You can always get help for documentation at --> http://www.postgresql.org/docs/8.2/static/view-pg-stats.html

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On Fri, May 18, 2007 at 04:26:05PM -0700, Y Sidhu wrote:
> >To answer your original question, a way to take a look at how bloated
> >your tables are would be to ANALYZE, divide reltuples by relpages from
> >pg_class (gives how many rows per page you have) and compare that to 8k
> >/ average row size. The average row size for table rows would be the sum
> >of avg_width from pg_stats for the table + 24 bytes overhead. For
> >indexes, it would be the sum of avg_width for all fields in the index
> >plus some overhead (8 bytes, I think).
> >
> >An even simpler alternative would be to install contrib/pgstattuple and
> >use the pgstattuple function, though IIRC that does read the entire
> >relation from disk.
> >--
> >Jim Nasby                                      decibel@decibel.org
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
>
> Here are my results:
>
> a.  SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class;
>
> I get 66
>
> b.  SELECT (8000/(sum(avg_width)+24)) as  table_stat FROM pg_stats;
>
> I get 1

And those results will be completely meaningless because they're
covering the entire database (catalog tables included). You need to
compare the two numbers on a table-by-table basis, and you'd also have
to ignore any small tables (say smaller than 1000 pages). Also, a page
is 8192 bytes in size (though granted there's a page header that's
something like 16 bytes).
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Thanks again! I'll make the change and get those numbers.

Yudhvir

On 5/21/07, Jim C. Nasby <decibel@decibel.org> wrote:
> On Fri, May 18, 2007 at 04:26:05PM -0700, Y Sidhu wrote:
> > >To answer your original question, a way to take a look at how bloated
> > >your tables are would be to ANALYZE, divide reltuples by relpages from
> > >pg_class (gives how many rows per page you have) and compare that to 8k
> > >/ average row size. The average row size for table rows would be the sum
> > >of avg_width from pg_stats for the table + 24 bytes overhead. For
> > >indexes, it would be the sum of avg_width for all fields in the index
> > >plus some overhead (8 bytes, I think).
> > >
> > >An even simpler alternative would be to install contrib/pgstattuple and
> > >use the pgstattuple function, though IIRC that does read the entire
> > >relation from disk.
> > >--
> > >Jim Nasby                                      decibel@decibel.org
> > >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> > >
> >
> > Here are my results:
> >
> > a.  SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class;
> >
> > I get 66
> >
> > b.  SELECT (8000/(sum(avg_width)+24)) as  table_stat FROM pg_stats;
> >
> > I get 1
>
> And those results will be completely meaningless because they're
> covering the entire database (catalog tables included). You need to
> compare the two numbers on a table-by-table basis, and you'd also have
> to ignore any small tables (say smaller than 1000 pages). Also, a page
> is 8192 bytes in size (though granted there's a page header that's
> something like 16 bytes).
> --
> Jim Nasby                                      decibel@decibel.org
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>


--
Yudhvir Singh Sidhu
408 375 3134 cell