Re: Load Distributed Checkpoints, take 3

Tom Lane wrote:
> Heikki Linnakangas  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