Hi, I'd like to help with the topic in the Subject: line. It seems to be a
TODO item. I've reviewed some threads discussing the matter, so I hope I've
acquired enough history concerning it. I've taken an initial swipe at
figuring out how to optimize sync'ing methods. It's based largely on
recommendations I've read on previous threads about fsync/O_SYNC and so on.
After reviewing, if anybody has recommendations on how to proceed then I'd
love to hear them.
Attached is a little program that basically does a bunch of sequential writes
to a file. All of the sync'ing methods supported by PostgreSQL WAL can be
used. Results are printed in microseconds. Size and quanity of writes are
configurable. The documentation is in the code (how to configure, build, run,
etc.). I realize that this program doesn't reflect all of the possible
activities of a production database system, but I hope it's a step in the
right direction for this task. I've used it to see differences in behavior
between the various sync'ing methods on various platforms.
Here's what I've found running the benchmark on some systems to which
I have access. The differences in behavior between platforms is quite vast.
PostgreSQL should be run on an old Apple MacIntosh attached to
its own Hitachi disk array with 2GB cache or so. Use any sync method
except for fsync().
Anyway, there is *a lot* of variance in file synching behavior across
different hardware and O/S platforms. It's probably not safe
to conclude much. That said, here are some findings so far based on
tests I've run:
1. under no circumstances do fsync() or fdatasync() seem to perform
better than opening files with O_SYNC or O_DSYNC
2. where there are differences, opening files with O_SYNC or O_DSYNC
tends to be quite faster.
3. fsync() seems to be the slowest where there are differences. And
O_DSYNC seems to be the fastest where results differ.
4. the safest thing to assert at this point is that
Solaris systems ought to use the O_DSYNC method for WAL.
AMD Athlon XP2000, 512MB RAM, single (54 or 7200?) RPM 20GB IDE disk,
reiserfs filesystem (3 something I think)
SuSE Linux kernel 2.4.21-99
I don't know the specific model. 400MHz G3, 512MB, single IDE disk,
Debian GNU/Linux 2.4.16-powerpc
HP Intel Linux:
Prolient HPDL380G3, 2 x 3GHz Xeon, 2GB RAM, SmartArray 5i 64MB cache,
2 x 15,000RPM 36GB U320 SCSI drives mirrored. I'm not sure if
writes are cached or not. There's no battery backup.
Redhat Enterprise Linux 3.0 kernel based on 2.4.21
Dell Intel OpenBSD:
Poweredge ?, single 1GHz PIII, 128MB RAM, single 7200RPM 80GB IDE disk,
OpenBSD 3.2 GENERIC kernel
Ultra2, 2 x 296MHz UltraSPARC II, 2GB RAM, 2 x 10,000RPM 18GB U160
SCSI drives mirrored with Solstice DiskSuite. UFS filesystem.
SUN E4500 + HDS Thunder 9570v
E4500, 8 x 400MHz UltraSPARC II, 3GB RAM,
HDS Thunder 9570v, 2GB mirrored battery-backed cache, RAID5 with a
bunch of 146GB 10,000RPM FC drives. LUN is on single 2GB FC fabric
Veritas filesystem (VxFS)
All test runs were done with CHUNKSIZE 8 * 1024, CHUNKS 2 * 1024,
FILESIZE_MULTIPLIER 2, and SLEEP 5. So a total of 16MB was sequentially
written for each benchmark.
Results are in microseconds.
PLATFORM: Athlon Linux
Notes: System mostly idle. Even during tests, top showed about 95%
idle. Something's not right on this box. All sync methods similarly
horrible on this system.
PLATFORM: Mac Linux
Notes: system mostly idle. fsync seems worst. Otherwise, they seem
pretty equivalent. This is the fastest system tested.
PLATFORM: HP Intel Linux
Notes: system idle. O_SYNC and O_DSYNC methods seem to be a lot
better on this platform than fsync & fdatasync.
PLATFORM: Dell Intel OpenBSD
Notes: system idle. I couldn't locate fdatasync() on this box, so I
couldn't test it. All sync methods seem equivalent and are very fast --
though still trail the old Mac.
PLATFORM: SUN Ultra2
Notes: system mostly idle, with occasional spikes from 1-10% utilization.
It looks like substantial difference between each sync method, with
O_DSYNC the best and fsync() the worst. There is substantial
difference between the open* and f* methods.
PLATFORM: SUN E4500 + HDS Thunder 9570v
Notes: host about 30% idle, but the array tested on was completely idle.
Something looks seriously not right about fsync and fdatasync -- write
cache seems to have no effect on them. As for write cache, that
probably explains the 2 seconds or so for the open_sync and
Thanks for reading...I look forward to feedback, and hope to be helpful in
Description: text/x-csrc (8.7 KB)
pgsql-performance by date
|Next:||From: Mark Cotner||Date: 2004-09-13 07:57:52|
|Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables|
|Previous:||From: Vijay Moses||Date: 2004-09-13 04:30:41|
|Subject: Four table join with million records - performance improvement?|