Thread: 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
Attachment
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 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, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/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 postgres > > Do 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 postgres > > > > Do 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 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, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +