Re: Load Distributed Checkpoints, take 3

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.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