Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURb6-0034zl-T0 for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 13:55:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sURb4-00Giad-8s for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 13:55:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURb3-00GiaU-Ot for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 13:55:02 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURaz-000CTD-BX for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 13:54:59 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id AC722807C3 for ; Thu, 18 Jul 2024 14:32:43 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 38D488E0736 for ; Thu, 18 Jul 2024 09:54:49 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721310888-3614-3610/6/41; Thu, 18 Jul 2024 13:54:48 +0000 Content-Type: multipart/alternative; boundary=------------RXa98QSq0nkBXFgZsVcQXUew Message-Id: Date: Thu, 18 Jul 2024 09:54:07 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: filesystem full during vacuum - space recovery issues To: pgsql-admin@lists.postgresql.org References: <38b20a9f-ef8a-486a-bb7d-f7a7be20f98d@talentstack.to> Content-Language: en-CA From: Thomas Simpson In-Reply-To: X-NSSLTD-Archiving: Added to store 1 as 91334 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------RXa98QSq0nkBXFgZsVcQXUew Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Thanks Ron for the suggestions - I applied some of the settings which=20 helped throughput a little bit but were not an ideal solution for me -=20 let me explain. Due to the size, I do not have the option to use the directory mode (or=20 anything that uses disk space) for dump as that creates multiple=20 directories (hence why it can do multiple jobs).=C2=A0 I do not have the=20 several hundred TB of space to hold the output and there is no practical=20 way to get it, especially for a transient reload. I have my original server plus my replica; as the replica also applied=20 the WALs, it too filled up and went down.=C2=A0 I've basically recreated = this=20 as a primary server and am using a pipeline to dump from the original=20 into this as I know that has enough space for the final loaded database=20 and should have space left over from the clean rebuild (whereas the=20 original server still has space exhausted due to the leftover files). Incidentally, this state is also why going to a backup is not helpful=20 either as the restore and then re-apply the WALs would just end up=20 filling the disk and recreating the original problem. Even with the improved throughput, current calculations are pointing to=20 almost 30 days to recreate the database through dump and reload which is=20 a pretty horrible state to be in. I think this is perhaps an area of improvement - especially as larger=20 PostgreSQL databases become more common, I'm not the only person who=20 could face this issue. Perhaps an additional dumpall mode that generates multiple output pipes=20 (I'm piping via netcat to the other server) - it would need to combine=20 with a multiple listening streams too and some degree of=20 ordering/feedback to get to the essentially serialized output from the=20 current dumpall.=C2=A0 But this feels like PostgreSQL expert developer = territory. Thanks Tom On 17-Jul-2024 09:49, Ron Johnson wrote: > On Wed, Jul 17, 2024 at 9:26=E2=80=AFAM Thomas Simpson wrote: ---8<--snip,snip---8<--- > That would, of course, depend on what you're currently doing.=C2=A0=20 > pg_dumpall of a Big Database is certainly suboptimal compared to=20 > "pg_dump -Fd --jobs=3D24". > > This is what I run (which I got mostly from a databasesoup.com=20 > blog post) on the target instance=C2=A0before= =20 > doing "pg_restore -Fd --jobs=3D24": > declare -i CheckPoint=3D30 > declare -i SharedBuffs=3D32 > declare -i MaintMem=3D3 > declare -i MaxWalSize=3D36 > declare -i 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.= conf" \ > =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}mi= n" \ > =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" > > After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only = --jobs=3D24: > pg_ctl stop -wt$TimeOut && pg_ctl start -wt$TimeOut > > Of course, these parameter values were for *my*=C2=A0hardware. > > My backups were in progress when all the issues happened, so > they're not such a good starting point and I'd actually prefer the > clean reload since this DB has been through multiple upgrades > (without reloads) until now so I know it's not especially clean.=C2= =A0 > The size has always prevented the full reload before but the > database is relatively low traffic now so I can afford some time > to reload, but ideally not 10 days. > >> Yours, >> Laurenz Albe > --------------RXa98QSq0nkBXFgZsVcQXUew Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Thanks Ron for the suggestions - I applied some of the settings which helped throughput a little bit but were not an ideal solution for me - let me explain.

Due to the size, I do not have the option to use the directory mode (or anything that uses disk space) for dump as that creates multiple directories (hence why it can do multiple jobs).=C2=A0 I = do not have the several hundred TB of space to hold the output and there is no practical way to get it, especially for a transient reload.

I have my original server plus my replica; as the replica also applied the WALs, it too filled up and went down.=C2=A0 I've = basically recreated this as a primary server and am using a pipeline to dump from the original into this as I know that has enough space for the final loaded database and should have space left over from the clean rebuild (whereas the original server still has space exhausted due to the leftover files).

Incidentally, this state is also why going to a backup is not helpful either as the restore and then re-apply the WALs would just end up filling the disk and recreating the original problem.

Even with the improved throughput, current calculations are pointing to almost 30 days to recreate the database through dump and reload which is a pretty horrible state to be in.

I think this is perhaps an area of improvement - especially as larger PostgreSQL databases become more common, I'm not the only person who could face this issue.

Perhaps an additional dumpall mode that generates multiple output pipes (I'm piping via netcat to the other server) - it would need to combine with a multiple listening streams too and some degree of ordering/feedback to get to the essentially serialized output from the current dumpall.=C2=A0 But this feels like PostgreSQL = expert developer territory.

Thanks

Tom


On 17-Jul-2024 09:49, Ron Johnson wrote:
On Wed, Jul 17, 2024 at 9:26=E2=80=AFAM Thomas = Simpson <ts@talentstack.to> wrote:
---8<--snip,snip---8<---
That would, of course, depend on what you're currently doing.=C2=A0 pg_dumpall of a Big Database is certainly suboptimal compared to "pg_dump -Fd --jobs=3D24".

This is what I run (which I got mostly from a d= atabasesoup.com blog post) on the target instance=C2=A0before doing "pg_resto= re -Fd --jobs=3D24":
declare -i CheckPoint=3D30
declare -i SharedBuffs=3D32
declare -i MaintMem=3D3
declare -i MaxWalSize=3D36
declare -i WalBuffs=3D64
pg_ctl restart -wt$TimeOut -mfast \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg_hb= a_maintmode.conf" \
=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_stateme= nt=3D120000" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${Share= dBuffs}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${C= heckPoint}min" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D${MaxWalS= ize}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<= br>

After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only --jobs=3D24:
pg_ctl stop -wt$TimeOut &&= ; pg_ctl start -wt$TimeOut

Of course, these parameter values were for my=C2=A0= hardware.

My backups were in progress when all the issues happened, so they're not such a good starting point and I'd actually prefer the clean reload since this DB has been through multiple upgrades (without reloads) until now so I know it's not especially clean.=C2=A0 The size = has always prevented the full reload before but the database is relatively low traffic now so I can afford some time to reload, but ideally not 10 days.

Yours,
Laurenz Albe
--------------RXa98QSq0nkBXFgZsVcQXUew--