On Tue, Jul 12, 2011 at 6:15 PM, lars <lhofhansl@xxxxxxxxx> 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@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
