From a VM OS point of view: It's an "standard" FS (like ext4) still COW when residing in a ZVOL on top of a proxmox host with ZFS?

[H25E]

I thought that it was true, but after reading some answers in this thread now I have doubts.

The real reason why I want to know it's because of the full_page_writes parameter in postgresql DB. Setting it to off improves the performance greatly (almost x2) but for non COW FS keeps the DB in a unsafe state if sudden crash or power loss happens. So, as ZFS it's a COW FS it's recommended to set it to off in every guide in how to tune PG for ZFS. For example:

Consider the Postgres full_page_writes parameter. Tomas Vondra has written about it in the past as a necessity to prevent WAL corruption due to partial writes. The WAL is both streaming replication and crash recovery, so its integrity is of utmost importance. As a result, this is one parameter almost everyone should leave alone.


ZFS is Copy on Write (CoW). As a result, it’s not possible to have a torn page because a page can’t be partially written without reverting to the previous copy. This means we can actually turn off full_page_writes in the Postgres config. The results are some fairly startling performance gains:

$> pgbench -j 32 -c 32 -M prepared -T 10 pgbench

...
tps = 10325.200812 (including connections establishing)
tps = 10336.807218 (excluding connections establishing)


That’s nearly a 70% improvement. Due to write amplification caused by full page writes, Postgres produced 1.2GB of WAL files during a 1-minute pgbench test, but only 160MB with full page writes disabled.


To be fair, a 32-thread pgbench write test is extremely abusive and certainly not a typical usage scenario. However, ZFS just ensured our storage a much lower write load by altering one single parameter. That means the capabilities of the hardware have also been extended to higher write workloads as IO bandwidth is not being consumed by WAL traffic.

So, turning full_page_writes off inside of an EXT4 ZVOL would make PG unsafe as in an EXT4 FS, or safe like running directly on a ZFS FS?

Thanks for your time.

 

[LnxBil]

The real reason why I want to know it's because of the full_page_writes parameter in postgresql DB.

That is save on ZFS itself (can be turned off), but that is not true on ext4 on zfs. The OS is inbetween and a lot of caches are in between and things can go wrong.

So, turning full_page_writes off inside of an EXT4 ZVOL would make PG unsafe as in an EXT4 FS, or safe like running directly on a ZFS FS?

yes

 

[H25E]

yes

Yes to unsafe I supose.

That's a shame. DB on ZFS are much slower by default, and there is a list of tunnings to ZFS/DB/Linux that are recommended to improve the performance and make it near as fast or faster than on traditional FS. And full_page_writes it's one of the most important ones. Or even the most one.

Then, how to make a fast DB in a proxmox guest when the host is running on ZFS? Format the guest with ZFS too? Share a host ZFS storage through NFS? Resign yourself about using VM and place your DB in a container?

 

[LnxBil]

That's a shame. DB on ZFS are much slower by default, and there is a list of tunnings to ZFS/DB/Linux that are recommended to improve the performance and make it near as fast or faster than on traditional FS. And full_page_writes it's one of the most important ones. Or even the most one.

I know. Therefore just run it on ZFS. If you already have ZFS, you don't have fast live-migration and failover-HA, so you don't need KVM for this.

Resign yourself about using VM and place your DB in a container?

Depends on your usecase, but that is one way to go. We choose this (benchmarks for ZFS with speed improvements were faster than ext4).

 

[H25E]

Thank you so much.

Depends on your usecase, but that is one way to go.

What would be the other ways to do it? To avoid performance penalty with a ZFS host?

 

[LnxBil]

What would be the other ways to do it? To avoid performance penalty with a ZFS host?

I cannot think of anything else for a ZFS host.
Using ZFS-on-ZFS is not faster, ZFS in the VM but not on the host would got, but you need a fast storage (e.g. non-thin LVM)