3 ms·

What about postgres passthrough to disk. Is it better to do something like that? I normally see postgres on zfs, but I wonder.

by R0b0t1 5y ago

hardwaresofton 5y ago
Please see my sibling response -- Postgres doesn't do async nor direct IO, default is fdatasync() now on linux and before it was fsync(). Here's a breakdown of the write path that is mostly correct which I typed out on reddit[0]: - (Postgres) transaction occurs, generating WAL records (write()s are issued) - (ZFS) receives filesystem writes makes transaction(s) and puts them in the current transaction group ("txg"), in memory - Exception: assuming logbias=latency if size is bigger than zfs_immediate_write_sz then the write goes straight to disk and a pointer is written into the ZIL instead... Bit blurry on this - (Postgres) wal_write_delay time passes (default 200ms) or enough wal_writer_flush_after (default 1MB) data is added - (Postgres) initiates WAL flush to disk -- i.e. calling fsync() - (ZFS) sees the fsync(), and this means it must do a file-specific ZIL flush - it is possible for the txg to be committed first which will also trigger a ZIL flush - (ZFS) performs a ZIL flush, writing ZIL records from memory to disk - (ZFS) zfs_txg_timeout seconds passes and a "SPA sync" is performed - (ZFS) transactions in the current transaction group are processed (+/- compression, encryption, etc) and are written to disk - This does NOT concern the ZIL records, these are writes in the transaction group that have not already been committed - [CORRECTION] direct sync records written as ZIL blocks are also added to the current TxG in memory. They are processed identically to async writes come TxG commit time and aggregate with them (they are async writes once durability is assured with a ZIL write). (see this comment by /u/taratarabobara) All that said I haven't read/written enough of either codebase (ZFS/Postgres) to consider myself an expert, so there may be logic bugs in there! [0]: https://www.reddit.com/r/zfs/comments/rlfhxb/why_not_txg_timeout1/ https://www.reddit.com/r/zfs/comments/rlfhxb/why_not_txg_tim...

What about postgres passthrough to disk. Is it better to do something like that? I normally see postgres on zfs, but I wonder.

by R0b0t1 5y ago

What about postgres passthrough to disk. Is it better to do something like that? I normally see postgres on zfs, but I wonder.

hardwaresofton 5y ago

Please see my sibling response -- Postgres doesn't do async nor direct IO, default is fdatasync() now on linux and before it was fsync(). Here's a breakdown of the write path that is mostly correct which I typed out on reddit[0]: - (Postgres) transaction occurs, generating WAL records (write()s are issued) - (ZFS) receives filesystem writes makes transaction(s) and puts them in the current transaction group ("txg"), in memory - Exception: assuming logbias=latency if size is bigger than zfs_immediate_write_sz then the write goes straight to disk and a pointer is written into the ZIL instead... Bit blurry on this - (Postgres) wal_write_delay time passes (default 200ms) or enough wal_writer_flush_after (default 1MB) data is added - (Postgres) initiates WAL flush to disk -- i.e. calling fsync() - (ZFS) sees the fsync(), and this means it must do a file-specific ZIL flush - it is possible for the txg to be committed first which will also trigger a ZIL flush - (ZFS) performs a ZIL flush, writing ZIL records from memory to disk - (ZFS) zfs_txg_timeout seconds passes and a "SPA sync" is performed - (ZFS) transactions in the current transaction group are processed (+/- compression, encryption, etc) and are written to disk - This does NOT concern the ZIL records, these are writes in the transaction group that have not already been committed - [CORRECTION] direct sync records written as ZIL blocks are also added to the current TxG in memory. They are processed identically to async writes come TxG commit time and aggregate with them (they are async writes once durability is assured with a ZIL write). (see this comment by /u/taratarabobara) All that said I haven't read/written enough of either codebase (ZFS/Postgres) to consider myself an expert, so there may be logic bugs in there! [0]: https://www.reddit.com/r/zfs/comments/rlfhxb/why_not_txg_timeout1/ https://www.reddit.com/r/zfs/comments/rlfhxb/why_not_txg_tim...