pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
10+ messages / 6 participants
[nested] [flat]
* Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 06:11 mudfoot@rawbw.com
0 siblings, 1 reply; 10+ messages in thread
From: mudfoot@rawbw.com @ 2004-09-13 06:11 UTC (permalink / raw)
To: pgsql-performance
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.
Summary first...
<halfjoke>
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().
</halfjoke>
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.
-----------
Test system(s)
Athlon Linux:
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
Mac Linux:
I don't know the specific model. 400MHz G3, 512MB, single IDE disk,
ext2 filesystem
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.
ext3 filesystem.
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,
ffs filesystem
OpenBSD 3.2 GENERIC kernel
SUN Ultra2:
Ultra2, 2 x 296MHz UltraSPARC II, 2GB RAM, 2 x 10,000RPM 18GB U160
SCSI drives mirrored with Solstice DiskSuite. UFS filesystem.
Solaris 8.
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
connection.
Veritas filesystem (VxFS)
Solaris 8.
Test methodology:
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
buffered: 48220
fsync: 74854397
fdatasync: 75061357
open_sync: 73869239
open_datasync: 74748145
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
buffered: 58912
fsync: 1539079
fdatasync: 769058
open_sync: 767094
open_datasync: 763074
Notes: system mostly idle. fsync seems worst. Otherwise, they seem
pretty equivalent. This is the fastest system tested.
PLATFORM: HP Intel Linux
buffered: 33026
fsync: 29330067
fdatasync: 28673880
open_sync: 8783417
open_datasync: 8747971
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
buffered: 511890
fsync: 1769190
fdatasync: --------
open_sync: 1748764
open_datasync: 1747433
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
buffered: 1814824
fsync: 73954800
fdatasync: 52594532
open_sync: 34405585
open_datasync: 13883758
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
buffered: 233947
fsync: 57802065
fdatasync: 56631013
open_sync: 2362207
open_datasync: 1976057
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
open_datasync methods.
--------------
Thanks for reading...I look forward to feedback, and hope to be helpful in
this effort!
Mark
Attachments:
syncbench.c (text/x-csrc, 8.7K) download
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <errno.h>
#include <string.h>
#include <sys/time.h>
/*
*********************************************************
License:
syncbench: a program to help determine optimal i/o synchronization
mechanisms
written by Mark Travis
Copyright (c) 2004 Mark Travis
Permission to use, copy, modify, and distribute this software and
its documentation for any purpose and without fee is hereby
granted, provided that the above copyright notice appear in all
copies and that both that copyright notice and this permission
notice appear in supporting documentation, and that the name of the
author not be used in advertising or publicity pertaining to
distribution of the software without specific, written prior
permission. The author makes no representations about the
suitability of this software for any purpose. It is provided "as
is" without express or implied warranty.
GOAL:
To help "Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options"
from the PostgreSQL TODO list (http://developer.postgresql.org/todo.php)
WHAT THIS DOES:
Writes sequential chunks of data to a file and synchronizes with the
I/O device in a variety of ways. Gives results in microseconds.
EDIT BEFORE COMPILING:
There are 6 things which must be defined in order for this thing to
compile.
FILE_SYNC and OPEN_SYNC define how file contents are to be sync'd, and
each platform has various options. FILE_SYNC defines the function
call made to sync after each write(). Most platforms should support fsync(2)
at a minimum. fdatasync(2) should be faster on platforms which support
it.
OPEN_SYNC defines the flag to be set in open(2) for
synchronizing all writes without explicitly calling fsync(2) or its
kind. Most platforms should support O_SYNC at a minimum. O_DSYNC should be
faster on platforms which support it. And some environments have
neither. I could only find O_FSYNC on FreeBSD 4.10. Anyway, reading man
pages for open(2), fsync(2), and fdatasync(2) is probably a good idea
before setting FILE_SYNC and OPEN_SYNC.
******************************************************
*/
/*
#define FILE_SYNC(X) fdatasync(X)
#define OPEN_SYNC O_DSYNC
*/
/*
**********************************************************
The other 4 macros are CHUNKSIZE, CHUNKS, FILESIZE_MULTIPLIER, and
SLEEP. CHUNKSIZE is the size of each chunk of data written in the test.
CHUNKS is the number of times they are written. All writes are
sequential. Before the test is run, a file is created, filled with
"A" characters, fsync(2)'d, and then closed. Then we sleep for SLEEP
seconds before proceeding with the actual benchmark run. The sleep
is to let the I/O device quiesce if it wants to.
The size of the file created is FILESIZE_MULTIPLER times CHUNKSIZE
times CHUNKS. So if FILESIZE_MULTIPLIER exceeds 1 then the file will
be bigger than the amount of data written to it. Having the file size
equal to or exceeding the amount of data written should help to simulate
real-world WAL behavior. Extending the size of a file requires extra
work for the filesystem to perform. To learn the impact of that, go
ahead and set FILESIZE_MULTIPLIER to less than 1 if you want.
*******************************************************
*/
/*
#define CHUNKSIZE 8 * 1024
#define CHUNKS 2 * 1024
#define FILESIZE_MULTIPLIER 2
#define SLEEP 5
*/
/*
*******************************************************
Nothing else should need to be modified from this point, but please
keep reading for building and running instructions.
BUILDING:
It should be simple as long as the right tools are in your $PATH (cc,
etc.)
Linux and *BSD seem to be happy with this:
cc syncbench.c -o syncbench
Solaris 8 requires -lrt in order to support fdatasync:
cc syncbench.c -lrt -o syncbench
Other O/S's you'll have to figure out on your own.
RUNNING:
It takes two arguments. The first is the name of the file to use for
the test. If the file doesn't exist already, it will be created. If
it does exist already, then it will be truncated before writing.
The second argument is the mode in which file data is synchronized to
disk. It can be one of buffered, filesync and opensync. buffered
means no syncing takes place. Unless your filesystem is mounted
synchronously this should be the fastest option by far. filesync
executes the function defined by FILE_SYNC above after each chunk of
data is written. opensync open()'s the file with the flag defined in
OPEN_SYNC.
PostgreSQL supports 5 methods of sync'ing if you count "fsync=false". These
methods are in postgresql.conf under the "WRITE AHEAD LOG" section.
Here's how to use this tool to try and simulate those methods:
PostgreSQL Method: Syncbench
fsync=false run with 2nd argument=buffered
fsync #define FILE_SYNC(X) fsync(X)
2nd argument=filesync
fdatasync #define FILE_SYNC(X) fdatasync(X)
2nd argument=filesync
open_sync #define OPEN_SYNC O_SYNC
2nd argument=opensync
open_datasync #define OPEN_SYNC O_DSYNC
2nd argument=opensync
Obviously, if the platform doesn't support those things for whatever
reason then the program won't build.
RESULTS:
The number of microseconds between writes starting and ending is
displayed. Time taken to pre-populate, open(), or close() the
datafile is not included in this calculation. Obviously, the results
will vary based on the amount of data written, synchronization
options used, hardware, O/S, filesystem, etc. It's a good idea to try
to run this on systems which are as idle as possible -- especially the
I/O device(s) being tested.
*******************************************************
*/
int main(int argc, char *argv[])
{
int fd, n, bm_openflag, bm_do_filesync;
char *buf;
struct timeval tv_before, tv_after;
struct timezone tz_garbage;
if ( argc != 3 )
{
printf("usage: %s <filename> <buffered|filesync|opensync>\n", argv[0] );
exit(1);
}
bm_openflag=0;
bm_do_filesync=0;
if ( !strncmp( argv[2], "buffered", strlen("async") ) )
{
printf("test uses %s\n", argv[2]);
} else if ( !strncmp( argv[2], "filesync", strlen("filesync") ) )
{
printf("test uses %s\n", argv[2]);
bm_do_filesync=1;
} else if ( !strncmp( argv[2], "opensync", strlen("opensync") ) )
{
printf("test uses %s\n", argv[2]);
bm_openflag=OPEN_SYNC;
} else
{
puts("Second argument must be one of async filesync opensync");
exit(1);
}
printf("Starting test with FILESIZE: %i, CHUNKSIZE: %i, CHUNKS: %i\n", \
FILESIZE_MULTIPLIER * CHUNKSIZE * CHUNKS, CHUNKSIZE, CHUNKS );
fd = open( argv[1], O_WRONLY | O_CREAT | O_TRUNC, 0666 );
if ( fd == -1 )
{
perror("Can't create data file");
exit(1);
}
buf = malloc(CHUNKSIZE);
if ( buf == NULL )
{
puts("malloc choked for some reason. Bye!");
exit(1);
}
/*
**************************************************************
Make sure that the whole file is not made up of NULLs.
I seem to recall a characteristic of *NIX filesystems that likes
to not populate NULL-filled files with actual blocks full of NULLs.
This may cause actual population of the file to cause an update
of metadata, which might cause performance issues. Anyway,
pre-populate the file with "A" to hedge against that.
It may be desirable to pre-populate just like WAL is pre-populated.
Or maybe this doesn't matter.
***************************************************************
*/
memset(buf, 65, CHUNKSIZE);
for ( n=0; n<CHUNKS*FILESIZE_MULTIPLIER; n++)
{
if ( write(fd, buf, CHUNKSIZE) == -1 )
{
perror("Can't write to data file");
exit(1);
}
}
free(buf);
if ( fsync(fd) )
{
perror("Can't fsync data file");
exit(1);
}
if ( close(fd) )
{
perror("Can't close data file");
exit(1);
}
/* sleep for grins. so maybe the device slows down */
sleep(SLEEP);
/* OK it's created. Now open it again then start the show */
fd = open( argv[1], O_WRONLY | bm_openflag, 0666 );
if ( fd == -1 )
{
perror("Can't open data file for writing");
exit(1);
}
buf=malloc(CHUNKSIZE);
if ( buf == NULL )
{
puts("malloc choked for some reason.");
exit(1);
}
memset(buf, 66, CHUNKSIZE);
gettimeofday( &tv_before, &tz_garbage );
for ( n=0; n<CHUNKS; n++)
{
if ( write(fd, buf, CHUNKSIZE) == -1 )
{
perror("Can't write to existing data file");
exit(1);
}
if ( bm_do_filesync )
{
if ( FILE_SYNC(fd) )
{
perror("Can't sync data file");
exit(1);
}
}
}
gettimeofday( &tv_after, &tz_garbage );
free(buf);
printf("Elapsed usec: %i\n", ( ((tv_after.tv_sec * 1000000) +
tv_after.tv_usec) - ((tv_before.tv_sec * 1000000) + tv_before.tv_usec) ) );
}
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 14:38 Bruce Momjian <pgman@candle.pha.pa.us>
parent: mudfoot@rawbw.com
0 siblings, 2 replies; 10+ messages in thread
From: Bruce Momjian @ 2004-09-13 14:38 UTC (permalink / raw)
To: mudfoot@rawbw.com; +Cc: pgsql-performance
Have you seen /src/tools/fsync?
---------------------------------------------------------------------------
mudfoot@rawbw.com wrote:
> 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.
>
> Summary first...
>
> <halfjoke>
> 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().
> </halfjoke>
>
> 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.
>
> -----------
>
> Test system(s)
>
> Athlon Linux:
> 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
>
> Mac Linux:
> I don't know the specific model. 400MHz G3, 512MB, single IDE disk,
> ext2 filesystem
> 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.
> ext3 filesystem.
> 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,
> ffs filesystem
> OpenBSD 3.2 GENERIC kernel
>
> SUN Ultra2:
> Ultra2, 2 x 296MHz UltraSPARC II, 2GB RAM, 2 x 10,000RPM 18GB U160
> SCSI drives mirrored with Solstice DiskSuite. UFS filesystem.
> Solaris 8.
>
> 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
> connection.
> Veritas filesystem (VxFS)
> Solaris 8.
>
> Test methodology:
>
> 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
> buffered: 48220
> fsync: 74854397
> fdatasync: 75061357
> open_sync: 73869239
> open_datasync: 74748145
> 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
> buffered: 58912
> fsync: 1539079
> fdatasync: 769058
> open_sync: 767094
> open_datasync: 763074
> Notes: system mostly idle. fsync seems worst. Otherwise, they seem
> pretty equivalent. This is the fastest system tested.
>
> PLATFORM: HP Intel Linux
> buffered: 33026
> fsync: 29330067
> fdatasync: 28673880
> open_sync: 8783417
> open_datasync: 8747971
> 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
> buffered: 511890
> fsync: 1769190
> fdatasync: --------
> open_sync: 1748764
> open_datasync: 1747433
> 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
> buffered: 1814824
> fsync: 73954800
> fdatasync: 52594532
> open_sync: 34405585
> open_datasync: 13883758
> 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
> buffered: 233947
> fsync: 57802065
> fdatasync: 56631013
> open_sync: 2362207
> open_datasync: 1976057
> 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
> open_datasync methods.
>
> --------------
>
> Thanks for reading...I look forward to feedback, and hope to be helpful in
> this effort!
>
> Mark
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 19:32 mudfoot@rawbw.com
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: mudfoot@rawbw.com @ 2004-09-13 19:32 UTC (permalink / raw)
To: pgsql-performance
Quoting Bruce Momjian <pgman@candle.pha.pa.us>:
>
> Have you seen /src/tools/fsync?
>
I have now. Thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 20:28 Gaetano Mendola <mendola@bigfoot.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 10+ messages in thread
From: Gaetano Mendola @ 2004-09-13 20:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>
Bruce Momjian wrote:
> Have you seen /src/tools/fsync?
>
Now that the argument is already open, why postgres choose
on linux fdatasync? I'm understanding from other posts that
on this platform open_sync is better than fdatasync.
However I choose open_sync. During initdb why don't detect
this parameter ?
Regards
Gaetano Mendola
These are my times:
kernel 2.4.9-e.24smp ( RAID SCSI ):
Simple write timing:
write 0.011544
Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 1.233312
write, close, fsync 1.242086
Compare one o_sync write to two:
one 16k o_sync write 0.517633
two 8k o_sync writes 0.824603
Compare file sync methods with one 8k write:
(o_dsync unavailable)
open o_sync, write 0.438580
write, fdatasync 1.239377
write, fsync, 1.178017
Compare file sync methods with 2 8k writes:
(o_dsync unavailable)
open o_sync, write 0.818720
write, fdatasync 1.395602
write, fsync, 1.351214
kernel 2.4.22-1.2199.nptlsmp (single EIDE disk):
Simple write timing:
write 0.023697
Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 0.688765
write, close, fsync 0.702166
Compare one o_sync write to two:
one 16k o_sync write 0.498296
two 8k o_sync writes 0.543956
Compare file sync methods with one 8k write:
(o_dsync unavailable)
open o_sync, write 0.259664
write, fdatasync 0.971712
write, fsync, 1.006096
Compare file sync methods with 2 8k writes:
(o_dsync unavailable)
open o_sync, write 0.536882
write, fdatasync 1.160347
write, fsync, 1.189699
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 21:15 Josh Berkus <josh@agliodbs.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2004-09-13 21:15 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-performance
Gaetano,
> Now that the argument is already open, why postgres choose
> on linux fdatasync? I'm understanding from other posts that
> on this platform open_sync is better than fdatasync.
Not necessarily. For example, here's my test results, on Linux 2.6.7,
writing to a ReiserFS mount on a Software RAID 1 slave of 2 IDE disks, on an
Athalon 1600mhz single-processor machine. I ran the loop 10,000 times
instead of 1000 because tests with 1,000 varied too much.
Simple write timing:
write 0.088701
Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 3.593958
write, close, fsync 3.556978
Compare one o_sync write to two:
one 16k o_sync write 42.951595
two 8k o_sync writes 11.251389
Compare file sync methods with one 8k write:
(o_dsync unavailable)
open o_sync, write 6.807060
write, fdatasync 7.207879
write, fsync, 7.209087
Compare file sync methods with 2 8k writes:
(o_dsync unavailable)
open o_sync, write 13.120305
write, fdatasync 7.583871
write, fsync, 7.801748
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-13 21:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2004-09-13 21:18 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-performance
Gaetano Mendola <mendola@bigfoot.com> writes:
> Now that the argument is already open, why postgres choose
> on linux fdatasync? I'm understanding from other posts that
> on this platform open_sync is better than fdatasync.
AFAIR, we've seen *one* test from *one* person alleging that.
And it was definitely not that way when we tested the behavior
originally, several releases back. I'd like to see more evidence,
or better some indication that the Linux kernel changed algorithms,
before changing the default.
The tests that started this thread are pretty unconvincing in my eyes,
because they are comparing open_sync against code that fsyncs after each
one-block write. Under those circumstances, *of course* fsync will lose
(or at least do no better), because it's forcing the same number of
writes through a same-or-less-efficient API. The reason that this isn't
a trivial choice is that Postgres doesn't necessarily need to fsync
after every block of WAL. In particular, when doing large transactions
there could be many blocks written between fsyncs, and in that case you
could come out ahead with fsync because the kernel would have more
freedom to schedule disk writes.
So, the only test I put a whole lot of faith in is testing your own
workload on your own Postgres server. But if we want to set up a toy
test program to test this stuff, it's at least got to have an easily
adjustable (and preferably randomizable) distance between fsyncs.
Also, tests on IDE drives have zero credibility to start with, unless
you can convince me you know how to turn off write buffering on the
drive...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-14 01:28 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2004-09-14 01:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-performance
Tom Lane wrote:
> The tests that started this thread are pretty unconvincing in my eyes,
> because they are comparing open_sync against code that fsyncs after each
> one-block write. Under those circumstances, *of course* fsync will lose
> (or at least do no better), because it's forcing the same number of
> writes through a same-or-less-efficient API. The reason that this isn't
> a trivial choice is that Postgres doesn't necessarily need to fsync
> after every block of WAL. In particular, when doing large transactions
> there could be many blocks written between fsyncs, and in that case you
> could come out ahead with fsync because the kernel would have more
> freedom to schedule disk writes.
My guess is that the majority of queries do not fill more than one WAL
block. Sure some do, but in those cases the fsync is probably small
compared to the duration of the query. If we had a majority of queries
filling more than one block we would be checkpointing like crazy and we
don't normally get reports about that.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-14 01:39 Gaetano Mendola <mendola@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Gaetano Mendola @ 2004-09-14 01:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
| Gaetano Mendola <mendola@bigfoot.com> writes:
|
|>Now that the argument is already open, why postgres choose
|>on linux fdatasync? I'm understanding from other posts that
|>on this platform open_sync is better than fdatasync.
|
|
| AFAIR, we've seen *one* test from *one* person alleging that.
| And it was definitely not that way when we tested the behavior
| originally, several releases back. I'd like to see more evidence,
| or better some indication that the Linux kernel changed algorithms,
| before changing the default.
I remember more then one person claim that open_sync *apparently*
was working better then fdatasync, however I trust you ( here is
3:00 AM ).
| The tests that started this thread are pretty unconvincing in my eyes,
| because they are comparing open_sync against code that fsyncs after each
| one-block write. Under those circumstances, *of course* fsync will lose
| (or at least do no better), because it's forcing the same number of
| writes through a same-or-less-efficient API.
|
| The reason that this isn't a trivial choice is that Postgres doesn't
| necessarily need to fsync after every block of WAL. In particular,
| when doing large transactions there could be many blocks written between
| fsyncs, and in that case you could come out ahead with fsync because the
| kernel would have more freedom to schedule disk writes.
Are you suggesting that postgres shall use more the one sync method and use
one or the other depending on the activity is performing ?
| So, the only test I put a whole lot of faith in is testing your own
| workload on your own Postgres server. But if we want to set up a toy
| test program to test this stuff, it's at least got to have an easily
| adjustable (and preferably randomizable) distance between fsyncs.
|
| Also, tests on IDE drives have zero credibility to start with, unless
| you can convince me you know how to turn off write buffering on the
| drive...
I reported the IDE times just for info; however my SAN works better
with open_sync. Can we trust on numbers given by tools/fsync ? I seen
some your objections in the past but I don't know if there was some fix
from that time.
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBRkvW7UpzwH2SGd4RAia1AKD2L5JLhpRNvBzPq9Lv5bAfFJvRmwCffjC5
hg7V0Sfm2At7yR1C+gBCzPE=
=RsSy
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-14 02:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2004-09-14 02:00 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-performance
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> If we had a majority of queries filling more than one block we would
> be checkpointing like crazy and we don't normally get reports about
> that.
[ raised eyebrow... ] And of course the 30-second-checkpoint-warning
stuff is a useless feature that no one ever exercises.
But your logic doesn't hold up anyway. People may be doing large
transactions without necessarily doing them back-to-back-to-back;
there could be idle time in between. For instance, I'd think an average
transaction size of 100 blocks would be more than enough to make fsync a
winner. There are 2K blocks per WAL segment, so 20 of these would fit
in a segment. With the default WAL parameters you could do sixty such
transactions per five minutes, or one every five seconds, without even
causing more-frequent-than-default checkpoints; and you could do two a
second without setting off the checkpoint-warning alarm. The lack of
checkpoint complaints doesn't prove that this isn't a common real-world
load.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
@ 2004-09-14 18:19 Vivek Khera <khera@kcilink.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Vivek Khera @ 2004-09-14 18:19 UTC (permalink / raw)
To: pgsql-performance
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
TL> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> If we had a majority of queries filling more than one block we would
>> be checkpointing like crazy and we don't normally get reports about
>> that.
TL> [ raised eyebrow... ] And of course the 30-second-checkpoint-warning
TL> stuff is a useless feature that no one ever exercises.
Well, last year about this time I discovered in my testing I was
excessively checkpointing; I found that the error message was
confusing, and Bruce cleaned it up. So at least one person excercised
that feature, namely me. :-)
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-09-14 18:19 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-13 06:11 Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options mudfoot@rawbw.com
2004-09-13 14:38 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-09-13 19:32 ` mudfoot@rawbw.com
2004-09-13 20:28 ` Gaetano Mendola <mendola@bigfoot.com>
2004-09-13 21:15 ` Josh Berkus <josh@agliodbs.com>
2004-09-13 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-14 01:28 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-09-14 02:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-14 18:19 ` Vivek Khera <khera@kcilink.com>
2004-09-14 01:39 ` Gaetano Mendola <mendola@bigfoot.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