DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=keCD7Ncr7s1JWBrQ1NsREESltIEpOV/nBvDRTnk/bY5Y+wwd5YTIsHEDMiTxNhcH3JDDa1HzSUfafnapabG4fuBqB3Cxl89IvgD5dZgYuRqA5GqaAYXy6ds6N0UMu/CgSdE7DkTMMqh9oSCja75B7AiVpQJmeOab4lnCk3G27T8=
Message-ID: <463096AD.4020707@enterprisedb.com>
Date: Thu, 26 Apr 2007 13:10:21 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Bill Moran <wmoran@collaborativefusion.com>
CC: Jim Nasby <decibel@decibel.org>,
	Postgresql list Performance <pgsql-performance@postgresql.org>
Subject: Re: Fragmentation of WAL files
References: <3BD44C5D-E402-43EB-8FFD-1A4BC97AFD0C@decibel.org>
	<20070426080050.522ef7b5.wmoran@collaborativefusion.com>
In-Reply-To: <20070426080050.522ef7b5.wmoran@collaborativefusion.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bill Moran wrote:
> In response to Jim Nasby <decibel@decibel.org>:
> 
>> I was recently running defrag on my windows/parallels VM and noticed  
>> a bunch of WAL files that defrag couldn't take care of, presumably  
>> because the database was running. What's disturbing to me is that  
>> these files all had ~2000 fragments. Now, this was an EnterpriseDB  
>> database which means the WAL files were 64MB instead of 16MB, but  
>> even having 500 fragments for a 16MB WAL file seems like it would  
>> definitely impact performance.
> 
> I don't know about that.  I've seen marketing material that claims that
> modern NTFS doesn't suffer performance problems from fragmentation.  I've
> never tested it myself, but my point is that you might want to do some
> experiments -- you might find out that it doesn't make any difference.
> 
> If it does, you should be able to stop the DB, defragment the files, then
> start the DB back up.  Since WAL files are recycled, they shouldn't
> fragment again -- unless I'm missing something.
> 
> If that works, it may indicate that (on Windows) a good method for installing
> is to create all the necessary WAL files as empty files before launching
> the DB.

If that turns out to be a problem, I wonder if it would help to expand 
the WAL file to full size with ftruncate or something similar, instead 
of growing it page by page.

>> Can anyone else confirm this? I don't know if this is a windows-only  
>> issue, but I don't know of a way to check fragmentation in unix.
> 
> I can confirm that it's only a Windows problem.  No UNIX filesystem
> that I'm aware of suffers from fragmentation.

What do you mean by suffering? All filesystems fragment files at some 
point. When and how differs from filesystem to filesystem. And some 
filesystems might be smarter than others in placing the fragments.

There's a tool for Linux in the e2fsprogs package called filefrag that 
shows the fragmentation of a file, but I've never used it myself.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com