Re: Re: [WIP] Performance Improvement by reducing WAL for Update Operation

On 03.10.2012 19:03, Amit Kapila wrote:
> Any comments/suggestions regarding performance/functionality test?

Hmm. Doing a lot of UPDATEs concurrently can be limited by the
WALInsertLock, which each inserter holds while copying the WAL record to
the buffer. Reducing the size of the WAL records, by compression or
delta encoding, alleviates that bottleneck: when WAL records are
smaller, the lock needs to be held for a shorter duration. That improves
throughput, even if individual backends need to do more CPU work to
compress the records, because that work can be done in parallel. I
suspect much of the benefit you're seeing in these tests might be
because of that effect.

As it happens, I've been working on making WAL insertion scale better in
general:
http://archives.postgresql.org/message-id/5064779A.3050407@vmware.com.
That should also help most when inserting large WAL records. The
question is: assuming we commit the xloginsert-scale patch, how much
benefit is there left from the compression? It will surely still help to
reduce the size of WAL, which can certainly help if you're limited by
the WAL I/O, but I suspect the results from the pgbench tests you run
might look quite different.

So, could you rerun these tests with the xloginsert-scale patch applied?
Reducing the WAL size might still be a good idea even if the patch
doesn't have much effect on TPS, but I'd like to make sure that the
compression doesn't hurt performance. Also, it would be a good idea to
repeat the tests with just a single client; we don't want to hurt the
performance in that scenario either.

- Heikki