Postgres 8.3 archive_command

On Wed, 2007-11-21 at 14:45 +0100, Rudolf van der Leeden wrote:

In v8.2.4 we are currently switching archiving on/off on the fly by
just inserting an archiving command.
Now we have got a separate option for handling the on/off state.
That's ok.
But the note "change requires restart" is a serious change over v8.2.

Thanks for the feedback.

There was a window of data loss caused by the capability to change the
archive_command on and off while running a large COPY, CTAS, CLUSTER or
CREATE INDEX, which we had to avoid.

The new way of doing this changes that so you must have archive_mode set
on always, but you can still change archive_command on the fly.

- The workaround is a script that simply does a NOOP if archiving
is effectively disabled while archive_mode=on all the time.
How can one switch off the archiving log messages?

You can't.

I think if you choose to set archive command to something that doesn't
actually archive the file, thats up to you. The server log shows that
Postgres server did as you asked it to do. That helps to avoid
complaints like "How come Postgres didn't tell me when it wasn't
archiving".

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments? That would
reduce the log volume in the normal case without endangering our ability
to see what is happening.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Wed, Nov 21, 2007 at 8:49 AM, in message

<1195656591.4246.42.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments? That would
reduce the log volume in the normal case without endangering our ability
to see what is happening.

I would prefer to always log the archives; it has proven useful in
investigating what's going on when our backup stream "stalls". How
much other stuff would I be getting if I had to turn on DEBUG1 to
see this? Would it be overkill to add a GUC to configure the log
level of archiving?

For less active databases, where we rarely get more than one
segment per hour, it would be kinda odd to see an archive logged
once every couple weeks.

-Kevin

Simon Riggs <simon@2ndquadrant.com> writes:

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments? That would
reduce the log volume in the normal case without endangering our ability
to see what is happening.

That would confuse people terribly, and it *would* endanger our ability
to see what was happening, 254 times out of 255.

regards, tom lane

On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:

Simon Riggs <simon@2ndquadrant.com> writes:

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments? That would
reduce the log volume in the normal case without endangering our ability
to see what is happening.

That would confuse people terribly, and it *would* endanger our ability
to see what was happening, 254 times out of 255.

That's my feeling too, just wanted to check it still made sense for
y'all.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:

On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:

That would confuse people terribly, and it *would* endanger our ability
to see what was happening, 254 times out of 255.

That's my feeling too, just wanted to check it still made sense for
y'all.

Just to clarify: I don't object to lowering "successfully archived"
messages to DEBUG1, if the field consensus is that it's too chatty.
What I didn't like was the idea of logging some events but not other
identical events.

regards, tom lane

On Wed, Nov 21, 2007 at 10:57 AM, in message <12172.1195664252@sss.pgh.pa.us>,

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndquadrant.com> writes:

On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:

That would confuse people terribly, and it *would* endanger our ability
to see what was happening, 254 times out of 255.

That's my feeling too, just wanted to check it still made sense for
y'all.

Just to clarify: I don't object to lowering "successfully archived"
messages to DEBUG1, if the field consensus is that it's too chatty.
What I didn't like was the idea of logging some events but not other
identical events.

Agreed on the intermittent logging. I don't feel it's too chatty,
but on the other hand, I could always change the logging level on
the fly if I was investigating a problem, so it wouldn't be much of
an inconvenience to switch it if it bugs others.

In poking around the logs just now, I noticed one message I'd like
to squelch. Run against Milwaukee County's recent log files:

grep -c 'PRIMARY KEY will create implicit index'

I get this for the last ten full days:

/var/pgsql/data/cc/pg_log/postgresql-2007-11-11_000000.log:210
/var/pgsql/data/cc/pg_log/postgresql-2007-11-12_000000.log:14138
/var/pgsql/data/cc/pg_log/postgresql-2007-11-13_000000.log:13250
/var/pgsql/data/cc/pg_log/postgresql-2007-11-14_000000.log:14912
/var/pgsql/data/cc/pg_log/postgresql-2007-11-15_000000.log:11635
/var/pgsql/data/cc/pg_log/postgresql-2007-11-16_000000.log:10774
/var/pgsql/data/cc/pg_log/postgresql-2007-11-17_000000.log:183
/var/pgsql/data/cc/pg_log/postgresql-2007-11-18_000000.log:120
/var/pgsql/data/cc/pg_log/postgresql-2007-11-19_000000.log:12667
/var/pgsql/data/cc/pg_log/postgresql-2007-11-20_000000.log:13992

It's actually pretty hard to find the archive logging amidst all
that. The log is next to useless without a grep -v to filter
them out.

If we're going to pursue the idea further, I guess I should
spawn a new thread, but would people consider moving *that* one
to DEBUG1? There's an idea I can get behind!

-Kevin

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments?
That would reduce the log volume in the normal case without

endangering

our ability to see what is happening.

Wouldn't it be more useful to increase the WAL segment size on such
installations
that switch WAL files so frequently that it is a problem for the log ?

This currently needs a recompile. I wondered for some time now whether
16 Mb isn't
too low for current hw. Maybe it is time for making WAL segment size
changeable
in the conf with a clean shutdown.

Andreas

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Just to clarify: I don't object to lowering "successfully archived"
messages to DEBUG1, if the field consensus is that it's too chatty.
What I didn't like was the idea of logging some events but not other
identical events.

Agreed on the intermittent logging. I don't feel it's too chatty,
but on the other hand, I could always change the logging level on
the fly if I was investigating a problem, so it wouldn't be much of
an inconvenience to switch it if it bugs others.

Also, you can always tweak your archive_command script to do some
logging of its own, so it's always possible to make the thing more
noisy if you need to. Less noisy, though, is hard without changing
the server code.

In poking around the logs just now, I noticed one message I'd like
to squelch. Run against Milwaukee County's recent log files:

grep -c 'PRIMARY KEY will create implicit index'

Set log_min_messages higher than NOTICE. Given the current usage of
NOTICE --- basically they're *all* newbie-annoyance items ---
I kinda wonder why WARNING isn't the default setting anyway.

regards, tom lane

On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote:

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments?
That would reduce the log volume in the normal case without

endangering

our ability to see what is happening.

Wouldn't it be more useful to increase the WAL segment size on such
installations
that switch WAL files so frequently that it is a problem for the log ?

This currently needs a recompile. I wondered for some time now whether
16 Mb isn't
too low for current hw. Maybe it is time for making WAL segment size
changeable
in the conf with a clean shutdown.

I think its too late in the release cycle to fully consider all the
implications of that. 16MB is hardcoded in lots of places. The
performance advantages of that have been mostly removed in 8.3, you
should note.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:

On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote:

Maybe it is time for making WAL segment size
changeable in the conf with a clean shutdown.

I think its too late in the release cycle to fully consider all the
implications of that. 16MB is hardcoded in lots of places.

Starting with the WAL file names. You couldn't even find the last
checkpoint record if this were changed between shutdown and restart;
you'd very likely go looking under the wrong filename, and even if
you hit on an existent filename you'd be looking at the wrong offset
within it.

I don't think that should even be a TODO item --- it seems far more
likely to provide a foot-gun than useful capability.

Whether 16MB is still a reasonable default segment size is worth
questioning, though I don't think that increasing it is an open-and-shut
proposition. Larger segments mean more overhead in configurations that
force frequent segment switches, for instance.

regards, tom lane

On Wed, 2007-11-21 at 12:24 -0500, Tom Lane wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Just to clarify: I don't object to lowering "successfully archived"
messages to DEBUG1, if the field consensus is that it's too chatty.
What I didn't like was the idea of logging some events but not other
identical events.

Agreed on the intermittent logging. I don't feel it's too chatty,
but on the other hand, I could always change the logging level on
the fly if I was investigating a problem, so it wouldn't be much of
an inconvenience to switch it if it bugs others.

Also, you can always tweak your archive_command script to do some
logging of its own, so it's always possible to make the thing more
noisy if you need to. Less noisy, though, is hard without changing
the server code.

OK, both of those thoughts are good, so happy now with DEBUG1 for
success messages: "archived transaction log file..."

Enclosed patch to set archived message to DEBUG1.

I've got a few other comments for PITR docs, so I'll mention this also
in forthcoming patch.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Wed, Nov 21, 2007 at 11:24 AM, in message <12760.1195665880@sss.pgh.pa.us>,

Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

In poking around the logs just now, I noticed one message I'd like
to squelch. Run against Milwaukee County's recent log files:

grep -c 'PRIMARY KEY will create implicit index'

Set log_min_messages higher than NOTICE. Given the current usage of
NOTICE --- basically they're *all* newbie-annoyance items ---

Done statewide. Thanks for the suggestion.

I kinda wonder why WARNING isn't the default setting anyway.

Based on what you said above, I wonder, too. Maybe that's something
to consider changing?

-Kevin

On Wed, 21 Nov 2007, Tom Lane wrote:

Whether 16MB is still a reasonable default segment size is worth
questioning, though I don't think that increasing it is an open-and-shut
proposition.

I don't think it's a reasonable change to make right now. I think 16MB is
already too big for some people, reasonable for most, and only too small
for a tiny portion of users that have fairly powerful systems.
Incorporating what you said, there are two downsides to a bigger segment
that immediately come to mind:

-More garbage in unfilled segments means higher archiving overhead in some
configurations. There are already people annoyed enough about this
problem to be coding around it (I'm thinking of Kevin Grittner's
pg_clearxlogtail).

-The way new WAL segments get created can block clients while they wait
for that write. As the segments gets larger this problem becomes worse,
and there are many situations where reducing worse-case latency is far
more important than throughput.

There's already been talk of improving the latter by having a background
process create the segments, but that doesn't make the problem go away
altogether; it just makes it less likely to happen. I'd at least like to
see that change and an official log tail cleaning mechanism both available
before considering a change to the default WAL size.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Perhaps we should move the successful archived message to DEBUG1

now,

except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments?
That would reduce the log volume in the normal case without

endangering

our ability to see what is happening.

Wouldn't it be more useful to increase the WAL segment size on such
installations
that switch WAL files so frequently that it is a problem for the log

?

This currently needs a recompile. I wondered for some time now

whether

16 Mb isn't
too low for current hw. Maybe it is time for making WAL segment size
changeable
in the conf with a clean shutdown.

I think its too late in the release cycle to fully consider all the
implications of that. 16MB is hardcoded in lots of places. The
performance advantages of that have been mostly removed in 8.3, you
should note.

Oh sorry, this was definitely not meant for 8.3. And here I didn't mean
the
performance of the db issue, but an issue for archiving the WAL files.
I think most archiving systems are not too happy with extremely frequent
backup calls. Also the overall handling of too many WAL files is imho
not handy.

Andreas

I don't think that should even be a TODO item --- it seems far more
likely to provide a foot-gun than useful capability.

On further reflection I think that initdb time is probably sufficient.
Do you think that would be a reasonable TODO ?

Whether 16MB is still a reasonable default segment size is worth
questioning, though I don't think that increasing it is an

open-and-shut

proposition. Larger segments mean more overhead in configurations

that

force frequent segment switches, for instance.

Yes, imho there is no one size fits all (if there were, it would
probably be
between 32 and 64 Mb).
But there are installations where even 16Mb is too much e.g. for an
embedded device.

Andreas

On Thu, 2007-11-22 at 10:34 +0100, Zeugswetter Andreas ADI SD wrote:

I don't think that should even be a TODO item --- it seems far more
likely to provide a foot-gun than useful capability.

On further reflection I think that initdb time is probably sufficient.
Do you think that would be a reasonable TODO ?

I think you'd have to explain why this was needed. It was useful for
performance once, but not anymore.

There are many possible errors there, so not a road I would go down
without good reason. Extra configuration is just one more thing to go
wrong and one more thing for people to misconfigure.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:

On Thu, 2007-11-22 at 10:34 +0100, Zeugswetter Andreas ADI SD wrote:

On further reflection I think that initdb time is probably sufficient.
Do you think that would be a reasonable TODO ?

I think you'd have to explain why this was needed. It was useful for
performance once, but not anymore.

Yeah, some demonstration that choosing another size is actually useful
would be a good idea before we go to the trouble of making it runtime
configurable. It's already build-time configurable, and I'm not
convinced that that's not sufficient.

regards, tom lane