pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Jones <erik@myemma.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Date: Thu, 29 Mar 2007 14:49:48 -0500
Message-ID: <F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com> (raw)
In-Reply-To: <29109.1175195989@sss.pgh.pa.us>
References: <9B7F4B34-7E96-4501-BCE0-D0BED739530A@myemma.com>
	<23286.1175184996@sss.pgh.pa.us>
	<E49400AB-D46D-4BB8-9F27-65F5AB06F40E@myemma.com>
	<29109.1175195989@sss.pgh.pa.us>

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
>> 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 <erik@myemma.com>
software developer
615-296-0838
emma(r)

view thread (22+ messages)  latest in thread

Message-ID: <F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com>
Permalink:  ../F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com/
Also on:    postgresql.org/message-id/F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: erik@myemma.com, tgl@sss.pgh.pa.us
  Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
  In-Reply-To: <F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com>
  References: <9B7F4B34-7E96-4501-BCE0-D0BED739530A@myemma.com> <23286.1175184996@sss.pgh.pa.us> <E49400AB-D46D-4BB8-9F27-65F5AB06F40E@myemma.com> <29109.1175195989@sss.pgh.pa.us> <F155EEAC-37E5-4CF8-B355-77FFAE79DECD@myemma.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox