Skip site navigation (1) Skip section navigation (2)

Re: Load Distributed Checkpoints, take 3

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Load Distributed Checkpoints, take 3
Date: 2007-06-26 15:50:57
Message-ID: 468135E1.4040407@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-patches
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

In response to

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2007-06-26 15:59:49
Subject: Re: [PATCHES] New Zealand - TZ change
Previous:From: Josh BerkusDate: 2007-06-26 14:59:25
Subject: Re: [PATCHES] New Zealand - TZ change

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group