From: | "Simon Riggs" <simon(at)2ndquadrant(dot)com> |
---|---|
To: | <pgsql-patches(at)postgresql(dot)org> |
Subject: | COPY with no WAL, v2 |
Date: | 2007-01-09 20:02:54 |
Message-ID: | 1168372975.3951.319.camel@silverbirch.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-patches |
VERSION 2, with all changed made as requested to date.
As discussed on -hackers, its possible to avoid writing any WAL at all
for COPY in these circumstances:
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php
and again recently.
BEGIN;
CREATE TABLE foo..
COPY foo...
COMMIT;
BEGIN;
TRUNCATE foo..
COPY foo...
COMMIT;
The enclosed patch implements this, as discussed. There is no user
interface to enable/disable, just as with CTAS and CREATE INDEX; no
docs, just code comments.
This plays nicely with the --single-transaction option in psql to allow
fast restores/upgrades.
YMMV but disk bound COPY will benefit greatly from this patch, some
tests showing 100% gain. COPY is still *very* CPU intensive, so some
tests have shown negligible benefit, fyi, but that isn't the typical
case.
While testing this, I realised something: small COPY commands get no
benefit at all, but larger ones do. When we do a small normal COPY the
data stays in cache, but the WAL is written to disk and fsynced. When we
do a small fast COPY, no WAL is written, but the data is written to disk
and fsynced. With COPY, WAL and data are roughly same size, hence no I/O
benefit. With larger COPY statements, benefit is very substantial.
Applies cleanly to CVS HEAD, passes make check.
I enclose a test case that shows whether the test has succeeded by
reading the WAL Insert pointer before/after each COPY. This has been
written in such a way that we could, if we wanted to, include a new
regression test for this. There is a function that returns an immutable
value if the test passes, rather than simply showing the WAL insert
pointer which would obviously vary between tests. The tests enclosed
here *also* include the WAL insert pointer so you can manually/visibly
see that the enclosed patch writes no WAL at appropriate times.
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgres
Do we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Attachment | Content-Type | Size |
---|---|---|
copy_nowal.v2.patch | text/x-patch | 18.5 KB |
copy_nowal_prep.sql | text/x-sql | 1.4 KB |
copy_nowal_test.sql | text/x-sql | 3.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2007-01-09 20:09:47 | Re: [PATCHES] COPY with no WAL, in certain circumstances |
Previous Message | Magnus Hagander | 2007-01-09 17:56:53 | vcbuild verbosity |