Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Running PostgreSQL on Compression-enabled ZFS

citusdata.com

Post reply on HN
Print view View original

21–30 of 57 posts

Re: Running PostgreSQL on Compression-enabled ZFS

#21

Post by cwsteinbach »

atoponce wrote: post #7

I'm curious of the rest of the architecture. Each benchmark needs to be tested separately, as ZFS is likely caching the reads in the ARC. We also need a benchmark of ZFS without compression enabled. However, we're not showing how bad ext3 is, but that the end result still shows the stellar performance, compression or not.

> as ZFS is likely caching the reads in the ARC

Each of the seven queries we used in our benchmark required a sequential scan of the 32GB dataset. It's unlikely that the ARC had any impact on the results since the EC2 instance had only 7GiB of memory.

Re: Running PostgreSQL on Compression-enabled ZFS

#22

Post by iso8859-1 »

laumars wrote: post #13
fsiefken wrote: post #10

The Btfrs and Reiser4 filesystems also support transparent compression and might currently be a better alternative to increase Postgresql query speed. Btfrs supports gzip, LZO, LZ4 and Snappy and is in the mainline linux kernel, Reiser4 is still maintained and available as a patch on Linux 3.8.5 (latest is 3.8.8) and supports LZO and gzip (alternatively there are also the embedded NAND flash medium compatible filesys…

Reiser4 is in a weird place after the conviction of Hans. I'm not sure I'd want to trust a production system on it. And I've been less than impressed with BtrFS on the test systems I've ran it on (though I'm aware there's others who swear by it - I'm only talking about my experiences). ZFS is a fantastic file system, but I can't help wondering if part of the issue is the fact that the benchmarking was conducted on a…

Btrfs is unstable too. Source: https://news.ycombinator.com/item?id=5460449

Re: Running PostgreSQL on Compression-enabled ZFS

#23

Post by iso8859-1 »

atoponce wrote: post #7

I'm curious of the rest of the architecture. Each benchmark needs to be tested separately, as ZFS is likely caching the reads in the ARC. We also need a benchmark of ZFS without compression enabled. However, we're not showing how bad ext3 is, but that the end result still shows the stellar performance, compression or not.

What is the reason for using ext3 over ext4?

Re: Running PostgreSQL on Compression-enabled ZFS

#24

Post by richardkmichael »

cwsteinbach wrote: post #20
fsiefken wrote: post #10

The Btfrs and Reiser4 filesystems also support transparent compression and might currently be a better alternative to increase Postgresql query speed. Btfrs supports gzip, LZO, LZ4 and Snappy and is in the mainline linux kernel, Reiser4 is still maintained and available as a patch on Linux 3.8.5 (latest is 3.8.8) and supports LZO and gzip (alternatively there are also the embedded NAND flash medium compatible filesys…

I wasn't aware that Reiser4 supported compression. Thanks for pointing that out. As for why we chose to use ZFS instead of Btrfs, we feel that ZFS is closer to being in a state where an enterprise customer would be comfortable deploying it in production. This is due to the fact that ZFS has been in development for over a decade with many Solaris sites already using it in production, and Btrfs is still marked as "unst…

EDIT: I realize you said "near" and "closer" to production ready, but I think it's worth mentioning --

No FUD intended, but I don't consider ZFS on Linux production ready. Wanting to use ZFS, I recently started regularly reading their GitHub issues.

There are deadlocks and un-importable pools in certain situations (hard-links being one: think rsync). I would not want production boxes in the same predicaments experienced by several bug reporters. Moreover, applying debug and hot-fix (hopefully) kernel patches and the associated downtime in production is a no-go for me.

Mind you, the project leads are very responsive and it's making great strides.

In addition, I believe the Linux implementation currently lacks the L2ARC (which can make ZFS really fly, caching to SSDs).

However, I would absolutely run ZFS on Illumos or Solaris; for the stability and article-mentioned compression benefits.

Re: Running PostgreSQL on Compression-enabled ZFS

#25

Post by trotsky »

old-gregg wrote: post #2

Can this simply be an artifact of terrible disk I/O on AWS or overall difference between ZFS/ext3? Do you think the results would have been similar if you were to use no-compression-ZFS instead of ext3 on a proper database hardware? Basically trying to figure out if the low performance of uncompressed dataset is specific to AWS/ext3. Thanks.

Using compression with zfs on solaris derived platforms serving as a san/nas backend for vsphere appears to speed up every workload backed on rotating storage. Well, not if vmdks use guest full disk encryption, but that's understandable.

Re: Running PostgreSQL on Compression-enabled ZFS

#26

Post by Nelson69 »

fsiefken wrote: post #10

The Btfrs and Reiser4 filesystems also support transparent compression and might currently be a better alternative to increase Postgresql query speed. Btfrs supports gzip, LZO, LZ4 and Snappy and is in the mainline linux kernel, Reiser4 is still maintained and available as a patch on Linux 3.8.5 (latest is 3.8.8) and supports LZO and gzip (alternatively there are also the embedded NAND flash medium compatible filesys…

btrfs has the features and it's destined or ordained to be a mainline mainstream Linux filesystem (trust me on this, if you're doing serious work, you want to be on main street) but there are some cases which aren't terribly uncommon where it has some performance problems.

Hard to say if it's better than some sort of linux with zfs Frankenstein system. Would love for Oracle to make ZFS more linux friendly though, seems like a win for everybody and there are tons of users that would love for it to happen.

I don't know if I'd call Reiserfs "maintained" and I couldn't recommend it to anyone. If it is maintained seriously, my recommendation would be to rename it.

Re: Running PostgreSQL on Compression-enabled ZFS

#27

Post by nemothekid »

If I'm reading this right, with ZFS compression enabled I am seeing 1/3rd disk usage and 3x increase of speeds in query times just from switching the filesystem. Stats like that make me very skeptical. Does this mean that I can get a 3x increase in speed while cutting my disk space down by a third just by switching to ZFS? If so, why isn't everyone doing this?

Re: Running PostgreSQL on Compression-enabled ZFS

#28

Post by wiredfool »

mbell wrote: post #11
old-gregg wrote: post #2

Can this simply be an artifact of terrible disk I/O on AWS or overall difference between ZFS/ext3? Do you think the results would have been similar if you were to use no-compression-ZFS instead of ext3 on a proper database hardware? Basically trying to figure out if the low performance of uncompressed dataset is specific to AWS/ext3. Thanks.

Another issue is that ZFS is extremely aggressive with caching data in ram (L1ARC). That can eat up memory you'd rather give to the database heap and also tends to skew benchmarks.

Yes, but postgres' design in that area actually helps. Postgres relies on the OS caching the data tables for the most part. There is some caching in shared buffers, but generally that's not a huge portion of the memory of your db system. (10-25%, and not more than a few gigs)

Re: Running PostgreSQL on Compression-enabled ZFS

#29

Post by lwat »

nemothekid wrote: post #27

If I'm reading this right, with ZFS compression enabled I am seeing 1/3rd disk usage and 3x increase of speeds in query times just from switching the filesystem. Stats like that make me very skeptical. Does this mean that I can get a 3x increase in speed while cutting my disk space down by a third just by switching to ZFS? If so, why isn't everyone doing this?

The way I make sense of this is that you need fewer (slow) disk reads to get the same amount of data into RAM, so that might explain the speedup?

I agree that it sounds too good to be true though.

Re: Running PostgreSQL on Compression-enabled ZFS

#30

Post by rscale »

mbell wrote: post #11
old-gregg wrote: post #2

Can this simply be an artifact of terrible disk I/O on AWS or overall difference between ZFS/ext3? Do you think the results would have been similar if you were to use no-compression-ZFS instead of ext3 on a proper database hardware? Basically trying to figure out if the low performance of uncompressed dataset is specific to AWS/ext3. Thanks.

Another issue is that ZFS is extremely aggressive with caching data in ram (L1ARC). That can eat up memory you'd rather give to the database heap and also tends to skew benchmarks.

I run Postgres on ZFS, and simply limit the amount of memory dedicated to the ARC.
Post reply on HN