Hacker News Reader

Comment by tmikaeld | Hacker News Reader

Lot's of what-if-s in this write-up - a bit of a tinkerers ball - would have loved more benchmarks with graphs to show the differences.

Interesting to see a 20% performance increase due to blocksize being 16k instead of 8k.

View on HN

Not a Postgres expert, but a MS SQL one: performance of the storage in well configured servers, after the warm up, is the least concern for perf tuning. In query tuning physical reads are a flag for bad configuration, logical reads are the first and foremost indicator and a 20% increase in storage performance brings less than 1% in total server performance. This is because if your query is well written and covered with indexes, reads are performed in memory and large writes are very rare.

Nonetheless, the article is interesting as a way to understand how Postgres and ZFS go together.

Completely agreed -- the reason that ZFS actually warrants tuning here is because ZFS has the ARC -- basically reads/writes are going to go through the linux page cache AND this additional ARC component, and there's a bit more leverage there than a more traditional LVM-on-xfs/ext4 stack.

Writes always have to touch disk of course to be durable so the underlying does matter. I'm not super familiar with MSSQL but does it have Direct IO? I have to admit I know almost nothing about the internals of MSSQL or the characteristics of filesystems on Windows.

There's a wealth of complexity and tunables in just how ZFS will write to disk along with how it will use and optimize ARC. Basically there's a second WAL at the disk level (the ZIL/SLOG) to contend with so there are lots of ways to do things and properties of the storage system can change.

MS SQL IO is tightly integrated with Windows, including modes (like instant file initialization) that are not accessible to user mode apps. That makes it 'kind of Direct IO' on Windows, I am not familiar with the Linux port but I don't have any expectations there.

MS SQL configuration and tuning is less and less about storage and more and more about server parameters (most defaults are coming from the SQL version that introduced it, ex. SQL 2000 almost 22 years ago), query and index tuning. The last 2 can make huge differences, many orders of magnitude, so this is where most time is spent optimizing. The server configuration part is usually a list of 10-20 parameters every DBA is changing from start almost never touch later. This makes it really easy on that part.

You're right! This article actually wasn't meant to be a benchmarking-style post, I didn't actually share my notes and numbers, because I originally started just experimenting and kind of fell down the rabbit hole. This originally started as me trying to pick a local filesystem after moving off of Ceph-for-everything -- it started as an mdraid vs LVM vs ZFS experiment.

That said, the reasons I haven't published all my notes is because:

- I only used one testing software pgbench (which is notorious for producing)

- Good evaluation needs a long term assessment of drive fragmentation which is hard to do (and literally takes time)

- Sometimes I changed multiple variables at a time, and not all the values I have in my notes are averaged across multiple runs, etc.

So in the end this post was just gathering what other much smarter people have said that I took inspiration from so it's all in one place.

Some intuition as well on why 16k can work better than 8k is in that scale15x talk -- for sequential table scans, it's almost like you're pre-faulting. A contact I've made through this research also recently suggested to me that I was only scratching the surface -- bigger record sizes should be better, as long as write amplification can be managed and fragmentation avoided (via a SLOG or more tuning).

Yea, I have run postgres/citus on zfs with petabytes of data. His analysis on record size is lacking. E.g there is a trade-off between compression vs record size. More compression means more effective iops

Please see the reddit discussion -- there's some great feedback there on this very point, and some experienced contacts actually think I didn't go far enough, better results at even more than 16k as long as you can control write amplification and fragmentation.

- https://www.reddit.com/r/zfs/comments/rjavyc/everything_ive_...

- https://www.reddit.com/r/zfs/comments/rlfhxb/why_not_txg_tim...

- https://www.reddit.com/r/PostgreSQL/comments/rlrvt1/what_hap...

Thanks will do! Also, your post would have saved me a bunch of time a few years back. Gj.

I hope we can lure more of the wizards sitting in their chairs out there to give more talks and write more content in 2022 -- there is so much knowledge on how to run these systems that hasn't been shared yet. I'm spoiled but I want more.

Reply on news.ycombinator.com