On Oct 20, 2006, at 4:24 PM, Simon Riggs wrote:
> 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.
Cool. Patch is short.
>> 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
Can I turn that off in 8.1?
>> 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?
performance in that a substantial portion of my time is spent writing
to pg_xlog
> 2) to reduce the WAL volume of PITR backups?
Yes. Main concern.
>
> e.g. archive_command = 'pg_WAL_filter -f | ... '
> e.g. archive_command = 'pg_WAL_filter -x 35456 | ... '
>
> There are some other ideas for generally reducing WAL volume also.
I'd like to see them not written to the xlogs at all (if possible).
Seems rather unnecessary unless I'm missing something.
// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/