pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFragmentation of WAL files
5+ messages / 5 participants
[nested] [flat]
* Fragmentation of WAL files
@ 2007-04-25 17:26 Jim Nasby <decibel@decibel.org>
2007-04-26 12:00 ` Re: Fragmentation of WAL files Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jim Nasby @ 2007-04-25 17:26 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fragmentation of WAL files
2007-04-25 17:26 Fragmentation of WAL files Jim Nasby <decibel@decibel.org>
@ 2007-04-26 12:00 ` Bill Moran <wmoran@collaborativefusion.com>
2007-04-26 12:10 ` Re: Fragmentation of WAL files Heikki Linnakangas <heikki@enterprisedb.com>
2007-04-26 15:37 ` Re: Fragmentation of WAL files Tom Lane <tgl@sss.pgh.pa.us>
2007-04-27 04:50 ` Re: Fragmentation of WAL files Greg Smith <gsmith@gregsmith.com>
0 siblings, 3 replies; 5+ messages in thread
From: Bill Moran @ 2007-04-26 12:00 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fragmentation of WAL files
2007-04-25 17:26 Fragmentation of WAL files Jim Nasby <decibel@decibel.org>
2007-04-26 12:00 ` Re: Fragmentation of WAL files Bill Moran <wmoran@collaborativefusion.com>
@ 2007-04-26 12:10 ` Heikki Linnakangas <heikki@enterprisedb.com>
2 siblings, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2007-04-26 12:10 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Jim Nasby <decibel@decibel.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fragmentation of WAL files
2007-04-25 17:26 Fragmentation of WAL files Jim Nasby <decibel@decibel.org>
2007-04-26 12:00 ` Re: Fragmentation of WAL files Bill Moran <wmoran@collaborativefusion.com>
@ 2007-04-26 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-04-26 15:37 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Jim Nasby <decibel@decibel.org>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fragmentation of WAL files
2007-04-25 17:26 Fragmentation of WAL files Jim Nasby <decibel@decibel.org>
2007-04-26 12:00 ` Re: Fragmentation of WAL files Bill Moran <wmoran@collaborativefusion.com>
@ 2007-04-27 04:50 ` Greg Smith <gsmith@gregsmith.com>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Smith @ 2007-04-27 04:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-04-27 04:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-25 17:26 Fragmentation of WAL files Jim Nasby <decibel@decibel.org>
2007-04-26 12:00 ` Bill Moran <wmoran@collaborativefusion.com>
2007-04-26 12:10 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-04-26 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-27 04:50 ` Greg Smith <gsmith@gregsmith.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox