Re: Postcrash

> Ich befürchte, die SD card und/oder das System schummeln gewaltig was
> fsync() angeht. In diesem Fall hat Postgres wenig Chance auf
> Crash-Resilience.
> Prüf das System doch mal mit dem pg_test_fsync tool aus contrib.

Habe ich inzwischen hinbekommen (ich habe außerdem eine nun SLC SD-Karte
eingelegt, das FS auf EXT3 umgestellt und auch selber die Rolle des
Fensterputzer übernommen :-))
Leider sagen mir die Ausgabe von pg_test_fsync nichts, also kann ich auch
nicht bewerten, ob das System schummelt:
-----------------
#pg_test_fsync --version
pg_test_fsync (PostgreSQL) 9.2.4

#su -c "pg_test_fsync -f /var/postgresql/fsync.test" -s /bin/sh postgres
2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 24.879 ops/sec
fdatasync 24.471 ops/sec
fsync 25.075 ops/sec
fsync_writethrough n/a
open_sync 24.364 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 21.702 ops/sec
fdatasync 21.849 ops/sec
fsync 21.671 ops/sec
fsync_writethrough n/a
open_sync 20.889 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write 21.626 ops/sec
2 * 8kB open_sync writes 7.353 ops/sec
4 * 4kB open_sync writes 19.326 ops/sec
8 * 2kB open_sync writes 18.309 ops/sec
16 * 1kB open_sync writes 15.349 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 23.698 ops/sec
write, close, fsync 24.600 ops/sec

Non-Sync'ed 8kB writes:
write 24028.662 ops/sec

#su -c "pg_test_fsync -f /var/postgresql/fsync.test -s 10" -s /bin/sh
postgres
10 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 258.480 ops/sec
fdatasync 249.492 ops/sec
fsync 180.480 ops/sec
fsync_writethrough n/a
open_sync 255.945 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 102.687 ops/sec
fdatasync 127.296 ops/sec
fsync 129.286 ops/sec
fsync_writethrough n/a
open_sync 104.276 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write 133.724 ops/sec
2 * 8kB open_sync writes 104.398 ops/sec
4 * 4kB open_sync writes 83.926 ops/sec
8 * 2kB open_sync writes 51.112 ops/sec
16 * 1kB open_sync writes 29.085 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 241.880 ops/sec
write, close, fsync 241.513 ops/sec

Non-Sync'ed 8kB writes:
write 25772.686 ops/sec

-----------------

Viele Grüße
Lars