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

51–57 of 57 posts

Re: Running PostgreSQL on Compression-enabled ZFS

#51

Post by lucian1900 »

cwsteinbach wrote: post #19
kevinastone wrote: post #5

Earlier quoted context omitted.

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 aga…

Did you use instance storage? EBS? Provisioned IOPS?

There are vast differences between those three.

Re: Running PostgreSQL on Compression-enabled ZFS

#52

Post by tracker1 »

lwat wrote: post #29
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.

I had an original IBM PC XT (used) with a 10MB full height (2x today's 5.25") MFM hard drive.. it had about 3MB of available disk space and took I swear 6+ minutes to boot.

It actually ran faster double-spaced (stacker) and had nearly 12MB of available space... didn't have any problems with programs loading, surprisingly enough.. which became more of an issue when moving onto a 486.

Yeah, when your storage is so relatively slow, the CPU can run compression, you can get impressive gains in space and performance.

Re: Running PostgreSQL on Compression-enabled ZFS

#53

Post by ozgune »

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?

Please also keep in mind that this blog post focuses on a workload that is completely disk I/O bound.

In practice, at least part of your working set gets served from memory, and compression doesn't help with the pages that are already in memory.

Re: Running PostgreSQL on Compression-enabled ZFS

#54

Post by cafard »

I tried running Oracle on ZFS for a while, with fairly terrible results. A bit of examination showed that ZFS was fine for table scans but had bad performance with indexes. It may be possible to tune one's way around this, but I simply dumped ZFS in favor of Automated Storage Management.

Re: Running PostgreSQL on Compression-enabled ZFS

#55

Post by fsiefken »

yunong wrote: post #15
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-lea…

You are half right. There are no direct and current benchmarks, but following the news through the years about Ext4, Reiser4, ZFS and Btfrs (and experimenting with them) I know the latter is quite fast disk I/O wise (again this is just a hint), I listed the alternative filesystems which support transparent compression for a future benchmark or evaluation for people - like me - who think transparent compression is a nice idea for speeding up queries.

I found 2 recent Phoronix benchmarks which compare Btfrs with Ext4 and Ext4 with ZFS respectively. You can't really combine them as it seems the hardware used is different but if you use Ext4 as a rough translation key it seems ZFS on linux (which is what the OP used) is slower then Ext4 and Btfrs. Transparent compression speed would depend on cpu and is comparable.

April 18, 2013 Ext4 vs ZFS http://www.phoronix.com/scan.php?page=news_item&px=MTM1N...

February 18, 2013 Btfrs (and others) vs Ext4 http://www.phoronix.com/scan.php?page=article&item=linux...

Unreliable Mashup which gives some indication: * fs-walk 1000 files 1 mb zfs 46.20 ext4 72.50 vs 78.67 btfrs 66.37 btfrs

* fs-walk 5000 files 1 mb 4 threads zfs 25.63 files/s ext4 79.73 vs 99.60 btfrs 94.63

* fs-mark 4000 files 32 subdir 1 mb zfs 7.78 ext4 74.07 vs 78.80 btfrs 65.17

* dbench 1 client count zfs 27.29 MB/s ext4 167.29 MB/s vs 195.24 btfrs 165.37

I'm also interested in a Btfrs benchmark vs ZFS on Illumos, this way you can determine which is the best or fastest system for this specific scenario (even thought the OP used Linux).

Incremental snaphots is a nice feature for a Postgresql stack, what is the significant or as you put it 'real' difference between the CoW and snapshot functionality of Btfrs compared to ZFS? Are there things you cannot do with Btfrs in a Postgresql stack compared to ZFS?

Re: Running PostgreSQL on Compression-enabled ZFS

#56

Post by bsg75 »

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?

Performance gains will be dependent in part to the compressibility of the data being written. If highly compressible (text, sparse structures like database pages), then the performance gain can be significant. Binary data or that which does not compress as well, using the algorithms usable by ZFS, will not see as much benefit.

Re: Running PostgreSQL on Compression-enabled ZFS

#57

Post by marshray »

cwsteinbach wrote: post #49
marshray wrote: post #41

Earlier quoted context omitted.

I don't see a problem with them expressing the ratio as a decimal since it becomes a simple multiplier of the original file size 38GB x 0.3. But it's downright misleading to show the vertical axis from something other than 0.0 to 1.0 when comparing ratios. They start it at 0.2. In reality, LZJB is saving 50% of the space whereas gzip saves 70%. But a naive glance at the graph implies gzip look roughly 3 times smaller…

Author here. Believe it or not I originally had the compression ratio graph rotated 90 degrees, and had manually modified it to run from 0.00 to 1.00. Google docs for some god awful reason insists on starting at 0.2 by default. Anyway, when my colleagues reviewed a draft of this post they requested that I rotate the graph back, and in the process I forgot to reset the scale. Sorry for the confusion. It's fixed now. A…

Perhaps "file size on disk" would be an unambiguous way to put it.
Post reply on HN