Обсуждение: Fragmentation of WAL files
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. 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. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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. > 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
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
In response to Heikki Linnakangas <heikki@enterprisedb.com>: [snip] > >> 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. To clarify my viewpoint: To my knowledge, there is no Unix filesystem that _suffers_ from fragmentation. Specifically, all filessytems have some degree of fragmentation that occurs, but every Unix filesystem that I am aware of has built-in mechanisms to mitigate this and prevent it from becoming a performance issue. > 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. Interesting. However, the existence of a tool does not particularly indicated the _need_ for said tool. It might just have been something cool that somebody wrote. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
> 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.
It sounds like that filesystem is too stupid to coalesce successive
write() calls into one allocation fragment :-(. I agree with the
comments that this might not be important, but you could experiment
to see --- try increasing the size of "zbuffer" in XLogFileInit to
maybe 16*XLOG_BLCKSZ, re-initdb, and see if performance improves.
The suggestion to use ftruncate is so full of holes that I won't
bother to point them all out, but certainly we could write more than
just XLOG_BLCKSZ at a time while preparing the file.
regards, tom lane
Bill Moran wrote: > In response to Heikki Linnakangas <heikki@enterprisedb.com>: >>>> 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. > > To clarify my viewpoint: > To my knowledge, there is no Unix filesystem that _suffers_ from > fragmentation. Specifically, all filessytems have some degree of > fragmentation that occurs, but every Unix filesystem that I am aware of > has built-in mechanisms to mitigate this and prevent it from becoming > a performance issue. More specifically, this problem was solved on UNIX file systems way back in the 1970's and 1980's. No UNIX file system (includingLinux) since then has had significant fragmentation problems, unless the file system gets close to 100% full. If you run below 90% full, fragmentation shouldn't ever be a significant performance problem. The word "fragmentation" would have dropped from the common parlance if it weren't for MS Windoz. Craig
"Craig A. James" <cjames@modgraph-usa.com> writes: > More specifically, this problem was solved on UNIX file systems way back in the > 1970's and 1980's. No UNIX file system (including Linux) since then has had > significant fragmentation problems, unless the file system gets close to 100% > full. If you run below 90% full, fragmentation shouldn't ever be a significant > performance problem. Note that the main technique used to avoid fragmentation -- paradoxically -- is to break the file up into reasonable sized chunks. This allows the filesystem the flexibility to place the chunks efficiently. In the case of a performance-critical file like the WAL that's always read sequentially it may be to our advantage to defeat this technique and force it to be allocated sequentially. I'm not sure whether any filesystems provide any option to do so. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> In the case of a performance-critical file like the WAL that's always read
> sequentially it may be to our advantage to defeat this technique and force it
> to be allocated sequentially. I'm not sure whether any filesystems provide any
> option to do so.
We more or less do that already by filling the entire file in one go
when it's created ...
regards, tom lane
On Thu, 26 Apr 2007, Bill Moran wrote: > I've seen marketing material that claims that modern NTFS doesn't suffer > performance problems from fragmentation. You're only reading half of the marketing material then. For a balanced picture, read the stuff generated by the companies that sell defragmenting tools. A good one to start with is http://files.diskeeper.com/pdf/HowFileFragmentationOccursonWindowsXP.pdf Going back to the Jim's original question, they suggest a Microsoft paper that talks about how the defrag report can be misleading in respect to open files. See http://support.microsoft.com/kb/228198 Also, some of the most interesting details they gloss over are specific to which version of Windows you're using; the reference guide to the subject of how NTFS decides how much space to pre-allocate at a time is available at http://support.microsoft.com/kb/841551 (ZIP file wrapped into EXE, yuck!) If you compare them, you can see that the approach they're using in NTFS has evolved to become more like that used by a good UNIX filesystem over time. I think your typical UNIX still has a healthy lead in this area, but the difference isn't as big as it used to be. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
* Bill Moran: > To clarify my viewpoint: > To my knowledge, there is no Unix filesystem that _suffers_ from > fragmentation. Specifically, all filessytems have some degree of > fragmentation that occurs, but every Unix filesystem that I am aware of > has built-in mechanisms to mitigate this and prevent it from becoming > a performance issue. One database engine tends to create a huge number of fragments because the files are written with holes in them. There is a significant impact on sequential reads, but that doesn't matter much because the engine doesn't implement fast, out-of-order B-tree scans anyway. 8-/ I still think that preallocating in reasonably sized chunks is beneficial. -- Florian Weimer <fweimer@bfk.de> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99