Re: xlogdump fixups and WAL log question.

On Fri, 2006-10-20 at 13:18 -0400, Theo Schlossnagle wrote:
> Not sure who cares, so xzilla indicated I should drop a note here. I
> just made the xlogdump stuff work for 8.1 (trivial) and fixed a few
> other small issues that caused it to not work right both generally
> and in our environment.
>
> http://pgfoundry.org/tracker/index.php?
> func=detail&aid=1000760&group_id=1000202&atid=772

Diogo Biazus was working on that; I care also.

> We're using it to track down what's causing some wal log ruckus.
> We're generating about a quarter terabyte of WAL logs a day (on bad
> days) which is posing some PITR backup pains. That amount isn't a
> severe challenge to backup, but our previous install was on Oracle
> and it generated substantially less archive redo logs (10-20 gigs per
> day).

As Tom says, definitely because of full_page_writes=on

> Is it possible to create tables in fashion that will not write info
> to the WAL log -- knowingly and intentionally making them
> unrecoverable? This is very desirable for us. We snapshot tables
> from a production environment. If the database goes down and we
> recover, the old snapshots are out of date anyway and serve no useful
> purpose. The periodic snapshot procedure would re-snap them in short
> order anyway. I'd like to do:
>
> INSERT INTO TABLE tblfoo_snap1 AS SELECT * from <table on remote
> database> NO LOGGING;
>
> (NO LOGGING being the only part we're currently missing) Is something
> like this possible?

Do you want this because of:
1) performance?
2) to reduce the WAL volume of PITR backups?

If you're thinking (1), then I guess I'd ask whether you've considered
what will happen when the reporting environment includes data from other
sources as it inevitably will. At that point, data loss would be much
more annoying. My experience is that the success of your current
implementation will lead quickly to a greatly increased user
requirement.

I've been looking at ways of reducing the WAL volume for PITR backups.
Here's a few ideas:

1. Provide a filter that can be easily used by archive_command to remove
full page writes from WAL files. This would require us to disable the
file size test when we begin recovery on a new WAL files, plus would
need to redesign initial location of the checkpoint record since we
could no longer rely on the XLogRecPtr being a byte offset within the
file.

e.g. archive_command = 'pg_WAL_filter -f | ... '

2. Include tablespaceid within the header of xlog records. This would
allow us to filter out WAL from one or more tablespaces, similarly to
(1), plus it would also allow single tablespace recovery.

e.g. archive_command = 'pg_WAL_filter -x 35456 | ... '

There are some other ideas for generally reducing WAL volume also.

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