Обсуждение: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
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 havea major performance impact? Simon.
Вложения
Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
От
Justin Pryzby
Дата:
On Sat, Apr 24, 2021 at 06:27:08PM +0000, 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 myproject. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know if it wouldhave 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. -- Justin
Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
От
Christophe Pettus
Дата:
> On Apr 24, 2021, at 11:27, Simon Connah <simon.n.connah@protonmail.com> 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 myproject. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know if it wouldhave a major performance impact? This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs
On 4/24/21 9:02 PM, Christophe Pettus wrote: > > >> On Apr 24, 2021, at 11:27, Simon Connah <simon.n.connah@protonmail.com> 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 myproject. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know if it wouldhave a major performance impact? > > This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: > > https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs > That talk was ages ago, though. The general conclusions may be still valid, but maybe btrfs improved a lot - I haven't done any testing since then. Not sure about durability, but there are companies using btrfs so perhaps it's fine - not sure. Arguably, a lot of this also depends on the exact workload - the issues I saw with btrfs were with OLTP stress test, it could have performed much better with other workloads. regards Tomas
Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?
От
Rory Campbell-Lange
Дата:
On 24/04/21, Christophe Pettus (xof@thebuild.com) wrote: > > On Apr 24, 2021, at 11:27, Simon Connah <simon.n.connah@protonmail.com> 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 myproject. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know if it wouldhave a major performance impact? > > This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: > > https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs I guess btrfs and zfs on linux performance might have improved somewhat since Tomas' analysis in 2015. Personally I've been a keen personal user of btrfs for over 5 years for its snapshot support, transparent compression andbitrot detection. However I can't think of a reason to use it for a production server. It's slower than ext4 and xfs,postgresql's dumping and streaming are probably better bets than snapshots for backup, and relatively few others arelikely to be comfortable administering it. But maybe I'm missing something. Phoronix run btrfs benchmarks from time-to-time. See https://www.phoronix.com/scan.php?page=search&q=Btrfs Rory
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, April 24th, 2021 at 21:03, Rory Campbell-Lange <rory@campbell-lange.net> wrote: > On 24/04/21, Christophe Pettus (xof@thebuild.com) wrote: > > > > On Apr 24, 2021, at 11:27, Simon Connah simon.n.connah@protonmail.com wrote: > > > > > > I'm curious, really. I use btrfs as my filesystem on my home systems and am setting up a server as I near releasingmy project. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know ifit would have a major performance impact? > > > > This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: > > > > https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs > > I guess btrfs and zfs on linux performance might have improved somewhat since Tomas' analysis in 2015. > > Personally I've been a keen personal user of btrfs for over 5 years for its snapshot support, transparent compression andbitrot detection. However I can't think of a reason to use it for a production server. It's slower than ext4 and xfs,postgresql's dumping and streaming are probably better bets than snapshots for backup, and relatively few others arelikely to be comfortable administering it. But maybe I'm missing something. > > Phoronix run btrfs benchmarks from time-to-time. See https://www.phoronix.com/scan.php?page=search&q=Btrfs > > Rory I should have been more explicit in my original post. I use OpenSUSE Tumbleweed and btrfs is the default filesystem. I alsobelieve SUSE Linux Enterprise Server uses btrfs as their default which seems to indicate that at least one enterprise-gradeLinux distribution trusts btrfs for their default install. I'd love to do some performance testing myself but the only way I could do it would be inside virtual machines which wouldmake the numbers meaningless. I'll certainly do some reading on the subject and look at that presentation that was mentioned.
Вложения
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...
On Mon, Apr 26, 2021 at 3:43 PM Rory Campbell-Lange <rory@campbell-lange.net> wrote:
On 26/04/21, Marc Millas (marc.millas@mokadb.com) 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