Running PostgreSQL on Compression-enabled ZFS
citusdata.com
Running PostgreSQL on Compression-enabled ZFS
1–10 of 57 posts
Re: Running PostgreSQL on Compression-enabled ZFS
#2Can 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.
Re: Running PostgreSQL on Compression-enabled ZFS
#3I wouldn't recommend doing benchmarking on a virtual server.
You have no idea how busy the real server is, (noisy neighbors, etc), so it's impossible to have comparable results from benchmark to benchmark.
Re: Running PostgreSQL on Compression-enabled ZFS
#4Can 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.
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]. :-)
Re: Running PostgreSQL on Compression-enabled ZFS
#5Can 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.
Re: Running PostgreSQL on Compression-enabled ZFS
#6Can 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.
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]. :-)
Citus
Re: Running PostgreSQL on Compression-enabled ZFS
#7I'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.
Re: Running PostgreSQL on Compression-enabled ZFS
#8Can 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.
I have seen similar advantages comparing XFS to ZFS+compression on a local server (Centos 6.3, ZFS on Linux 0.61).
Using a 2 disk striped volume for PostgreSQL 9.2, I get an average of 2.5X compression (as reported by ZFS), and a 1.5 to 2X time reduction in database restores (single threaded or 8 jobs in parallel).
Given this development box has relatively slow 7200 RPM disks, the tradeoff of more CPU time for less disk transfer makes sense.
Edit: My use case is an OLAP server. I can't state how the tradeoffs affect OLTP performance.
Re: Running PostgreSQL on Compression-enabled ZFS
#9Can 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.
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.
Re: Running PostgreSQL on Compression-enabled ZFS
#10The 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 filesystems F2FS and UBIFS which both improve on the JFFS2 filesystem and it's transparent compression).
For I/O bound queries SSD drives (in your preferred raid configuration) also will speed up the system. Btfrs has built-in support for TRIM SSD already, Reiser4 TRIM/SSD support is being discussed among the remaining developers.