Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
Hi Bruce, everybody, compression ? I am currently working on a project to move an oracle db to postgres. The db is 15 TB. with Oracle compression it does use 5 TB of disk space. If we cannot compress the whole thing, the project loses its economic base. (added 10 TB for prod, 10TB for pre-prod, 10TB for testing dev, ...) we do test zfs, and we will give a try to btrfs. any suggestion ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Sat, Apr 24, 2021 at 9:00 PM Bruce Momjian wrote: > On Sat, Apr 24, 2021 at 01:45:48PM -0500, Justin Pryzby wrote: > > On Sat, Apr 24, 2021 at 06:27:08PM +, Simon Connah wrote: > > > I'm curious, really. I use btrfs as my filesystem on my home systems > and am setting up a server as I near releasing my project. I planned to use > btrfs on the server, but it got me thinking about PostgreSQL 13. Does > anyone know if it would have a major performance impact? > > > > Is there some reason the question is specific to postgres13 , or did you > just > > say that because it's your development target for your project. > > > > I think it almost certainly depends more on your project than on > postgres 13. > > > > It may well be that performance is better under btrfs, maybe due to > compression > > or COW. But you'd have to test what you're doing to find out - and > maybe write > > up the results. > > > > Also, it's very possible that btfs performs better for (say) report > queries, > > but worse for data loading. Maybe you care more about reporting, but > that's > > not true for everyone. > > My question is whether btrfs is reliable enough or write-durable enough > for Postgres. I would need a pretty good reason to not use ext4 or xfs. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > > > >
Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
On 26/04/21, Marc Millas ([email protected]) wrote: > compression ? > > I am currently working on a project to move an oracle db to postgres. > The db is 15 TB. > with Oracle compression it does use 5 TB of disk space. > > If we cannot compress the whole thing, the project loses its economic base. > (added 10 TB for prod, 10TB for pre-prod, 10TB for testing dev, ...) > > we do test zfs, and we will give a try to btrfs. I've been using btrfs with lzo compression for several years on my personal laptop and some non-critical backup systems with no trouble. (In fact btrfs has helped us recover from some disk failures really well.) While I run postgresql on my machine it is for light testing purposes so I wouldn't want to comment on its suitability for production. There are some differences reported here between lzo and zlib compression performance for Postgresql: https://sudonull.com/post/96976-PostgreSQL-and-btrfs-elephant-on-an-oil-diet zstd compression support for btrfs is reported on by Phoronix here: https://www.phoronix.com/scan.php?page=article&item=btrfs-zstd-compress&num=2 The compression page of the btrfs wiki is here: https://btrfs.wiki.kernel.org/index.php/Compression You might want to armor yourself for possible problems by reading the Debian btrfs wiki page: https://wiki.debian.org/Btrfs If you test your workload please let us know your results. Rory
Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
Interesting ! the debian blog saying that " but only users who are interested in debugging future bugs should use transparent compression at this time" makes me feel its less urgent to test this option... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Mon, Apr 26, 2021 at 3:43 PM Rory Campbell-Lange wrote: > On 26/04/21, Marc Millas ([email protected]) wrote: > > compression ? > > > > I am currently working on a project to move an oracle db to postgres. > > The db is 15 TB. > > with Oracle compression it does use 5 TB of disk space. > > > > If we cannot compress the whole thing, the project loses its economic > base. > > (added 10 TB for prod, 10TB for pre-prod, 10TB for testing dev, ...) > > > > we do test zfs, and we will give a try to btrfs. > > I've been using btrfs with lzo compression for several years on my > personal laptop and some non-critical backup systems with no trouble. > (In fact btrfs has helped us recover from some disk failures really > well.) While I run postgresql on my machine it is for light testing > purposes so I wouldn't want to comment on its suitability for > production. > > There are some differences reported here between lzo and zlib > compression performance for Postgresql: > > https://sudonull.com/post/96976-PostgreSQL-and-btrfs-elephant-on-an-oil-diet > > zstd compression support for btrfs is reported on by Phoronix here: > > https://www.phoronix.com/scan.php?page=article&item=btrfs-zstd-compress&num=2 > > The compression page of the btrfs wiki is here: > https://btrfs.wiki.kernel.org/index.php/Compression > > You might want to armor yourself for possible problems by reading the > Debian btrfs wiki page: https://wiki.debian.org/Btrfs > > If you test your workload please let us know your results. > > Rory > >
