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

Reply via email to