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