Re: Checkpoint cost, looks like it is WAL/CRC

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Checkpoint cost, looks like it is WAL/CRC
Date: 2005-07-26 23:15:24
Message-ID: 19590.1122419724@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> We should run tests with much higher wal_buffers numbers to nullify the
>> effect described above and reduce contention. That way we will move
>> towards the log disk speed being the limiting factor, patch or no patch.

> I've run such tests, at a glance they do seem to improve performance. I
> need some time to collate the results.

With larger wal_buffers values it might also be interesting to take some
measures to put a larger share of the WAL writing burden on the bgwriter.

Currently the bgwriter only writes out WAL buffers in two scenarios:

1. It wants to write a dirty shared buffer that has LSN beyond the
current WAL flush marker. Just like any backend, the bgwriter must
flush WAL as far as the LSN before writing the buffer.

2. The bgwriter is completing a checkpoint. It must flush WAL as far as
the checkpoint record before updating pg_control.

It might be interesting to add some logic to explicitly check for and
write out any full-but-unwritten WAL buffers during the bgwriter's
main loop.

In a scenario with many small transactions, this is probably a waste of
effort since backends will be forcing WAL write/flush any time they
commit. (This is why I haven't pursued the idea already.) However,
given a large transaction and adequate wal_buffer space, such a tactic
should offload WAL writing work nicely.

I have no idea whether the DBT benchmarks would benefit at all, but
given that they are affected positively by increasing wal_buffers,
they must have a fair percentage of not-small transactions.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Fuhr 2005-07-26 23:19:57 Re: RESULT_OID Bug
Previous Message Jim C. Nasby 2005-07-26 23:14:28 Re: ENUM type