6 ms·

If you are using ZFS, I strongly recommend using LZ4 or ZSTD compression with PostgreSQL. Performance is still awesome. On average I get 2x compressionratio wit

by olavgg 5y ago

If you are using ZFS, I strongly recommend using LZ4 or ZSTD compression with PostgreSQL. Performance is still awesome. On average I get 2x compressionratio with LZ4 and 4x with ZSTD. With this, you are compressing everything, not just columns. And ZFS has dynamic block sizes which works really great together with compression. For example a 8kb PostgreSQL page, may be stored as a 1kb compressed block on disk.

RedShift1 5y ago
So disable compression in Postgres and do all compression in ZFS?
- mattashii 5y ago
  Postgres' own compression helps with memory usage in buffers, saving IOps, so I'd keep my compression in PostgreSQL enabled
  - throwdbaaway 5y ago
    Compressed ARC has been a thing in ZFS for a long time. So let's say you allow ZFS cache to take 50% of total memory. With 4x compress ratio from ZSTD, you will be able to cache 2x the total memory. It is amazing.
    - deleted 5y ago
      [deleted]
    - nix23 5y ago
      You should not use ARC for pg data, since postgres has it's own cache, otherwise you just have two caches with the same data in it.
      - throwdbaaway 5y ago
        Unfortunately that's just how postgres works? I thought the rule of thumb is still to allocate 25% of memory for postgres 's shared buffer, and 50% of memory for OS buffer cache (i.e. ARC when using ZFS)?
        ComputerGuru 5y ago
        That’s how postgres works, but ZFS lets you work around that by disabling arc caching on a dataset by dataset basis.
        nix23 5y ago
        >Unfortunately No, postgres know better what to cache then arc.
        radiowave 5y ago
        For a transaction processing workload (where hitting indexes is the all-important thing), that's exactly right. But if you have more of an analytic workload involving sequential scans, Postgres definitely expects the underlying storage system to be handling at least some of the caching.
    - mattashii 5y ago
      Yes, but on the other hand, my toast relation now needs (e.g.) 4x the amount of blocks it would have had when compression was enabled. So we have a net 0 storage savings, except that we now need to request 4x as many pages from the OS to get the full toast data, those pages taking up 4x as much space in Postgres' shared buffers, also evicting 4x as many pages from the buffers as it would have otherwise. Unless your read operations to the file system are somehow very cheap, I don't think disabling toast compression is a good idea.
      - throwdbaaway 5y ago
        I see your point. Perhaps the best way is to use TOAST compression for wide field values, and then rely on ZFS filesystem compression for others? For some context, in MySQL, both the table-level compression and page-level compression are problematic, while ZFS has served me well, thus I have a lot more faith in it.
        ComputerGuru 5y ago
        > a Perhaps the best way is to use TOAST compression for wide field values You don’t have to do anything. Toast compression is enabled for compatible types by default but never kicks in until a cell hits 2K bytes in size (by default).
      - magicalhippo 5y ago
        LZ4 is quite fast to bail on data it can't compress, so why not just have LZ4 enabled on both?
- nix23 5y ago
  I would recommend that (ATM?): For high-speed pgsql (on ZFS) enable zfs-compression (lz4) just on the filesystem, and disable zfs-caching (the arc, just leave metadata on) because postgres has it's own cache. >ZFS-Dataset: atime=off compression=lz4 recordsize=8k logbias=throughput xattr=sa redundant_metadata=most <===That's a maybe primarycache=metadata >In postgres: full_page_writes=off <===That's a maybe >Plus maybe some pgtune: https://pgtune.leopard.in.ua/#/ https://pgtune.leopard.in.ua/#/
  - RedShift1 5y ago
    What about VDEV organization in a virtual machines? I have no physical machines left, everything is running on VMware. Just create one big VDEV?
    - nix23 5y ago
      Yes in a virtual machine just one vdev/dataset (if it's about speed, if redundancy...well that depends on you host-filesystem, then maybe two virtual disks?), maybe some tuning with the write-through etc on the Host to that virtual-disk? And raw would be a good thing too.
  - ahachete 5y ago
    >Plus maybe some pgtune: Alternative tuning guide: https://postgresqlco.nf/tuning-guide https://postgresqlco.nf/tuning-guide (Disclosure: part of the team behind it)
  - paulmd 5y ago
    Have not used this in anger but - from what I've read best practice is also to use separate datasets for the WAL and the rest of the data (including committed data) - this helps ZFS understand that WAL and committed are both separate datasets that both need separate QOS handling within ARC rather than just letting the WAL eat the whole cache. It'll try its best to balance them regardless, of course. You can still snapshot them atomically by making them both children of a parent dataset and performing a recursive snapshot on the parent. so you have dataset: myservice/pgdata myservice/pgwal or myservice/pgdata myservice/pgdata/pgwal or whatever. So question, given that this article is about LZ4 memory compression for postgres, would you want it enabled for both/either/none of those datasets? Obviously compression-on-compression doesn't generally help at all, but lz4 performance obviously doesn't hurt much at all either, so if there's anything that postgres doesn't compress, maybe it would be faster in a few niche situations.
nikita2206 5y ago
Are you finding the benefits and features of ZFS worth the perf penalty? Or maybe even its compression makes it overall a faster alternative to say ext4 for a database? I’ve considered in the past running a DB in AWS with direct nvme drive used for SLOG and for L2ARC cache while the main storage would have been a mounted EBS volume, I haven’t tried it but am still wondering if it’s a better alternative to running a DB on top of bare EBS in Amazon (it should be a lot cheaper than say io2 type volume with provisioned IOPS)
- throwdbaaway 5y ago
  With cloud network-based storage like EBS or pd-ssd, I doubt you will notice any IOPS-related perf penalty when running a RDBMS on top of ZFS. Assuming a mixed read/write workload, you will likely hit the disk write throughput limit first, as ZFS just writes ... a lot more, due to how blocks are stored internally: https://github.com/openzfs/zfs/issues/6584#issuecomment-384805998 https://github.com/openzfs/zfs/issues/6584#issuecomment-3848... On an innodb database, I get about 3x compress ratio with ZSTD compression, and ZFS still has to write about 2x more than EXT4.
- Filligree 5y ago
  There just isn't much of a perf penalty. It uses more CPU, yes, but with today's highly-threaded CPUs I can hardly tell there's a cost. For any data you want to keep, I'd say it's well worth the performance cost. Even for data you don't want to keep, so long as it isn't absolutely performance-critical I find it still worth the cost.
  - pizza234 5y ago
    > There just isn't much of a perf penalty. It uses more CPU, yes Not in my (although small) experience with ZFS vs. databases; the bottleneck is the disk (although it's correct that ZFS is quite CPU-intensive). Databases have their logging strategies, and ZFS has its own too. So the expense of a write operation is (in principle) doubled. AFAIK, the typical strategy to mitigate the ZFS overhead is to use a log on a separate, fast storage ("SLOG"). On the db side, in MySQL, it's possible to disable the doublewrite buffer (which is another form of overhead, which, in theory, ZFS should avoid), but nobody really knows if that is 100% reliable or not.
rob_c 5y ago
Was about to recommend the same. There a potentially big wins from db performance with mariadb+ZFS too, depends on the workload. Also ZFS supports zstd now too rather than just lz4
kiririn 5y ago
> And ZFS has dynamic block sizes which works really great together with compression This is not just a nice-to-have but is the reason why it is viable at all. In comparison, compression in Btrfs for this sort of workload produces insane write amplification (20x or more), as it forces large blocks for compression
dekobon 5y ago
If you are going to be using PostgreSQL with ZFS, be sure to set: wal_init_zero = off # zero-fill new WAL files wal_recycle = off # recycle WAL files Otherwise, you will see some intermittent performance issues.
traceroute66 5y ago
Regarding the general point about PostgreSQL and ZFS it might be noted that the community are not too keen on it (mostly from a performance perspective) as per recent mailing list thread[1]. [1]https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOHuZH8CJsKGZw7DEVtJPf0s_oPEvUKwFewHcDQFCUnutcA9GYIkOrRfFAKRsyj0ZFN9ECAbLm-NYPR8jMxTl-RU-U3hA%3D%40protonmail.ch https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOH...

by olavgg 5y ago

RedShift1 5y ago

So disable compression in Postgres and do all compression in ZFS?

mattashii 5y ago
Postgres' own compression helps with memory usage in buffers, saving IOps, so I'd keep my compression in PostgreSQL enabled
- throwdbaaway 5y ago
  Compressed ARC has been a thing in ZFS for a long time. So let's say you allow ZFS cache to take 50% of total memory. With 4x compress ratio from ZSTD, you will be able to cache 2x the total memory. It is amazing.
  - deleted 5y ago
    [deleted]
  - nix23 5y ago
    You should not use ARC for pg data, since postgres has it's own cache, otherwise you just have two caches with the same data in it.
    - throwdbaaway 5y ago
      Unfortunately that's just how postgres works? I thought the rule of thumb is still to allocate 25% of memory for postgres 's shared buffer, and 50% of memory for OS buffer cache (i.e. ARC when using ZFS)?
      - ComputerGuru 5y ago
        That’s how postgres works, but ZFS lets you work around that by disabling arc caching on a dataset by dataset basis.
      - nix23 5y ago
        >Unfortunately No, postgres know better what to cache then arc.
        radiowave 5y ago
        For a transaction processing workload (where hitting indexes is the all-important thing), that's exactly right. But if you have more of an analytic workload involving sequential scans, Postgres definitely expects the underlying storage system to be handling at least some of the caching.
  - mattashii 5y ago
    Yes, but on the other hand, my toast relation now needs (e.g.) 4x the amount of blocks it would have had when compression was enabled. So we have a net 0 storage savings, except that we now need to request 4x as many pages from the OS to get the full toast data, those pages taking up 4x as much space in Postgres' shared buffers, also evicting 4x as many pages from the buffers as it would have otherwise. Unless your read operations to the file system are somehow very cheap, I don't think disabling toast compression is a good idea.
    - throwdbaaway 5y ago
      I see your point. Perhaps the best way is to use TOAST compression for wide field values, and then rely on ZFS filesystem compression for others? For some context, in MySQL, both the table-level compression and page-level compression are problematic, while ZFS has served me well, thus I have a lot more faith in it.
      - ComputerGuru 5y ago
        > a Perhaps the best way is to use TOAST compression for wide field values You don’t have to do anything. Toast compression is enabled for compatible types by default but never kicks in until a cell hits 2K bytes in size (by default).
    - magicalhippo 5y ago
      LZ4 is quite fast to bail on data it can't compress, so why not just have LZ4 enabled on both?
nix23 5y ago
I would recommend that (ATM?): For high-speed pgsql (on ZFS) enable zfs-compression (lz4) just on the filesystem, and disable zfs-caching (the arc, just leave metadata on) because postgres has it's own cache. >ZFS-Dataset: atime=off compression=lz4 recordsize=8k logbias=throughput xattr=sa redundant_metadata=most <===That's a maybe primarycache=metadata >In postgres: full_page_writes=off <===That's a maybe >Plus maybe some pgtune: https://pgtune.leopard.in.ua/#/ https://pgtune.leopard.in.ua/#/
- RedShift1 5y ago
  What about VDEV organization in a virtual machines? I have no physical machines left, everything is running on VMware. Just create one big VDEV?
  - nix23 5y ago
    Yes in a virtual machine just one vdev/dataset (if it's about speed, if redundancy...well that depends on you host-filesystem, then maybe two virtual disks?), maybe some tuning with the write-through etc on the Host to that virtual-disk? And raw would be a good thing too.
- ahachete 5y ago
  >Plus maybe some pgtune: Alternative tuning guide: https://postgresqlco.nf/tuning-guide https://postgresqlco.nf/tuning-guide (Disclosure: part of the team behind it)
- paulmd 5y ago
  Have not used this in anger but - from what I've read best practice is also to use separate datasets for the WAL and the rest of the data (including committed data) - this helps ZFS understand that WAL and committed are both separate datasets that both need separate QOS handling within ARC rather than just letting the WAL eat the whole cache. It'll try its best to balance them regardless, of course. You can still snapshot them atomically by making them both children of a parent dataset and performing a recursive snapshot on the parent. so you have dataset: myservice/pgdata myservice/pgwal or myservice/pgdata myservice/pgdata/pgwal or whatever. So question, given that this article is about LZ4 memory compression for postgres, would you want it enabled for both/either/none of those datasets? Obviously compression-on-compression doesn't generally help at all, but lz4 performance obviously doesn't hurt much at all either, so if there's anything that postgres doesn't compress, maybe it would be faster in a few niche situations.

nikita2206 5y ago

Are you finding the benefits and features of ZFS worth the perf penalty? Or maybe even its compression makes it overall a faster alternative to say ext4 for a database? I’ve considered in the past running a DB in AWS with direct nvme drive used for SLOG and for L2ARC cache while the main storage would have been a mounted EBS volume, I haven’t tried it but am still wondering if it’s a better alternative to running a DB on top of bare EBS in Amazon (it should be a lot cheaper than say io2 type volume with provisioned IOPS)

throwdbaaway 5y ago
With cloud network-based storage like EBS or pd-ssd, I doubt you will notice any IOPS-related perf penalty when running a RDBMS on top of ZFS. Assuming a mixed read/write workload, you will likely hit the disk write throughput limit first, as ZFS just writes ... a lot more, due to how blocks are stored internally: https://github.com/openzfs/zfs/issues/6584#issuecomment-384805998 https://github.com/openzfs/zfs/issues/6584#issuecomment-3848... On an innodb database, I get about 3x compress ratio with ZSTD compression, and ZFS still has to write about 2x more than EXT4.
Filligree 5y ago
There just isn't much of a perf penalty. It uses more CPU, yes, but with today's highly-threaded CPUs I can hardly tell there's a cost. For any data you want to keep, I'd say it's well worth the performance cost. Even for data you don't want to keep, so long as it isn't absolutely performance-critical I find it still worth the cost.
- pizza234 5y ago
  > There just isn't much of a perf penalty. It uses more CPU, yes Not in my (although small) experience with ZFS vs. databases; the bottleneck is the disk (although it's correct that ZFS is quite CPU-intensive). Databases have their logging strategies, and ZFS has its own too. So the expense of a write operation is (in principle) doubled. AFAIK, the typical strategy to mitigate the ZFS overhead is to use a log on a separate, fast storage ("SLOG"). On the db side, in MySQL, it's possible to disable the doublewrite buffer (which is another form of overhead, which, in theory, ZFS should avoid), but nobody really knows if that is 100% reliable or not.

rob_c 5y ago

Was about to recommend the same. There a potentially big wins from db performance with mariadb+ZFS too, depends on the workload. Also ZFS supports zstd now too rather than just lz4

kiririn 5y ago

> And ZFS has dynamic block sizes which works really great together with compression This is not just a nice-to-have but is the reason why it is viable at all. In comparison, compression in Btrfs for this sort of workload produces insane write amplification (20x or more), as it forces large blocks for compression

dekobon 5y ago

If you are going to be using PostgreSQL with ZFS, be sure to set: wal_init_zero = off # zero-fill new WAL files wal_recycle = off # recycle WAL files Otherwise, you will see some intermittent performance issues.

traceroute66 5y ago

Regarding the general point about PostgreSQL and ZFS it might be noted that the community are not too keen on it (mostly from a performance perspective) as per recent mailing list thread[1]. [1]https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOHuZH8CJsKGZw7DEVtJPf0s_oPEvUKwFewHcDQFCUnutcA9GYIkOrRfFAKRsyj0ZFN9ECAbLm-NYPR8jMxTl-RU-U3hA%3D%40protonmail.ch https://www.postgresql.org/message-id/flat/49ijds_99IK_4iDOH...