Re: Reduced power consumption in WAL Writer process

My instrumentation wasn't that good. I was using powertop 1.13, which
apparently goes to great lengths to group processes by various
criteria (including process group), but doesn't actually offer the
option of seeing that instrumentation per process.

I'm using version 1.98 beta 1 as of now, which provides per-process
instrumentation - it only works with Kernel versions 2.6.36+ though.
The per process breakdown of wake-ups per second is:

248.3 us/s     5.0            Process    postgres: wal writer process
281.0 us/s     4.9            Process    postgres: writer process
82.3 us/s     1.1            Process    postgres: autovacuum launcher process
82.3 us/s     0.4            Process    postgres: stats collector process
442.8 us/s     0.15            Process    postgres
23.6 us/s     0.15            Process    postgres -d1

The second column from the left is wake-ups per second. As previously
reported and as you see here, there are about 11.5 idle wake-ups per
second per cluster. Note that archiving was running when this
instrumentation was performed, but the recently-improved archiver
wasn't listed at all, presumably because powertop didn't detect any
wake-ups in the period of instrumentation, which was 10 or 20 seconds.

As you'd expect, the WAL writer is woken up (1 second /
wal_writer_delay milliseconds) times per second.

On 14 July 2011 11:00, Simon Riggs <simon@2ndquadrant.com> wrote:
>> That was my first though too - but I wonder if that's too aggressive? A
>> backend that does for example a large bulk load will cycle through the
>> buffers real quick. It seems like a bad idea to wake up walwriter between
>> each buffer in that case. Then again, setting a latch that's already set is
>> cheap, so maybe it works fine in practice.
>>
>> Maybe it would be better to do it less frequently, say, every time you
>> switch to new WAL segment. Or every 10 buffers or something like that.
>
> Yes, that roughly what I'm saying. When nextidx == 0 is just after we
> wrapped wal_buffers, i.e. we only wake up the WALWriter every
> wal_buffers pages.

I'll work towards that implementation.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services