COPY with no WAL, v2
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
Will hold for doc patches.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Simon Riggs wrote:
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 postgresDo we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
[ Attachment, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Simon Riggs wrote:
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgresDo we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
I am still waiting for the documentation patch.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Fri, 2007-01-19 at 11:36 -0500, Bruce Momjian wrote:
Simon Riggs wrote:
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgresDo we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
I am still waiting for the documentation patch.
Understood; busy on other topics last few days. It's coming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Patch applied. Thanks.
---------------------------------------------------------------------------
Simon Riggs wrote:
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 postgresDo we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
[ Attachment, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Patch applied. Thanks.
---------------------------------------------------------------------------
Simon Riggs wrote:
On Fri, 2007-01-19 at 11:36 -0500, Bruce Momjian wrote:
Simon Riggs wrote:
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgresDo we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
I am still waiting for the documentation patch.
Doc patch attached.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
[ Attachment, skipping... ]
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Import Notes
Reply to msg id not found: 1169493004.3776.399.camel@silverbirch.site | Resolved by subject fallback