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 1p0joD-0003WZ-GT for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 13:41:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0joA-0006Gx-Ps for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 13:40:58 +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 1p0joA-0006Gh-CE for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 13:40:58 +0000 Received: from mail-qk1-x729.google.com ([2607:f8b0:4864:20::729]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p0jo7-0003jL-5F for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 13:40:57 +0000 Received: by mail-qk1-x729.google.com with SMTP id g10so1139979qkl.6 for ; Thu, 01 Dec 2022 05:40:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=M1FLOlVqCJqfgrJLC28uJFpgvKnMJ1mxw2CnJzd1E8I=; b=IN+t76aSZGwhK/skvdTa5LmaAt45EPwkNmG0Aan1/qgKo0bfGaMmhMnXGOHNNeP+4t 4UE5w456worJsmEfDEZQJtoypyjupJb25yk/h9WWany7v4Zh3GdqVh72KLCsRchkU4PQ ciZfPoj9VwIs/jtDZaX5Kkj4AHrOZShOqJRGckfGAX/vR/EQEs7lQ/I8w3d9AcBVYRpN ez63tLyZ52+VEtQt4DQKrWeLXMVwLHaI6Ed2Rn+Xqb4PbzYAqzzXww46YGEGiKJ6092o dc3cso0pSsyJMqbD+LsLpR9n8f+kgUw6ITSWTiOlM+moMZV1souk19J4bDP1rGD0bEkS muVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=M1FLOlVqCJqfgrJLC28uJFpgvKnMJ1mxw2CnJzd1E8I=; b=130MqvHejGAZ2wOdMsQ4VSkdPnCo6uWDqCWf5DQ8iH51pBTvmxnYoPIZexDoSeAICq CE0pNUoOMcnytHRqgpK4uELDdPdATZjygfvxvrfPz2knygR3lHCe1v9ssztUkAD6qEBV A4wPDDtp1MMgBJOjKT+QZtny7MIG2RgIpIyufivqR/WSYwelUyFNIxtufRtEyYqraTF0 npZRdHVnm8DhfyokuI09AlygxN0BX6BUQ3jTDO3dSWPd39MQf2MCdzuT3+Ok1/SaE5X6 mJ0sZqcm5H27VvkE9VaFLlgw8eN1iwrHjPh7RJxYL/WdKmhAgwS92LOXPJBGVsdABciG fw9w== X-Gm-Message-State: ANoB5pmJCElksm1AtZaDN94OAjXr/U/iiy8OzR+/kTwKs1VMIVAJBLOP Rgzm697krdTYV567/aY/gi0= X-Google-Smtp-Source: AA0mqf55wbbGxoHRTNCq3NQAYK/dY8ReO9E6K0TdtgGupbo1b6IUq/Mkb07ETNACsxwfrdXPMpVuwA== X-Received: by 2002:a05:620a:1398:b0:6fa:1d51:74a1 with SMTP id k24-20020a05620a139800b006fa1d5174a1mr43246440qki.688.1669902053956; Thu, 01 Dec 2022 05:40:53 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id j7-20020a05620a288700b006eee3a09ff3sm3516040qkp.69.2022.12.01.05.40.53 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 01 Dec 2022 05:40:53 -0800 (PST) Content-Type: multipart/alternative; boundary="------------IBijYcGLMPuFv8eiPbCrA0nw" Message-ID: Date: Thu, 1 Dec 2022 08:40:52 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.5.0 Subject: Re: postgres large database backup To: Michael Loftis Cc: 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> Content-Language: en-US From: Mladen Gogala 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. --------------IBijYcGLMPuFv8eiPbCrA0nw Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/22 20:41, Michael Loftis wrote: > > 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. > Hi Michael, I am not sure that such statement holds water. When a snapshot is taken, the amount of necessary I/O requests goes up dramatically. For every block that snapshot points to, it is necessary to read the block, write it to the spare location and then overwrite it, if you want to write to a block pointed by snapshot. That gives 3 I/O requests for every block written. NetApp is trying to optimize it by using 64MB blocks, but ZFS on Linux cannot do that, they have to use standard CoW because they don't have the benefit of their own hardware and OS. And the standard CoW is tripling the number of I/O requests for every write to the blocks pointed to by the snapshot, for every snapshot. CoW is a very expensive animal, with horns. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------IBijYcGLMPuFv8eiPbCrA0nw Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/30/22 20:41, Michael Loftis wrote:

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. 

Hi Michael,

I am not sure that such statement holds water. When a snapshot is taken, the amount of necessary I/O requests goes up dramatically. For every block that snapshot points to, it is necessary to read the block, write it to the spare location and then overwrite it, if you want to write to a block pointed by snapshot. That gives 3 I/O requests for every block written. NetApp is trying to optimize it by using 64MB blocks, but ZFS on Linux cannot do that, they have to use standard CoW because they don't have the benefit of their own hardware and OS. And the standard CoW is tripling the number of I/O requests for every write to the blocks pointed to by the snapshot, for every snapshot. CoW is a very expensive animal, with horns.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------IBijYcGLMPuFv8eiPbCrA0nw--