3

I'm just preparing a VM machine (running onver Proxmox) to run a postgresql 9.6 over a Ubuntu 16.04 LTS. This postgres would be used to handle Jira/FisheEye/Confluence databases for a small company. Usually we are a few user at same time, so we not need to tune it for extreme performance/scalability.

Well, the case is that we have using BTRFS on the servers as help us to handle the problem of adding extra space to a VM when is necessary, plus we enable lzo compression. Also, we use btrok to handle backups of BTRFS subvolumes to another machine.

I have doubts if could be a good idea use BTRFS to handle the postgresql DB files as we would be very helpful on the case that we need to expand the virtual hard disk space, but I read about postgresql bad performance over BTRFS (on special if the datacow is not disabled.

Anyone have experience with this situation ?

2

2 Answers 2

7

The general answer: bad idea. You can read a few details about it here. Long story short is that the COW mechanism of BTRFS will cause performance inconsistencies for normal OLTP workload

The better answer: in certain cases I would use it. Why and how:

  • You can only notice real performance differences if you really get to stress the filesystem and run really heavy work for the FS in that DB. Unlikely to happen for JIRA and Confluence with normal workloads (assuming you don't work in a company with thousands of developers etc), especially if you tune & configure their caching properly. Also, considering you want to enable compression, it doesn't sound like IO performance is your main concern :).
  • Considering the previous bullet, manageability, good integration into your current tools and environment, and existing knowledge about the technologies you are using should definitely trump the fact that, in much higher workloads, other filesystems provide better performance.
  • I would also consider tuning everything properly to compensate: run on flash memory, do proper data alignment (physical <-> controllers <-> partitioning <-> FS <-> DB), do proper FS (BTRFS requires more maintenance than your throw-in-and-forget-about-it ext4), and DB maintenance and tuning.

I hope it helps.

Note: A user suggested that COW can be disabled for BTRFS for specific volumes/folders and I disregard the fact. Indeed, it can be disabled, but if you do that, why would you still use BTRFS? - Because you can still use COW on rest of the filesystem and all the other cool features (like snapshots and stuff) without performance hit for the virtualbox and postgres? Sure, it doesn't make sense for pure DB server to use BTRFS and disable COW. But for general purpose machine/server? It allows you to use all the cool features (RAID1, ...) without performance hit. So win-win in my eyes.

5
  • I read that page. But it's a few years old, so there is time where BTRFS driver could has been improved. I was asking for more actual information about it. We have enabled compression, not only to get less hard drive usage, but because we thought that enabling compression improves I/O (with the price of using a bit more of CPU) as it need to write/read less data to the hard drive. Also, we aren't using SSD drives. We have a RAID 1+0 on the Proxmox server.
    – Zardoz89
    Jul 27, 2017 at 8:48
  • And yep, our company is small, so usually we have only a few users working at same time over Jira/Confluence/FishEye.
    – Zardoz89
    Jul 27, 2017 at 8:54
  • 1
    The problem with I/O and databases is not throughput but IOPS. Compression or not, same IOPS. Jul 27, 2017 at 8:57
  • What about data integrity: orange.kaosy.org/2018/12/28/… Aug 19, 2020 at 11:24
  • Since posting that answer I created several DB clusters on both BTRFS and ZFS and everything worked smoothly. Aug 26, 2020 at 18:51
3

A user suggested that COW can be disabled for BTRFS for specific volumes/folders and I disregard the fact. Indeed, it can be disabled, but if you do that, why would you still use BTRFS?

Even when COW is disabled for specific files/folders they still have snapshot capabilities. After a snapshot is taken, the next write will be COW, then return to in-place writing. Pretty easy backup solution for running databases, given you do it when the FS is not busy. You still lose checksumming for all in-place writes, of course.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .