Message-ID: <ba92a3e5ad5b44b0ead518bb3ea53040229aad42.camel@cybertec.at>
Subject: Re: Minimize checkpointer and walwriter io during pg_restore
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron Johnson <ronljohnsonjr@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 04 Jun 2024 17:21:08 +0200
In-Reply-To: 
 <CANzqJaCnU9WWWTaC7fMY0d8_acXZR_gnSMvDbKhSwjfBqwUfeQ@mail.gmail.com>
References: 
	<CANzqJaCnU9WWWTaC7fMY0d8_acXZR_gnSMvDbKhSwjfBqwUfeQ@mail.gmail.com>
Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual;
 keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk
 bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC
 GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b
 lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1
 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ
 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59
 +AnW5l0HsQcY9ntFL/zEBOaonjdJf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ba92a3e5ad5b44b0ead518bb3ea53040229aad42.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-06-04 at 09:14 -0400, Ron Johnson wrote:
> Currently, when doing a pg_restore with 24 threads, I see this when using=
 iotop:
> checkpointer 90+ MB/s
> walwriter 45+ MB/s
>=20
> Is there any way to minimize it more than I already have, or is that just=
 how much work needs to be done when restoring the database?
>=20
> Here's the current configuration (which will be reset after the post-rest=
ore ANALYZE):
> declare -gi SharedBuffs=3D32
> declare -gi MaintMem=3D3 # so the oom=C2=A0killer doesn't kill it
> declare -gi CheckPoint=3D30
> declare -gi MaxWalSize=3D36
> declare -gi WalBuffs=3D64
> pg_ctl restart -wt$TimeOut -mfast \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg_hba_maintmode.co=
nf" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_statement=3Dnone" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_temp_files=3D100kB" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_checkpoints=3Don" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_min_duration_statement=3D120000" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${SharedBuffs}GB" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance_work_mem=3D${MaintMem}GB" =
\
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3Doff" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Doff" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D${CheckPoint}min"=
 \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D${MaxWalSize}GB" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_level=3Dminimal" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_senders=3D0" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_buffers=3D${WalBuffs}MB" \
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c autovacuum=3Doff"=C2=A0

I would not set "fsync" to off.  It won't make a measurable difference for
pg_restore, and it may break your database.  The same applies to "full_page=
_writes".

What might make a difference is if you use the --single-transaction option =
of
pg_restore.  If "wal_level =3D minimal", that could skip writing a substant=
ial
amount of WAL.

Yours,
Laurenz Albe