Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4320B9FC39A for ; Thu, 29 Mar 2007 17:15:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 91839-02-2 for ; Thu, 29 Mar 2007 17:15:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp110.iad.emailsrvr.com (smtp110.iad.emailsrvr.com [207.97.245.110]) by postgresql.org (Postfix) with ESMTP id DEA9F9FBEE5 for ; Thu, 29 Mar 2007 16:50:01 -0300 (ADT) Received: from [10.0.23.92] (unknown [209.149.59.218]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) (Authenticated sender: erik@myemma.com) by relay1.r1.iad.emailsrvr.com (SMTP Server) with ESMTP id 123D344DB91; Thu, 29 Mar 2007 15:50:01 -0400 (EDT) In-Reply-To: <29109.1175195989@sss.pgh.pa.us> References: <9B7F4B34-7E96-4501-BCE0-D0BED739530A@myemma.com> <23286.1175184996@sss.pgh.pa.us> <29109.1175195989@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-21--677907085 Message-Id: Cc: PostgreSQL Performance From: Erik Jones Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris Date: Thu, 29 Mar 2007 14:49:48 -0500 To: Tom Lane X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/423 X-Sequence-Number: 23828 --Apple-Mail-21--677907085 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Mar 29, 2007, at 2:19 PM, Tom Lane wrote: > Erik Jones writes: >> One very important thing to note here is that the number, or rather >> rate, of disk writes has not changed. It's the volume of data in >> those writes that has dropped, along with those transaction >> mysterious counts. > > Hmm. I'm suddenly thinking about the stats collector: in existing > 8.2.x > releases it's got a bug that causes it to write the collected-stats > file > much too often. If you had done something that would shrink the size > of the stats file, that might explain this observation. Do you have > stats_reset_on_server_start turned on? Nope. > > The drop in reported transaction rate is still baffling though. > Are you > sure you're really doing the same amount of work? Can you estimate > what > you think the transaction rate *should* be from a what-are-your- > clients- > doing perspective? Unfortunately, I can't think of any way to do that. Our app is made up of a lot of different components and not all of them are even directly client driven. For the client driven portions of the app any given web request can contain anywhere from around 10 to sometimes over 50 different xacts (and, that just a guesstimate). Also, we didn't start tracking xact counts via pg_stat_database until about two months ago when we were in IO bound hell and we actually thought that the really big xact #s were normal for our app as that was the first and, thus, only numbers we had to work with. Also, another metric we track is to take a count from pg_stat_activity of queries running longer than 1 second every five minutes. Before these recent changes it wasn't uncommon to see that count start to seriously stack up to over 200 at times with write intensive queries hanging out for sometimes 30 minutes or more (we'd often end having to kill them...). Since we upped the shared buffers and turned on forcedirectio for our fs mount, that number has stayed under 50 and has only crossed 20 once. erik jones software developer 615-296-0838 emma(r) --Apple-Mail-21--677907085 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
On Mar 29, 2007, at = 2:19 PM, Tom Lane wrote:

Erik Jones <erik@myemma.com> writes:
=
One very important thing to = note here is that the number, or rather =A0
rate, of = disk writes has not changed.=A0 = It's the volume of data in =A0
those = writes that has dropped, along with those transaction =A0

Hmm.=A0 I'm suddenly thinking about = the stats collector: in existing 8.2.x
releases = it's got a bug that causes it to write the collected-stats = file
much too often.=A0 If you had done something = that would shrink the size
of the stats = file, that might explain this observation.=A0 Do you have=A0
Nope.


The drop in reported transaction rate is still = baffling though.=A0 Are = you
sure you're really doing the = same amount of work?=A0 Can = you estimate what
you think the transaction rate = *should* be from a what-are-your-clients-
doing = perspective?

Unfortunately, I can't = think of any way to do that.=A0 Our app is made up of a lot of different = components and not all of them are even directly client driven.=A0 For = the client driven portions of the app any given web request can contain = anywhere from around 10 to sometimes over 50 different xacts (and, that = just a guesstimate).=A0 Also, we didn't start tracking xact counts via = pg_stat_database until about two months ago when we were in IO bound = hell and we actually thought that the really big xact #s were normal for = our app as that was the first and, thus, only numbers we had to work = with.=A0=A0

Also, another metric we = track is to take a count from pg_stat_activity of queries running longer = than 1 second every five minutes.=A0 Before these recent changes it = wasn't uncommon to see that count start to seriously stack up to over = 200 at times with write intensive queries hanging out for sometimes 30 = minutes or more (we'd often end having to kill them...).=A0 Since we = upped the shared buffers and turned on forcedirectio for our fs mount, = that number has stayed under 50 and has only crossed 20 = once.=A0

=
erik jones = <erik@myemma.com>
software= developer
615-296-0838
emma(r)


=

= --Apple-Mail-21--677907085--