From: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Bulk inserts and usage_count |
Date: | 2007-05-15 15:37:28 |
Message-ID: | 4649D3B8.30908@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While testing the buffer ring patch, I noticed that bulk inserts with
both INSERT and COPY pin and unpin the buffer they insert to for every
tuple. That means that the usage_count of all those buffers are bumped
up to 5. That's gotta be bad if you try to run a COPY concurrently with
other activity. It also affects tables like TPC-C order_line where a
tuples are always inserted and updated in groups.
To demonstrate:
postgres=# truncate foo; TRUNCATE TABLE
postgres=# COPY foo FROM '/tmp/foo.data'; COPY 1000
postgres=# SELECT c.relname, bufferid, relblocknumber, isdirty,
usagecount FROM pg_buffercache bc, pg_class c WHERE c.relfilenode =
bc.relfilenode and c.relname='foo';
relname | bufferid | relblocknumber | isdirty | usagecount
---------+----------+----------------+---------+------------
foo | 105078 | 4 | f | 5
foo | 105079 | 3 | f | 5
foo | 105080 | 2 | f | 5
foo | 105081 | 1 | f | 5
foo | 105082 | 0 | f | 5
(5 rows)
A fix for COPY will fall naturally out of the buffer ring patch, but not
for INSERT.
A more general fix would be to somehow keep the last insertion page
pinned across calls to heap_insert.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2007-05-15 15:54:25 | Re: Not ready for 8.3 |
Previous Message | Bruce Momjian | 2007-05-15 15:37:02 | Not ready for 8.3 |