Using incrond for archiving

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