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

11–20 of 57 posts

Re: Running PostgreSQL on Compression-enabled ZFS

#11

Post by mbell »

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.

Re: Running PostgreSQL on Compression-enabled ZFS

#12

Post by bsg75 »

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.

Placing limits on ARC (25% for me currently) limits that effect.

Re: Running PostgreSQL on Compression-enabled ZFS

#13

Post by laumars »

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 virtual machine. ZFS is better suited for raw disks than virtual devices (again, just my anacdotal evidence. I've never ran benchmarks myself).

Re: Running PostgreSQL on Compression-enabled ZFS

#14

Post by GalacticDomin8r »

This isn't the first time benchmarks like this have been done and these results are consistent with the earlier ones.

It shouldn't surprise most people that enabling transparent compression gives these benefits. Why you ask? Well what is the largest bottleneck in a system? Disk IO - by far. So all ZFS is doing is transferring workload to a subsystem you likely have plenty of(CPU) from one that you have the least of(Disk IO/latency)

Re: Running PostgreSQL on Compression-enabled ZFS

#15

Post by yunong »

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…

Do you have any benchmarks to support your claim? Statements such as "Btrfs ... might currently be a better alternative" without benchmarks are worthless. Anyway -- I'd be interested to see benchmarks of Btrfs on GNU/Linux vs ZFS on illumos -- I suspect that ZFS "might currently be a better alternative".

Simply ratcheting off a set of features and stating that Btrfs is "better" is dubious at best, and perhaps mis-leading. As the OP stated in his blog post, ZFS has a rich feature set -- which we find invaluable in our own postgres stack -- features such as incremental snapshots, a real copy on write filesystem, etc.

Re: Running PostgreSQL on Compression-enabled ZFS

#16

Post by GalacticDomin8r »

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.

ext3 does suck for certain workloads, one of them being large scale db's. And by suck, I mean dangerous. Unless you really want to set barrier on the fs and watch your IO plummet to 45 record player speeds.

Re: Running PostgreSQL on Compression-enabled ZFS

#17

Post by gngeal »

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…

Reiser4 is in a weird place...

...and so is Hans. :-) (Sorry, I couldn't resist :-))

Re: Running PostgreSQL on Compression-enabled ZFS

#18

Post by mattbillenstein »

cwsteinbach wrote: post #9
mfenniak wrote: post #4

Earlier quoted context omitted.

old-gregg makes a great observation here. The addition of a ZFS benchmark without-compression is needed to isolate the compression as a factor in the speedup. That aside, I thought this was a wonderful article with non-intuitive findings. Very interesting, CirtusDB [edit: er, CitusDB]. :-)

old-gregg's point is valid. Since we didn't benchmark ZFS without compression we can't say for sure how much of the performance improvement is attributable to compression vs. just ZFS. As far as AWS goes, we have noticed ephemeral disks connected to the same instance can exhibit fairly large performance differences, and attempted to control for that in our tests by reusing the same disk for each test run.

I always question benchmarks on ec2 because of "noisy neighbor" effects - maybe that's in the noise, but someone trying to replicate your results would perhaps see significantly different results depending whether their VM landed on a busy node or not... Tag this one #ymmv

+1 on testing uncompressed ZFS

I did see a blog post about MySQL with similar results (at least compression was a significant win) some time ago - disks are so slow compared to what throughput modern CPUs are capable of on these sorts of compression algorithms.

Re: Running PostgreSQL on Compression-enabled ZFS

#19

Post by cwsteinbach »

kevinastone wrote: post #5
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.

You're also testing on a c1.xlarge that gives you excess CPU compared to I/O, so it's potentially biasing your results.

While I can't claim that we logged CPU load while running these tests, I can say that I watched the output of top and iotop and that the CPU load was relatively light. It's also worth pointing out that Amazon describes the I/O performance of c1.xlarge instances as "high". We also considered using an hs1.8xlarge "High Storage" instance for these tests, but eventually decided that we were more interested in testing against conventional disks as opposed to SSDs.

Re: Running PostgreSQL on Compression-enabled ZFS

#20

Post by cwsteinbach »

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 "unstable".
Post reply on HN