#Openzfs postgres tuning recommendations how to
#Openzfs postgres tuning recommendations update

You'd want to compare the numbers on the first row and the last row to get a sense of the maximum performance gains that I could achieve. However, putting both of them together gave a 2-4x boost compared to baseline numbers. For example, I cannot explain why full_page_writes=off independently did not give that much boost, nor did an optimized PG configuration. Please do not copy these settings blindly because I am myself not clear on why/how these settings had the impact they did.

#Openzfs postgres tuning recommendations how to

This page documents how to tune ZFS + Postgres to give better performance for the tpcb-like benchmark. Please refer to ZFS 2-3x slower than EXT4 to see how ZFS defaults + Postgres defaults severely underperform EXT4 defaults + Postgres defaults (and also to know more about the system on which these benchmarks were performed).

In other words, every choice is a compromise and one has to pick according to their priorities.Tuning ZFS + Postgres to outperform EXT4 + Postgres The LVM+Btrfs+Ext4 combination performs well, but is not supported by cloud-utils and I had to do the resizing manually.And it’s complicated to setup if you do it yourself. It also uses a lot of memory which is limited on a VPS. However I would expect it to get very fragmentated with time, as all COW filesystems do. ZFS performs ok, at least on a fresh installation.But it is reasonably easy to setup and comes with useful features, such as snapshots and easy backups. Btrfs trails the other options for a database in terms of latency and throughput.

But they come with the smallest set of features compared to newer filesystems.

Ext4 and XFS are the fastest, as expected.

The only realistic benchmark is the one done on a real application in real conditions. The real performance of an application will depend on whether it is CPU bound, or network bound, or serving many files or finally the database performance. These are a lot of numbers to absorb, but first a warning: This is a small synthetic test designed to measure a specific aspect of performance, database performance. The idea is to combine the best parts of a COW and a traditional filesystem at the cost of extra complexity. Why Btrfs over LVM? It’s not as crazy as it sounds and it’s mentioned in the btrfs wiki as a possibility.

Arch Linux with the -lts kernel ( no preemption) and Ext4, XFS, Btrfs filesystems.

Arch Linux using a custom LVM + Btrfs + Ext4 for database.

Ubuntu as setup by the VPS provider ( virtio-scsi).

Arch Linux using its official cloud image (Btrfs, no compression, has metadata and system DUP, noatime, autodefrag, virtio-block).

Include connection establishing, rounded, higher is better. Tests were run using a raw preallocated image on a SSD Btrfs filesystem.

Filesystem tuned similarly to Linux but without changing primarycache setting according to.

ZFS tuned for Postgresql according to the arch wiki.

However, Btrfs and ZFS were tuned for a database as one would normally do. I used these instructions to benchmark Postgresql, it’s a basic benchmark with 10 and 100 clients. First, the tests were run locally in a VM configured close to the specs of the VPS I was planning to use. Then there is ZFS, which is used by FreeBSD for good reasons (its previous filesystem UFS is about as good as Ext2) and is also promoted by Ubuntu these days. So I was curious, not whether Btrfs would outperform Ext4 and XFS but rather if it’s a viable choice. However in that case database performance is often the limiting factor.

Weak random read/write performance when used for a database or a virtual machine hostįor development, the pros outweigh the cons and I wouldn’t mind the extra features on a web server.

#Openzfs postgres tuning recommendations update

Snapshots to easily rollback when/if an update goes wrong.I like Btrfs and I think it’s a good match for a rolling release distribution targeted at developers such as Arch (and OpenSuse and recently Fedora). There are other options of course and interestingly, the official Arch Linux cloud image uses Btrfs, with zstd compression. Most VPS hosting providers use Ext4 or XFS out of the box.