Skip site navigation (1) Skip section navigation (2)

Re: Shared buffers, db transactions commited, and write IO on Solaris

From: Erik Jones <erik(at)myemma(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Date: 2007-03-29 16:45:57
Message-ID: E49400AB-D46D-4BB8-9F27-65F5AB06F40E@myemma.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mar 29, 2007, at 11:16 AM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> writes:
>> We've recently made a couple changes to our system that have resulted
>> in a drastic increase in performance as well as some very confusing
>> changes to the database statistics, specifically
>> pg_stat_database.xact_commit.  Here's the details:
>
> I'm kinda boggled too.  I can see how increasing shared buffers could
> result in a drastic reduction in write rate, if the working set of  
> your
> queries fits in the new space but didn't fit in the old.  I have no  
> idea
> how that leads to a drop in number of transactions committed though.
> It doesn't make sense that autovac would run less frequently, because
> it's driven by number of tuples changed not number of disk writes; and
> that could hardly account for a 10x drop anyway.
>
> Did you by any chance take note of exactly which processes were
> generating all the I/O or the CPU load?

Well, wrt to the CPU load, as I said, we're pretty sure that's  
autovac as we still get spikes that hit about the same threshold,  
after which cache hits go up dramatically and the spikes just don't  
last two days anymore.

As far as the procs responsible for the writes go, we were unable to  
see that from the OS level as the guy we had as a systems admin last  
year totally screwed us with the way he set up the SunCluster on the  
boxes and we have been unable to run Dtrace which has left us  
watching a lot of iostat.  However, we did notice a direct  
correlation between write spikes and "write intensive" queries like  
large COPYs, UPDATEs, and INSERTs.

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.  Could the bgwriter be the culprit here?  Does  
anything it does get logged as a transaction?

erik jones <erik(at)myemma(dot)com>
software developer
615-296-0838
emma(r)



In response to

Responses

pgsql-performance by date

Next:From: dimitri kDate: 2007-03-29 17:41:05
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Previous:From: Tom LaneDate: 2007-03-29 16:16:36
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group