Re: pg_stats how-to?

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
=========
On 5/14/07, Bill Moran <wmoran(at)collaborativefusion(dot)com> wrote:
>
> In response to "Y Sidhu" <ysidhu(at)gmail(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)collaborativefusion(dot)com
> Phone: 412-422-3463x4023
>

--
Yudhvir Singh Sidhu
408 375 3134 cell