Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BE69F9FBA1C for ; Thu, 26 Apr 2007 09:00:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 23156-01 for ; Thu, 26 Apr 2007 09:00:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx00.pub.collaborativefusion.com (mx00.pub.collaborativefusion.com [206.210.89.199]) by postgresql.org (Postfix) with ESMTP id 4C96B9FBAB8 for ; Thu, 26 Apr 2007 09:00:52 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (vanquish.pgh.priv.collaborativefusion.com [192.168.2.61]) (SSL: TLSv1/SSLv3,256bits,AES256-SHA) by wingspan with esmtp; Thu, 26 Apr 2007 08:00:50 -0400 id 000564AF.46309472.0000BC3F Date: Thu, 26 Apr 2007 08:00:50 -0400 From: Bill Moran To: Jim Nasby Cc: Postgresql list Performance Subject: Re: Fragmentation of WAL files Message-Id: <20070426080050.522ef7b5.wmoran@collaborativefusion.com> In-Reply-To: <3BD44C5D-E402-43EB-8FFD-1A4BC97AFD0C@decibel.org> References: <3BD44C5D-E402-43EB-8FFD-1A4BC97AFD0C@decibel.org> Organization: Collaborative Fusion X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/426 X-Sequence-Number: 24303 In response to Jim Nasby : > 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. > 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. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023