Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] COPY with no WAL, v2

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 (view raw or flat)
Thread:
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. +

In response to

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-01-25 02:17:38
Subject: Re: [PATCHES] COPY with no WAL, v2
Previous:From: Tom LaneDate: 2007-01-25 01:19:50
Subject: Re: [pgsql-patches] unprivileged contrib and pl install (formerly tsearch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group