Hacker News Reader

Comment by west0n | Hacker News Reader

I'm very curious if there are any databases running in production environments on Btrfs or ZFS.

It wasn't a company you've heard of, but I can absolutely tell you that there are:) We really benefited from compressing our postgres databases; not only did it save space, but in some cases it actually improved throughput because data could be read and uncompressed faster than the disks were physically capable of.

Edit: Also there was a time when it was normal for databases to be running on Solaris, in which case ZFS is expected; my recollection is that Sun even marketed how great of a combination that was.

Interesting. The compression options that postgres offers itself were not enough?

It went the other way; we were using ZFS to protect against data errors, then found that it did compression as well. But looking now, the only native postgres options I see are TOAST, which seems to only work for certain data types in the database, and WAL compression (that's only existed since pg 15), so unless I've missed something I would tend to say yes it's far superior to the native options.

I mostly only looked into the options offered by MariaDB a while ago, and they seemed quite neat. I had just assumed that postgres was at least on par.

Thanks for reporting!

postgres allows to compress only large text and blob column values.

Sun marketed the combination of PostgreSQL and ZFS quite heavily. But IIRC most of the success stories they presented involved what was decidedly non-OLTP read heavy workload with somewhat ridiculously large databases. One of the case studies even involved using PostgreSQL on ZFS as an archival layer for Oracle, with the observation that what is a large Oracle database is medium-sized one for PostgreSQL (and probably even more so if it is read mostly and on ZFS).

Sunhad some recommendations for tuning PostgreSQL performance on ZFS, but the recommendations seemed weird or even wrong for OLTP on top of CoW filesystem (I assume that these recommendations were meant for the above mentioned DWH/archival scenarios).

We're using BTRFS to host PostgreSQL and MySQL replication slaves. We're snapshoting drives holding data for both every 15 minutes, 1h, 8h and 12h and keep few snapshots for each frequency.

Those replicas are not used for any workload, besides nightly consistency checks for MySQLs via pt-table-checksum to ensure we don't have data drift.

Snapshots are crash consistent. Once in a while they give us ability to very quickly inspect how data looked like few minutes or hours ago. This can be life-saver in case of fat-fingering a production data and saved us from lenghty grepping of backups when we needed to recover few records from a specific table.

Yes, I know soft deletes, audit logs - all of those could help and we do have them, but sometimes that's not enough or not feasible.

Due to it's COW nature BTRFS is far from perfect for data that changes all the time [ databases busy with writes, images of VMs with plenty of disk write activity ]. There's plenty of write amplification, but that can be solved with NVMe drives thrown on the problem.

How do you avoid heavy fragmentation caused by random writes? Do you disable COW (sounds like "no", given you snapshot)? Or autodefrag (how's performance)?

Only 12 pages. Oracle database on ZFS best practices.

https://www.oracle.com/technetwork/server-storage/solaris10/...

afaik meta (who are very large btrfs user) do not use it with mysql but do use it with rocksdb backends.

i think you can tweak it to make high random write load less painful but it generally will struggle with that.

Reply on news.ycombinator.com