From: Daniel Gustafsson <daniel@yesql.se>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_C823BBA6-599B-494C-B135-7D9463BB232E"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: More performance improvements for pg_dump in binary upgrade mode
Message-Id: <8F1F1E1D-D17B-4B33-B014-EDBCD15F3F0B@yesql.se>
Date: Wed, 15 May 2024 22:15:13 +0200
Cc: Nathan Bossart <nathandbossart@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/8F1F1E1D-D17B-4B33-B014-EDBCD15F3F0B%40yesql.se>
Precedence: bulk


--Apple-Mail=_C823BBA6-599B-494C-B135-7D9463BB232E
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Prompted by an off-list bugreport of pg_upgrade hanging (which turned =
out to be
slow enough to be perceived to hang) for large schemas I had a look at =
pg_dump
performance during --binary-upgrade mode today.  My initial take was to =
write
more or less exactly what Nathan did in [0], only to realize that it was =
a)
already proposed and b) I had even reviewed it.  Doh.

The next attempt was to reduce more per-object queries from binary =
upgrade, and
the typarray lookup binary_upgrade_set_type_oids_by_type_oid seemed like =
a good
candidate for a cache lookup.  Since already cache type TypeInfo =
objects, if we
add typarray to TypeInfo we can use the existing lookup code.

As a baseline, pg_dump dumps a synthetic workload of 10,000 (empty) =
relations
with a width of 1-10 columns:

$ time ./bin/pg_dump --schema-only --quote-all-identifiers =
--format=3Dcustom \
  --file a postgres > /dev/null

real	0m1.256s
user	0m0.273s
sys	0m0.059s

The same dump in binary upgrade mode runs significantly slower:

$ time ./bin/pg_dump --schema-only --quote-all-identifiers =
--binary-upgrade \
  --format=3Dcustom --file a postgres > /dev/null

real	1m9.921s
user	0m0.782s
sys	0m0.436s

With the typarray caching from the patch attached here added:

$ time ./bin/pg_dump --schema-only --quote-all-identifiers =
--binary-upgrade \
  --format=3Dcustom --file b postgres > /dev/null

real	0m45.210s
user	0m0.655s
sys	0m0.299s

With the typarray caching from the patch attached here added *and* =
Nathan's
patch from [0] added:

$ time ./bin/pg_dump --schema-only --quote-all-identifiers =
--binary-upgrade \
  --format=3Dcustom --file a postgres > /dev/null

real	0m1.566s
user	0m0.309s
sys	0m0.080s

The combination of these patches thus puts binary uphrade mode almost on =
par
with a plain dump, which has the potential to make upgrades of large =
schemas
faster.  Parallel-parking this patch with Nathan's in the July CF, just =
wanted
to type it up while it was fresh in my mind.

--
Daniel Gustafsson

[0] https://commitfest.postgresql.org/48/4936/


--Apple-Mail=_C823BBA6-599B-494C-B135-7D9463BB232E
Content-Disposition: attachment;
	filename=0001-Cache-typarray-for-fast-lookups-in-binary-upgrade-mo.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="0001-Cache-typarray-for-fast-lookups-in-binary-upgrade-mo.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=208dbb3c1caaa0adefafab8483dd7827ce45157a20=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20Daniel=20Gustafsson=20=
<dgustafsson@postgresql.org>=0ADate:=20Wed,=2015=20May=202024=2022:03:22=20=
+0200=0ASubject:=20[PATCH]=20Cache=20typarray=20for=20fast=20lookups=20=
in=20binary=20upgrade=20mode=0A=0AWhen=20upgrading=20a=20large=20schema=20=
it=20adds=20significant=20overhead=20to=20perform=0Aindividual=20catalog=20=
lookups=20per=20relation=20in=20order=20to=20retrieve=20Oid=20for=0A=
preserving=20Oid=20calls.=20This=20instead=20adds=20the=20typarray=20to=20=
the=20TypeInfo=0Acache=20which=20then=20allows=20for=20fast=20lookups=20=
using=20the=20existing=20API.=0A---=0A=20src/bin/pg_dump/pg_dump.c=20|=20=
20=20++++++++------------=0A=20src/bin/pg_dump/pg_dump.h=20|=20=201=20+=0A=
=202=20files=20changed,=209=20insertions(+),=2012=20deletions(-)=0A=0A=
diff=20--git=20a/src/bin/pg_dump/pg_dump.c=20b/src/bin/pg_dump/pg_dump.c=0A=
index=20ac920f64c7..5fb4453712=20100644=0A---=20=
a/src/bin/pg_dump/pg_dump.c=0A+++=20b/src/bin/pg_dump/pg_dump.c=0A@@=20=
-5304,23=20+5304,15=20@@=20=
binary_upgrade_set_type_oids_by_type_oid(Archive=20*fout,=0A=20=09Oid=09=09=
=09pg_type_array_oid;=0A=20=09Oid=09=09=09pg_type_multirange_oid;=0A=20=09=
Oid=09=09=09pg_type_multirange_array_oid;=0A+=09TypeInfo=20=20=20*tinfo;=0A=
=20=0A=20=09appendPQExpBufferStr(upgrade_buffer,=20"\n--=20For=20binary=20=
upgrade,=20must=20preserve=20pg_type=20oid\n");=0A=20=09=
appendPQExpBuffer(upgrade_buffer,=0A=20=09=09=09=09=09=20=20"SELECT=20=
pg_catalog.binary_upgrade_set_next_pg_type_oid('%u'::pg_catalog.oid);\n\n"=
,=0A=20=09=09=09=09=09=20=20pg_type_oid);=0A=20=0A-=09=
appendPQExpBuffer(upgrade_query,=0A-=09=09=09=09=09=20=20"SELECT=20=
typarray=20"=0A-=09=09=09=09=09=20=20"FROM=20pg_catalog.pg_type=20"=0A-=09=
=09=09=09=09=20=20"WHERE=20oid=20=3D=20'%u'::pg_catalog.oid;",=0A-=09=09=09=
=09=09=20=20pg_type_oid);=0A-=0A-=09res=20=3D=20=
ExecuteSqlQueryForSingleRow(fout,=20upgrade_query->data);=0A-=0A-=09=
pg_type_array_oid=20=3D=20atooid(PQgetvalue(res,=200,=20PQfnumber(res,=20=
"typarray")));=0A-=0A-=09PQclear(res);=0A+=09tinfo=20=3D=20=
findTypeByOid(pg_type_oid);=0A+=09pg_type_array_oid=20=3D=20=
tinfo->typarray;=0A=20=0A=20=09if=20(!OidIsValid(pg_type_array_oid)=20&&=20=
force_array_type)=0A=20=09=09pg_type_array_oid=20=3D=20=
get_next_possible_free_pg_type_oid(fout,=20upgrade_query);=0A@@=20=
-5785,6=20+5777,7=20@@=20getTypes(Archive=20*fout,=20int=20*numTypes)=0A=20=
=09int=09=09=09i_typtype;=0A=20=09int=09=09=09i_typisdefined;=0A=20=09=
int=09=09=09i_isarray;=0A+=09int=09=09=09i_typarray;=0A=20=0A=20=09/*=0A=20=
=09=20*=20we=20include=20even=20the=20built-in=20types=20because=20those=20=
may=20be=20used=20as=20array=0A@@=20-5805,7=20+5798,7=20@@=20=
getTypes(Archive=20*fout,=20int=20*numTypes)=0A=20=09=09=09=09=09=09=20=
"typnamespace,=20typacl,=20"=0A=20=09=09=09=09=09=09=20"acldefault('T',=20=
typowner)=20AS=20acldefault,=20"=0A=20=09=09=09=09=09=09=20"typowner,=20=
"=0A-=09=09=09=09=09=09=20"typelem,=20typrelid,=20"=0A+=09=09=09=09=09=09=
=20"typelem,=20typrelid,=20typarray,=20"=0A=20=09=09=09=09=09=09=20"CASE=20=
WHEN=20typrelid=20=3D=200=20THEN=20'=20'::\"char\"=20"=0A=20=09=09=09=09=09=
=09=20"ELSE=20(SELECT=20relkind=20FROM=20pg_class=20WHERE=20oid=20=3D=20=
typrelid)=20END=20AS=20typrelkind,=20"=0A=20=09=09=09=09=09=09=20=
"typtype,=20typisdefined,=20"=0A@@=20-5832,6=20+5825,7=20@@=20=
getTypes(Archive=20*fout,=20int=20*numTypes)=0A=20=09i_typtype=20=3D=20=
PQfnumber(res,=20"typtype");=0A=20=09i_typisdefined=20=3D=20=
PQfnumber(res,=20"typisdefined");=0A=20=09i_isarray=20=3D=20=
PQfnumber(res,=20"isarray");=0A+=09i_typarray=20=3D=20PQfnumber(res,=20=
"typarray");=0A=20=0A=20=09for=20(i=20=3D=200;=20i=20<=20ntups;=20i++)=0A=
=20=09{=0A@@=20-5864,6=20+5858,8=20@@=20getTypes(Archive=20*fout,=20int=20=
*numTypes)=0A=20=09=09else=0A=20=09=09=09tyinfo[i].isArray=20=3D=20=
false;=0A=20=0A+=09=09tyinfo[i].typarray=20=3D=20atooid(PQgetvalue(res,=20=
i,=20i_typarray));=0A+=0A=20=09=09if=20(tyinfo[i].typtype=20=3D=3D=20=
TYPTYPE_MULTIRANGE)=0A=20=09=09=09tyinfo[i].isMultirange=20=3D=20true;=0A=
=20=09=09else=0Adiff=20--git=20a/src/bin/pg_dump/pg_dump.h=20=
b/src/bin/pg_dump/pg_dump.h=0Aindex=20f518a1e6d2..135219bf4a=20100644=0A=
---=20a/src/bin/pg_dump/pg_dump.h=0A+++=20b/src/bin/pg_dump/pg_dump.h=0A=
@@=20-200,6=20+200,7=20@@=20typedef=20struct=20_typeInfo=0A=20=09const=20=
char=20*rolname;=0A=20=09Oid=09=09=09typelem;=0A=20=09Oid=09=09=09=
typrelid;=0A+=09Oid=09=09=09typarray;=0A=20=09char=09=09typrelkind;=09=09=
/*=20'r',=20'v',=20'c',=20etc=20*/=0A=20=09char=09=09typtype;=09=09/*=20=
'b',=20'c',=20etc=20*/=0A=20=09bool=09=09isArray;=09=09/*=20true=20if=20=
auto-generated=20array=20type=20*/=0A--=20=0A2.39.3=20(Apple=20Git-146)=0A=
=0A=

--Apple-Mail=_C823BBA6-599B-494C-B135-7D9463BB232E--