7 ms·

The vast majority of databases currently recommend using XFS.

by west0n 3y ago

west0n 3y ago
I'm very curious if there are any databases running in production environments on Btrfs or ZFS.
- yjftsjthsd-h 3y ago
  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.
  - eru 3y ago
    Interesting. The compression options that postgres offers itself were not enough?
    - yjftsjthsd-h 3y ago
      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.
      - eru 3y ago
        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!
    - riku_iki 3y ago
      postgres allows to compress only large text and blob column values.
  - dfox 3y ago
    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).
- chasil 3y ago
  Only 12 pages. Oracle database on ZFS best practices. https://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf https://www.oracle.com/technetwork/server-storage/solaris10/...
- dilyevsky 3y ago
  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.
- pQd 3y ago
  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.
  - dilyevsky 3y ago
    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)?
londons_explore 3y ago
All modern databases do large block streaming appending writes, and small random reads, usually of just a handful of files. It ought to be easy to design a filesystem which can have pretty much zero overhead for those two operations. I'm kinda disappointed that every filesystem doesn't perform identically for the database workload. I totally understand that different file systems would do different tradeoffs affecting directory listing, tiny file creation/deletion, traversing deep directory trees, etc. But random reads of a huge file ought to perform near identically to the underlying storage medium.
- rubiquity 3y ago
  What you’re describing is basically ext4. I do know there are some proprietary databases that use raw block devices. The downside being that you also need to make your own user space utilities for inspecting and managing it.

The vast majority of databases currently recommend using XFS.

by west0n 3y ago

The vast majority of databases currently recommend using XFS.

west0n 3y ago

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

yjftsjthsd-h 3y ago
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.
- eru 3y ago
  Interesting. The compression options that postgres offers itself were not enough?
  - yjftsjthsd-h 3y ago
    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.
    - eru 3y ago
      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!
  - riku_iki 3y ago
    postgres allows to compress only large text and blob column values.
- dfox 3y ago
  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).
chasil 3y ago
Only 12 pages. Oracle database on ZFS best practices. https://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf https://www.oracle.com/technetwork/server-storage/solaris10/...
dilyevsky 3y ago
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.
pQd 3y ago
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.
- dilyevsky 3y ago
  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)?

londons_explore 3y ago

All modern databases do large block streaming appending writes, and small random reads, usually of just a handful of files. It ought to be easy to design a filesystem which can have pretty much zero overhead for those two operations. I'm kinda disappointed that every filesystem doesn't perform identically for the database workload. I totally understand that different file systems would do different tradeoffs affecting directory listing, tiny file creation/deletion, traversing deep directory trees, etc. But random reads of a huge file ought to perform near identically to the underlying storage medium.

rubiquity 3y ago
What you’re describing is basically ext4. I do know there are some proprietary databases that use raw block devices. The downside being that you also need to make your own user space utilities for inspecting and managing it.