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 1lJGdl-0006XC-70 for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Mar 2021 14:13:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lJGdj-00030m-Dg for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Mar 2021 14:13:43 +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 1lJGdj-00030f-43 for pgsql-hackers@lists.postgresql.org; Mon, 08 Mar 2021 14:13:43 +0000 Received: from mail-ua1-x936.google.com ([2607:f8b0:4864:20::936]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lJGdg-00088R-HP for pgsql-hackers@postgresql.org; Mon, 08 Mar 2021 14:13:42 +0000 Received: by mail-ua1-x936.google.com with SMTP id m26so1900964uah.13 for ; Mon, 08 Mar 2021 06:13:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=taXRLchl5DljXaY8c5aMWWr4owoY1mGheM2InzHgZ1o=; b=LDpmluBKNnArp8U13Hl+GFJYTM7pMCV9aSbN76TO1GfzH1Wmz0AcT2LkASgAmS4kgb u2W6ymdY4MRgvHgGdBNcz2MJFiKCyors9wJHh3GhB9KRNOJr1ffpaypasYZ8jj6K0244 e6OUFZDeABJJ+DZGxCENNcWuhAPgiSH3vH7BbOjRk+QdGIfKImwSOpcem9nbMjqWhKzN Kz4WZ/wR18icUfeLRLH0XpG8WwyOjtz3o4zsCMsG7/buG0+AAQbpF+Q0vGSjCYlMB/gU GCMHuDIJ9WYtxcTTaBlbqjqjDxVPar/x7X7XjJWcIwQTP9q/0BpAoux3DdruXl6PRHG8 MlyA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=taXRLchl5DljXaY8c5aMWWr4owoY1mGheM2InzHgZ1o=; b=ru8s4N4QLKzSzCDaboMFznbFD8AEnd9WA2CerAD5qJd4o/6mqFZwfHbLbQS542kPZG YmM9QcoicFzgOz7ZDhcX7YzGYbZs+RuXMGB9B3Of8tyuypzz4l6YB+ZK5FkLAlM79nx6 OeyiDF65YL8DFU1IePNlEUPw7HsWHtYyuyZZmZ2qtSaIk3SBg/xrrkUkXGib0RU0eMXa HJMPaykkZfJGNXDZOR9c171KG8IB5/l5dbRSBt9XWVoCtd2sizjdHjdEoD7Vkq6kt2Ne 9Oxc6jHqLPUC1HDUxUUTQ+2uEuZkJZsTWYMH6EbS5RB8kjOgXLB95QnS18oJk81f7ouK 9UWA== X-Gm-Message-State: AOAM531Adp540XB0H4laPvxJTGZC6Xqha67Gp3iC/xC4NOE72z0ueZXk hnd2aCogTk0Lxbh6gRc9LEXPJD0XxtSHz6vLtGk= X-Google-Smtp-Source: ABdhPJz2Djvyx73u4lyf2loPjMxjv7CJZ7D4sWPVeqClHIwA9s9948J6serFARwZlBjKBJi5jrJAc9ksR8EZEJdzSOQ= X-Received: by 2002:ab0:7656:: with SMTP id s22mr11395326uaq.67.1615212818480; Mon, 08 Mar 2021 06:13:38 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Robins Tharakan Date: Tue, 9 Mar 2021 01:13:02 +1100 Message-ID: Subject: Re: pg_upgrade failing for 200+ million Large Objects To: Magnus Hagander Cc: Peter Eisentraut , "pgsql-hackers@postgresql.org" Content-Type: multipart/alternative; boundary="00000000000048410605bd070a07" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000048410605bd070a07 Content-Type: text/plain; charset="UTF-8" Hi Magnus, On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > AFAICT at a quick check, pg_dump in binary upgrade mode emits one lo_create() and one ALTER ... OWNER TO for each large object - so with > 500M large objects that would be a billion statements, and thus a > billion xids. And without checking, I'm fairly sure it doesn't load in > a single transaction... > Your assumptions are pretty much correct. The issue isn't with pg_upgrade itself. During pg_restore, each Large Object (and separately each ALTER LARGE OBJECT OWNER TO) consumes an XID each. For background, that's the reason the v9.5 production instance I was reviewing, was unable to process more than 73 Million large objects since each object required a CREATE + ALTER. (To clarify, 73 million = (2^31 - 2 billion magic constant - 1 Million wraparound protection) / 2) Without looking, I would guess it's the schema reload using > pg_dump/pg_restore and not actually pg_upgrade itself. This is a known > issue in pg_dump/pg_restore. And if that is the case -- perhaps just > running all of those in a single transaction would be a better choice? > One could argue it's still not a proper fix, because we'd still have a > huge memory usage etc, but it would then only burn 1 xid instead of > 500M... > (I hope I am not missing something but) When I tried to force pg_restore to use a single transaction (by hacking pg_upgrade's pg_restore call to use --single-transaction), it too failed owing to being unable to lock so many objects in a single transaction. This still seems to just fix the symptoms and not the actual problem. > I agree that the patch doesn't address the root-cause, but it did get the upgrade to complete on a test-setup. Do you think that (instead of all objects) batching multiple Large Objects in a single transaction (and allowing the caller to size that batch via command line) would be a good / acceptable idea here? Please take a look at your email configuration -- all your emails are > lacking both References and In-reply-to headers. > Thanks for highlighting the cause here. Hopefully switching mail clients would help. - Robins Tharakan --00000000000048410605bd070a07 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
<= font face=3D"monospace">Hi Magnus,

= On Mon, 8 Mar 2021 at 23:34, Magnus Hagander <magnus@hagander.net> wrote:
AFAICT at a quick check, pg_dump in bi= nary upgrade mode emits one
lo_create() and one ALTER ... OWNER TO for each large object - so with
500M large objects that would be a billion statements, and thus a
billion xids. And without checking, I'm fairly sure it doesn't load= in
a single transaction...

Your assumptions are pretty much correct.

The issue isn= 't=C2=A0with pg_upgrade itself. During pg_restore, each Large Object (a= nd separately each ALTER LARGE OBJECT OWNER TO) consumes an XID each. For b= ackground, that's the reason the=C2=A0v9.5 production instance I was re= viewing, was unable to process more than 73 Million large objects since eac= h object required a CREATE + ALTER. (To clarify, 73 million =3D (2^31 - 2 b= illion magic constant - 1 Million wraparound protection) / 2)
<= /div>

<= div class=3D"gmail_quote">
Without= looking, I would guess it's the schema reload using
pg_dump/pg_restore and not actually pg_upgrade itself. This is a known
issue in pg_dump/pg_restore. And if that is the case -- perhaps just
running all of those in a single transaction would be a better choice?
One could argue it's still not a proper fix, because we'd still hav= e a
huge memory usage etc, but it would then only burn 1 xid instead of
500M...
(= I hope I am not missing something but) When I tried to force pg_restore to use a single transac= tion (by hacking pg_upgrade's pg_restore call to use --single-transacti= on), it too failed owing to being unable to lock so many objects in a singl= e transaction.

<= div>
This still seems to just fix the symptoms and not the actu= al problem.

I agree that the patch d= oesn't address the root-cause, but it did get the upgrade to complete o= n a test-setup. Do you think that (instead of all objects) batching multipl= e Large Objects in a single transaction (and allowing the caller to size th= at batch via command line) would be a good / acceptable idea here?

Please= take a look at your email configuration -- all your emails are
lacking both References and In-reply-to headers.

Thanks for highlighting the cause here. Hopefully switching = mail clients would help.
-
Robins Tharakan
--00000000000048410605bd070a07--