Re: archive wal's failure and load increase. - Mailing list pgsql-performance

On Thu, 2006-09-28 at 21:41 +0200, Cedric Boudin wrote:

> I would like to share with you a side effect that I discovered today on
> our postgresql 8.1 server.
> We ve been running this instance with PITR for now 2 months without any
> problems.
> The wal's are copied to a remote machine with the pg_archive_command and
> locally to some other directory.
> For some independant reasons we made the remote machine unreachable for
> some hours. The pg_archive_command returned as expected a failure value.
>
> Now to what puzzles me:
> the load on the box that normally is kept between 0.7 and 1.5 did
> suddenly rise to 4.5 -5.5 and the processes responsiveness  got bad.
> The dir pg_xlog has plenty of space to keep several day of wal's.
> there was no unfinished backup's or whatever that could have apparently
> slowed the machine that much.
>
> So the question is: is there a correlation between not getting the wal's
> archived and this "massive" load growth?
> In my understanding, as the pgl engine has nothing more to do with the
> filled up log except just to make sure it's archived correctly ther
> should not be any significant load increase for this reason. Looking at
> the logs the engine tried approx. every 3 minutes to archive the wal's.
> Is this behaviour expected, If it is then is it reasonnable to burden
> the engine that is already in a inexpected situation with some IMHO
> unecessary load increase.

archiver will attempt to run archive_command 3 times before it fails.
Success or failure should be visible in the logs. archiver will try this
a *minimum* of every 60 seconds, so if there is a delay of 3 minutes
then I'm guessing the archive_command itself has some kind of timeout on
it before failure. That should be investigated.

If archive_command succeeds then archiver will process all outstanding
files. If it fails then it stops trying - it doesn't retry *every*
outstanding file, so the retries themselves do not grow in cost as the
number of outstanding files increases. So IMHO the archiver itself is
not the source of any issues.

There is one negative effect from having outstanding archived files:
Every time we switch xlogs we would normally reuse an existing file.
When those files are locked because of pending archive operations we are
unable to do that, so must create a new xlog file, zero it and fsync -
look at xlog.c:XLogFileInit(). While that occurs all WAL write
operations will be halted and the log jam that results probably slows
the server down somewhat, since we peform those actions with
WALWriteLock held.

We could improve that situation by
1. (server change) notifying bgwriter that we have an archiver failure
situation and allow new xlogs to be created as a background task. We
discussed putting PreallocXlogFiles() in bgwriter once before, but I
think last time we discussed that idea it was rejected, IIRC.

2. (server or manual change) preallocating more xlog files

3. (user change) enhancing the archive_command script so that it begins
reusing files once archiving has been disabled for a certain length of
time/size of xlog directory. You can do this by having a script try the
archive operation and if it fails (and has been failing) then return a
"success" message to the server to allow it reuse files. That means you
start dropping WAL data and hence would prevent a recovery from going
past the point you started dropping files - I'd never do that, but some
have argued previously that might be desirable.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com