Skip site navigation (1) Skip section navigation (2)

Re: WAL file size vs. data file size

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: WAL file size vs. data file size
Date: 2011-10-27 14:50:47
Message-ID: 95E5A86E-42AF-4B3D-9410-82869BC0CD1F@silentmedia.com (view raw or flat)
Thread:
Lists: pgsql-general
On Oct 27, 2011, at 8:44 AM, Tom Lane wrote:

> Ben Chobot <bench(at)silentmedia(dot)com> writes:
>> Today I tried to restore a 70GB database with the standard "pg_dump -h old_server <∑> | psql -h new_server <∑>" method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore hung mis-way because my pg_xlogs directory ran out of space. 
> 
>> Is it expected that WAL files are less dense than data files?
> 
> Yes, that's not particularly surprising ... but how come they weren't
> getting recycled?  Perhaps you had configured WAL archiving but it was
> broken?

It's because I'm archiving wal files into Amazon's S3, which is slooooooooooow. PG is recycling as fast as it can, but when a few MB of COPY rows seem to ballon up to a few hundred MB of WAL files, it has a lot to archive before it can recycle. It'll be fine for steady state but it looks like it's just going to be a waste for this initial load.

What's the expected density ratio? I was always under the impression it would be about 1:1 when doing things like COPY, and have never seen anything to the contrary. 

In response to

pgsql-general by date

Next:From: Gauthier, DaveDate: 2011-10-27 16:18:01
Subject: matching against a list of regexp?
Previous:From: Tom LaneDate: 2011-10-27 14:44:34
Subject: Re: WAL file size vs. data file size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group