Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
