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

Using incrond for archiving

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Using incrond for archiving
Date: 2011-11-11 22:21:18
Message-ID: 4EBD9FDE.4000104@peak6.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)peak6(dot)com

______________________________________________

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

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2011-11-12 06:31:16
Subject: Re: WAL partition filling up after high WAL activity
Previous:From: Stephen FrostDate: 2011-11-11 20:18:22
Subject: Re: unlogged tables

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