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: josh(at)agliodbs(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Date: 2007-03-31 15:56:40
Message-ID: E64D7843-51F0-4288-80EC-8920147E333F@myemma.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mar 30, 2007, at 4:46 PM, Josh Berkus wrote:

> Erik,
>
>> You'er welcome!  However, I believe our situation is very different
>> from what you're testing if I understand you correctly.  Are you
>> saying that you're entire database will fit in memory?  If so, then
>> these are very different situations as there is no way ours could
>> ever do that.  In fact, I'm not sure that forcedirectio would really
>> net you any gain in that situation as the IO service time will be
>> basically nil if the filesystem cache doesn't have to page which I
>> would think is why your seeing what you are.
>
> Even more interesting.  I guess we've been doing too much work with
> benchmark workloads, which tend to be smaller databases.
>
> Thing is, there's *always* I/O for a read/write database.  If  
> nothing else,
> updates have to be synched to disk.

Right.  But, how *much* I/O?

>
> Anyway ... regarding the mystery transactions ... are you certain  
> that it's
> not your application?  I can imagine that, if your app has a fairly  
> tight
> retry interval for database non-response, that I/O sluggishness could
> result in commit attempts spinning out of control.

Well, our application code itself doesn't retry queries if the db is  
taking a long time to respond.  However, we do have a number of our  
servers making db connections via pgpool so you may be on to  
something here.  While I will be taking these questions to the pgpool  
lists, I'll posit them here as well:  If a pgpool child process  
reaches it's connection lifetime while waiting on a query to  
complete, does pgpool retry the query with another child?  If a  
connection thus dies, does the transaction complete normally on the  
server?  If the answers to these questions are both yes, this could  
definitely be what was happening.

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



In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-03-31 16:31:48
Subject: Re: scalablility problem
Previous:From: Xiaoning DingDate: 2007-03-31 15:54:29
Subject: Re: scalablility problem

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