Re: Load Distributed Checkpoints, take 3

Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
>> One pathological case is a COPY of a table slightly smaller than
>> shared_buffers. That will fill the buffer cache. If you then have a
>> checkpoint, and after that a SELECT COUNT(*), or a VACUUM, the buffer
>> cache will be full of pages with just hint-bit-updates, but no WAL
>> activity since last checkpoint.
>
> This argument supposes that the bgwriter will do nothing while the COPY
> is proceeding.

It will clean buffers ahead of the COPY, but it won't write the buffers
COPY leaves behind since they have usage_count=1.

Let me demonstrate this with an imaginary example with shared_buffers=4:

buf_id usage_count dirty
1 0 f
2 0 f
3 0 f
4 0 f

After COPY

buf_id usage_count dirty
1 1 t
2 1 t
3 1 t
4 1 t

CHECKPOINT:

buf_id usage_count dirty
1 1 f
2 1 f
3 1 f
4 1 f

VACUUM:

buf_id usage_count dirty
1 1 t
2 1 t
3 1 t
4 1 t

As soon as a backend asks for a buffer, the situation is defused as the
backend will do a full clock sweep and decrement the usage_count of each
buffer to 0, letting the bgwriter lru-scan to clean them.

Having the buffer cache full of dirty buffers is not a problem on its
own, so this only becomes a performance issue if you then issue another
large COPY etc. that needs those buffers, and you now have to write them
at the busy time.

This is a corner case that might not be worth worrying about. It's also
mitigated by the fact that the OS cache is most likely clean after a
period of idle time, and should be able to absorb the write burst.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com