Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5B7B09FA377 for ; Mon, 14 May 2007 19:16:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 86336-05 for ; Mon, 14 May 2007 19:15:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.248]) by postgresql.org (Postfix) with ESMTP id BBA2F9FA2F4 for ; Mon, 14 May 2007 19:15:51 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so455410and for ; Mon, 14 May 2007 15:15:50 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=A+UptFYDMZbuADJy7bWUQs+sIvT8M8ZkN3XN5g/2HZ6N84zU5emiT/+U2bT5JL6BNpt23m6WjSzBVUV9qL2k6qUwpPuZka0kMjcX6ISGxQs9Nbytpr/5ZcseZebZQ0L1r0EQ9d8LESoWZ+4+pzDKwsFhM5zZIJM2Nl/QT3xh110= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=EyxNF+AtqEh/8CpscoY/Fz6UyGEKaHDf56xjMNxJCYXOYc57AA8A4RVT20DzTDIyj/NLbEkVeuaDU5ZVdAs4uHh+CF9vcuIrQpbX67GYYdg5IYln4R7IqJPNDhqYPX65zFKb5IvDQ7/+U98CVLReFvNDuR7NeMrHWwqlWxrkq0Y= Received: by 10.100.92.17 with SMTP id p17mr2221505anb.1179180949807; Mon, 14 May 2007 15:15:49 -0700 (PDT) Received: by 10.100.6.20 with HTTP; Mon, 14 May 2007 15:15:49 -0700 (PDT) Message-ID: Date: Mon, 14 May 2007 15:15:49 -0700 From: "Y Sidhu" To: "Bill Moran" Subject: Re: pg_stats how-to? Cc: pgsql-performance@postgresql.org In-Reply-To: <20070514170959.a951eefc.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11746_15063197.1179180949636" References: <4647B0A9.7080304@gmail.com> <20070514162610.GO69517@nasby.net> <20070514174558.GS69517@decibel.org> <20070514183615.GW69517@nasby.net> <20070514190748.GY69517@nasby.net> <20070514170959.a951eefc.wmoran@collaborativefusion.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/260 X-Sequence-Number: 24650 ------=_Part_11746_15063197.1179180949636 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > > In response to "Y Sidhu" : > > > 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 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 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 > -- Yudhvir Singh Sidhu 408 375 3134 cell ------=_Part_11746_15063197.1179180949636 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@collaborativefusion.com> wrote:
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



--
Yudhvir Singh Sidhu
408 375 3134 cell ------=_Part_11746_15063197.1179180949636--