Thread: Postgres 8.3 archive_command
Hi, I just installed v8.3beta3 and discovered that the way WAL archiving is handled has changed. From postgresql.conf: #archive_mode = off # allows archiving to be done (change requires restart) #archive_command = '' # command to use to archive a logfile segment 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. Background: We are using the archive_command for PITR backup every night. This can easily be done while the server is running. After the migration to v8.3 we'd have to (1) stop the server, switch on archiving, start the server, (2) run PITR backup,and (3) stop the server, switch off archiving, start the server. Questions: - Is the requirement "change of archive_mode requires restart" just temporary or is it going to stay in the final release? If the server restart is planned for final release (for whatever reason) then my next question is: - 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? Thanks and best regards, Rudolf VanderLeeden IT Consultant Logicunited GmbH Germany vanderleeden@logicunited.com
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
Attachment
>>> 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 bebetween 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