Wal logs continued...

Started by webb spraguealmost 25 years ago2 messagesgeneral
Jump to latest
#1webb sprague
wsprague@o1.com

Earlier I posted with my problems about the WAL logs eating up all my
diskspace. I tried the solutions offered--checkpoint after a big copy and
shortening the time between flushes. They helped somewhat.

Unfortunately, the problem snow seems to happen when I vacuum-analyze after a
big delete. Even if the vacuum takes more than 2 minutes (the amount of time
between flushes that I set in postgresql.conf), the logs continue to grow.
Currently the vacuum has been running for about 20 minutes after a delete of
about 2,000,000 records out of 5,000,000, and the WAL logs are at about 1.5 G.

Versions: RH 6.2, PG 7.1.1

Is there some way to set the number of log files before a flush/checkpoint
thing? We are going to go to a bigger machine next week, but this is quite
an inconvenience, and it would probably benefit the DB as a whole to place
some limit on the size of the WAL.

I would code it myself, but I can't yet (next year, after I finish a couple
of Stevens' books....). If there is any thing else I can do to help, please
let me know.

Thanks,
W

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: webb sprague (#1)
Re: Wal logs continued...

webb sprague <wsprague@o1.com> writes:

Unfortunately, the problem snow seems to happen when I vacuum-analyze
after a big delete. Even if the vacuum takes more than 2 minutes (the
amount of time between flushes that I set in postgresql.conf), the
logs continue to grow.

I don't think there's any way around that at the moment :-(. The old
log segments cannot be deleted as long as they include information about
as-yet-uncommitted transactions, and the VACUUM is one big transaction.

It would theoretically be possible to delete old log segments from
before the last checkpoint (or better, next-to-last checkpoint) if
we were only concerned about using the logs for crash recovery.
I imagine Vadim will object to that, however, since he has in mind
using the logs for UNDOing aborted transactions sometime soon.

A more likely solution path will be to eliminate VACUUM or break it
into smaller transactions...

regards, tom lane