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