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 1lpAyg-0000Z1-W1 for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Jun 2021 14:39:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lpAyf-000531-IN for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Jun 2021 14:39:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lpAyf-00051W-5T for pgsql-hackers@lists.postgresql.org; Fri, 04 Jun 2021 14:39:13 +0000 Received: from mail-io1-xd2f.google.com ([2607:f8b0:4864:20::d2f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lpAya-00085a-BS for pgsql-hackers@postgresql.org; Fri, 04 Jun 2021 14:39:12 +0000 Received: by mail-io1-xd2f.google.com with SMTP id r4so10273522iol.6 for ; Fri, 04 Jun 2021 07:39:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=ODBL5f1IOgXII+yOJdxI5LDqIqKjHRfgdiX2D6/DPTo=; b=SAtLaJQvKT7FdRBX7N0wx8lfIBS/9IyI1uI7X/UogPz0lDcZoR7MyuETfTCMY0Sbzo RfUl6FWLNLRSu2ay6UWEto2KfiZf87KEaEp2LiCf2zwFP2QQQS3xt2tN9ADm7qroDJkG 37m8voyH9ID3Ry6OUKNdvDiIxyInHoDQf3eNcdiqO3+4FC2ZUN8YWNuKVsWiXcpy6rv9 3OwHk1QQ/V5NBOhhnkO//tN1eULWqZAOWsJsBzbDCfhSuIBcDYU3hBihHUaspFLR96E6 QBmU8vwbVL42iY4G3iXF49gpXeqFT7oBSjr+BLm/vaQE/TIygKPBsTnHg4dtY0m6R0E7 XEdA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=ODBL5f1IOgXII+yOJdxI5LDqIqKjHRfgdiX2D6/DPTo=; b=goMUqXX78Ns64CEpljHFu2XzmpXJWtOzOMkCVAEJOREOQbCOYaJB9y3Vz9u6sO/LWT nLhgkPamSXTH/F0azEXr8rDjw9LpyzMEVcJyFysjmDNGpdUTE2WL6Lvnu6L1sU8OWxxp 70p64podxZ2bss2r1bUHAaU/bua2WTfpJtzFG5qnTEy2+GBMSMLVWcJsiHUU0jA78Hwm fcVViyhBzy2uj7iQV7lWGZTufdkqknm7yLoK6ItwGlal9N9DS/5qWCZ6GESOVFaeMjSs bF7faPwOTKgB0j8uhTWMhp4stmbySwGkBWPhFzpdaDLCgRX4XCKZVlwX0JaTADir+srP g0Bw== X-Gm-Message-State: AOAM533Q8qstT7Xiur8Q1B+GP381U1ffevK+ULzq6/OFyxMC9qK/j6xR ZdVQEMfi6cRWJoYovM5GqqZRnw== X-Google-Smtp-Source: ABdhPJx4caFiH2WUliDXk7lR1BqFZgH1JmKcSC/Xp9tJxQTGaFq/I4utF4E8AP/oPKwb2j29yKjK6A== X-Received: by 2002:a5e:930d:: with SMTP id k13mr4123834iom.61.1622817546457; Fri, 04 Jun 2021 07:39:06 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id u8sm3536735ilg.43.2021.06.04.07.39.05 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 04 Jun 2021 07:39:05 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id CB6488006B1; Fri, 4 Jun 2021 09:39:04 -0500 (CDT) Date: Fri, 4 Jun 2021 09:39:04 -0500 From: Justin Pryzby To: Michael Paquier Cc: Thomas Munro , David Steele , Fujii Masao , Bruce Momjian , Paul Guo , Tom Lane , Michael Brown , pgsql-hackers@postgresql.org Subject: Re: fdatasync performance problem with large number of DB files Message-ID: <20210604143904.GQ14099@telsasoft.com> References: <7716fafe-fcab-3faf-c337-d1e8c6ae6191@pgmasters.net> <20210526001359.GE3676@telsasoft.com> <20210529192321.GM2082@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Jun 04, 2021 at 04:24:02PM +0900, Michael Paquier wrote: > On Sat, May 29, 2021 at 02:23:21PM -0500, Justin Pryzby wrote: > > On Tue, May 25, 2021 at 07:13:59PM -0500, Justin Pryzby wrote: > >> On Sat, Mar 20, 2021 at 12:16:27PM +1300, Thomas Munro wrote: > >> > > > + { > >> > > > + {"recovery_init_sync_method", PGC_POSTMASTER, ERROR_HANDLING_OPTIONS, > >> > > > + gettext_noop("Sets the method for synchronizing the data directory before crash recovery."), > >> > > > + }, > >> > >> Is there any reason why this can't be PGC_SIGHUP ? > > > > I can't see any reason why this is nontrivial. > > I think that we had better let recovery_init_sync_method as > PGC_POSTMASTER, to stay on the safe side. SyncDataDirectory() only > gets called now in the backend code by the startup process after a > crash at the beginning of recovery, so switching to PGC_SIGHUP would > have zero effect to begin with. Now, let's not forget that > SyncDataDirectory() is a published API, and if anything exterior were > to call that, it does not seem right to me to make that its behavior > reloadable at will. You said switching to SIGHUP "would have zero effect"; but, actually it allows an admin who's DB took a long time in recovery/startup to change the parameter without shutting down the service. This mitigates the downtime if it crashes again. I think that's at least 50% of how this feature might end up being used. It might be "safer" if fsync were PGC_POSTMASTER, but it's allowed to change at runtime that parameter, which is much more widely applicable. I've already mentioned restart_after_crash, and remove_temp_files_after_crash. -- Justin