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 |
Thread: | |
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 | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-01-11 04:32:51 | Re: [HACKERS] [PATCHES] wal_checksum = on (default) | off |
Previous Message | L Bayuk | 2007-01-11 03:06:08 | Re: [PATCHES] Building libpq/psql with Borland BCC5 |