Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0Yjo-0000J7-0y for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 01:51:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0Yjm-0006M3-R4 for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 01:51:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0Yjm-0006Lb-0l for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 01:51:42 +0000 Received: from mail-qv1-xf34.google.com ([2607:f8b0:4864:20::f34]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0Yjf-0005za-6o for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 01:51:40 +0000 Received: by mail-qv1-xf34.google.com with SMTP id d13so380532qvj.8 for ; Wed, 30 Nov 2022 17:51:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=xLrSAr4O1NsNB/d3GVyO+r2TYCi5X7sUv/W1wMWt9bg=; b=TCE7KRyxq0eG3yNdsaiuqTTxAipcIfoYzOs/rvYoCO4wg+JJ6JELRHn+rfm7vZOELH wSeaN22ZT7xurXmccvfQaEjDqBQr+e0lHEc3RrPVoF4pelefSaWhjTGBO+fM4haqb3s2 K6VIy9XCSPOnJ0RMZyt9ou8whJu96NuTpv2/gZMUF9QNGQLrfEfhaI/nqgLk4YBl1vR1 L/uo8Sqh0iSTVW2GcNH923W2Ubvpv69LWlK6gijKwRu6rAZ7r0f3c+4iQSGP+in9tTft h60W17LOIDi5jPORNfpPpMAL4MiNkmYNw5KWoF1aL3qvXn8SmwUfcGdxaoEoh0UiL0yj qEYA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=xLrSAr4O1NsNB/d3GVyO+r2TYCi5X7sUv/W1wMWt9bg=; b=OyIxrmsUk3ivnn+DVsW60mvIa/xjsTOS/9PGWH4QJn7qyjq2R9YC2YhUmF1aqX+9mr 8aRAmjiGMFMrBGdcAZJqLrs7ndIswbmHd4PoWyB7d1Heorn7gWvUHTYFRSH/+l9hZoSt cLJDxOm2LuuQVb6bRD51w+JlB19tapsUlVlrrY2P9Wnu/peSPi9EbU5EO+eEdTGFjuf+ v/Z1t43WsGHsL2+FPWeCCw0ReDYbulIaJ3jM2lGbad+nL0zHhR6a+3APmw4qwcdF1i6i QQHJgxx1jNaCFWNeY1KxjJQBbqrqR61erooYswaqZkKWQKq//IjWmCbRjEgoft+ezqJ4 jJcw== X-Gm-Message-State: ANoB5pkjjZ7/FklRmgXknjFhBJAAbYAHZlnmfomOn2yYKgHttr/iu5wl Nq0RTtc313c9Pgj0hN1LU11vWzfPY7A= X-Google-Smtp-Source: AA0mqf48Ez9+jVbbn6CCdKyYFW8NB/dY9dfTSWtkrOVuj1ZGSQMuOn22BMQnf8Jgk7DiFtI0ncqE3w== X-Received: by 2002:a05:6214:5984:b0:4c6:9390:983 with SMTP id ll4-20020a056214598400b004c693900983mr41306911qvb.65.1669859492648; Wed, 30 Nov 2022 17:51:32 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id h18-20020ac85692000000b003a5d7b54894sm1823607qta.31.2022.11.30.17.51.32 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 30 Nov 2022 17:51:32 -0800 (PST) Content-Type: multipart/alternative; boundary="------------KcRccQcC9VtpgfRHS15XocgE" Message-ID: <31e934ad-5e3e-22d4-f8b1-0fdee463eb42@gmail.com> Date: Wed, 30 Nov 2022 19:51:31 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: postgres large database backup Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9d50f118-a3dd-565a-41bd-d3056b9b90d6@gmail.com> <592a2c79-9728-e450-ad60-a0df7dd0f214@erven.at> <303273b8-6116-23b2-f26d-866e38ef1c0a@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------KcRccQcC9VtpgfRHS15XocgE Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/22 19:41, Michael Loftis wrote: > > > On Wed, Nov 30, 2022 at 18:03 Mladen Gogala wrote: > > On 11/30/22 18:19, Hannes Erven wrote: >> You could also use a filesystem that can do atomic snapshots - like ZFS. > > Uh, oh. Not so sure about that. Here is a page from the world of the > big O: https://blog.docbert.org/oracle-on-zfs/ > > However, similar can be said about ZFS. ZFS snapshots will slow down > the I/O considerably. I would definitely prefer snapshots done in > hardware and not in software. My favorite file systems, depending on > the type of disk, are F2FS and XFS. > > > ZFS snapshots don’t typically have much if  any performance impact versus > not having a snapshot (and already being on ZFS) because it’s already > doing COW style semantics. > > Postgres write performance using ZFS is difficult because it’s super > important to match up the underlying I/O sizes to the device/ZFS ashift, > the ZFS recordsize, and the DB’s page/wal page sizes though, but not > getting this right also cause performance issues without any snapshots, > because again COW. If you’re constantly breaking a record block or sector > there’s going to be a big impact. It won’t be any worse (in my own > testing) regardless of if you have snapshots or not. Snapshots on ZFS > don’t  cause any crazy write amplification by themselves (I’m not sure > they cause any extra writes at all, I’d have to do some sleuthing) > > ZFS will yes be slower than a raw disk (but that’s not an option for Pg > anyway), and may or may not be faster than a different  filesystem on a HW > RAID volume or storage array volume. It absolutely takes more > care/clue/tuning to get Pg write performance on ZFS, and ZFS does > duplicate some of Pg’s resiliency so there is duplicate work going on. I wonder what percentage of /Big Databases/ (like Op's and Vijaykumar's) are still on physical servers, as opposed to VMs connected to SANs.  Even many physical servers are connected to SANs.  (That is, of course, in the dreaded Enterprise environment.) -- Angular momentum makes the world go 'round. --------------KcRccQcC9VtpgfRHS15XocgE Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/30/22 19:41, Michael Loftis wrote:


On Wed, Nov 30, 2022 at 18:03 Mladen Gogala <gogala.mladen@gmail.com> wrote:
On 11/30/22 18:19, Hannes Erven wrote:
You could also use a filesystem that can do atomic snapshots - like ZFS.

Uh, oh. Not so sure about that. Here is a page from the world of the big O: https://blog.docbert.org/oracle-on-zfs/

However, similar can be said about ZFS. ZFS snapshots will slow down the I/O considerably. I would definitely prefer snapshots done in hardware and not in software.  My favorite file systems, depending on the type of disk, are F2FS and XFS.


ZFS snapshots don’t typically have much if  any performance impact versus not having a snapshot (and already being on ZFS) because it’s already doing COW style semantics. 

Postgres write performance using ZFS is difficult because it’s super important to match up the underlying I/O sizes to the device/ZFS ashift, the ZFS recordsize, and the DB’s page/wal page sizes though, but not getting this right also cause performance issues without any snapshots, because again COW. If you’re constantly breaking a record block or sector there’s going to be a big impact. It won’t be any worse (in my own testing) regardless of if you have snapshots or not. Snapshots on ZFS don’t  cause any crazy write amplification by themselves (I’m not sure they cause any extra writes at all, I’d have to do some sleuthing)

ZFS will yes be slower than a raw disk (but that’s not an option for Pg anyway), and may or may not be faster than a different  filesystem on a HW RAID volume or storage array volume. It absolutely takes more care/clue/tuning to get Pg write performance on ZFS, and ZFS does duplicate some of Pg’s resiliency so there is duplicate work going on.

I wonder what percentage of /Big Databases/ (like Op's and Vijaykumar's) are still on physical servers, as opposed to VMs connected to SANs.  Even many physical servers are connected to SANs.  (That is, of course, in the dreaded Enterprise environment.)

--
Angular momentum makes the world go 'round.
--------------KcRccQcC9VtpgfRHS15XocgE--