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