Lists: | pgsql-patches |
---|
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 |
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: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: [PATCHES] COPY with no WAL, v2 |
Date: | 2007-01-11 04:19:12 |
Message-ID: | 200701110419.l0B4JCo19527@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: [PATCHES] COPY with no WAL, v2 |
Date: | 2007-01-19 16:36:44 |
Message-ID: | 200701191636.l0JGaii13954@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | "Simon Riggs" <simon(at)2ndquadrant(dot)com> |
---|---|
To: | "Bruce Momjian" <bruce(at)momjian(dot)us> |
Cc: | <pgsql-patches(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] COPY with no WAL, v2 |
Date: | 2007-01-19 20:21:44 |
Message-ID: | 1169238105.3776.32.camel@silverbirch.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: [PATCHES] COPY with no WAL, v2 |
Date: | 2007-01-25 02:17:32 |
Message-ID: | 200701250217.l0P2HWu26899@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +