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

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)