Re: pg_stats how-to? - Mailing list pgsql-performance

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