On Tue, Jul 12, 2011 at 6:15 PM, lars <lhofhansl@yahoo.com> wrote:
> Back to the first case, here's an strace from the backend doing the select
> right after the updates.
> write(13,
> "f\320\1\0\1\0\0\0\273\0\0\0\0\340\27\22`\32\0\00000002833!000"..., 2400256)
> = 2400256
On Wed, Jul 13, 2011 at 9:46 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Code comments indicate that they expect the pruning to be a pretty
> clear win on multiple reads, although I don't know how much that was
> benchmarked. Jeff does raise a good point, though -- it seems odd
> that WAL-logging of this pruning would need to be synchronous. We
> support asynchronous commits -- why not use that feature
Right -- here are my thoughts. notice the above is writing out 293
pages. this is suggesting to me that Kevin is right and you've
identified a pattern where you are aggravating the page cleanup
facilities of HOT. What threw me off here (and perhaps bears some
additional investigation) is that early on in the report you were
claiming an update to an indexed field which effectively disables HOT.
The fairly lousy I/O performance of EBS is further hurting you here:
you have a very fast computer with lots of memory with a late 90's
disk system in terms of performance. This means that AWS is not all
that great for simulating load profiles unless you are also highly
underweight I/O in your servers. Postgres btw demands (as does
Oracle) a decent i/o system for many workloads that might be
surprising.
A note about HOT: there is no way to disable it (other than updating
an indexed field to bypass it) -- HOT was a performance revolution for
Postgres and numerous benchmarks as well as anecdotal reports have
confirmed this. HOT mitigates the impact of dead tuples by 1. highly
reducing index bloat under certain conditions and 2. allowing dead
tuples to be more aggressively cleaned up -- a 'page level vacuum' if
it were. HOT is an especially huge win when updates are frequent and
transactions are small and short....but maybe in your simulated case
it's not helping. hm.
merlin