Message-ID: <4EBD9FDE.4000104@peak6.com>
Date: Fri, 11 Nov 2011 16:21:18 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Using incrond for archiving
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

Hey guys,

I've been running some tests while setting up some tiered storage, and I 
noticed something. Even having an empty 'echo' as archive_command 
drastically slows down certain operations. For instance:

=> ALTER TABLE foo SET TABLESPACE slow_tier;
ALTER TABLE
Time: 3969.962 ms

When I set archive_command to anything:

=> ALTER TABLE foo SET TABLESPACE slow_tier;
ALTER TABLE
Time: 11969.962 ms

I'm guessing it has something to do with the forking code, but I haven't 
dug into it very deeply yet.

I remembered seeing incrond as a way to grab file triggers, and did some 
tests with an incrontab of this:

/db/wal/ IN_CLOSE_WRITE cp -a $@/$# /db/archive/$#

Sure enough, files don't appear there until PG closes them after 
writing. The background writing also doesn't appear to affect speed of 
my test command.

So my real question: is this safe? Supposedly the trigger only gets 
activated when the xlog file is closed, which only the PG daemon should 
be doing. I was only testing, so I didn't add a 'test -f' command to 
prevent overwriting existing archives, but I figured... why bother if 
there's no future there?

I'd say tripling the latency for some database writes is a pretty 
significant difference, though. I'll defer to the experts in case this 
is sketchy. :)

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email