public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 2/3] Allow composite types in bootstrap 5+ messages / 4 participants [nested] [flat]
* [PATCH 2/3] Allow composite types in bootstrap @ 2020-11-17 15:28 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Justin Pryzby @ 2020-11-17 15:28 UTC (permalink / raw) --- src/backend/bootstrap/bootstrap.c | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 18eb62ca47..e4fc75ab84 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -916,6 +916,7 @@ gettype(char *type) { if (Typ != NIL) { + static bool did_reread PG_USED_FOR_ASSERTS_ONLY = false; /* Already reread pg_types */ ListCell *lc; foreach (lc, Typ) @@ -927,6 +928,33 @@ gettype(char *type) return app->am_oid; } } + + /* + * The type wasn't known; check again to handle composite + * types, added since first populating the array. + */ + + /* + * Once all the types are populated and we handled composite + * types, shouldn't need to do that again. + */ + Assert(!did_reread); + did_reread = true; + + list_free_deep(Typ); + Typ = NULL; + populate_typ_array(); + + /* Need to avoid infinite recursion... */ + foreach (lc, Typ) + { + struct typmap *app = lfirst(lc); + if (strncmp(NameStr(app->am_typ.typname), type, NAMEDATALEN) == 0) + { + Ap = app; + return app->am_oid; + } + } } else { -- 2.26.2 --------------EED78D4A67CFF327F6B880D2 Content-Type: text/x-patch; charset=UTF-8; name="0003-Extended-statistics-on-expressions-20210122b.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0003-Extended-statistics-on-expressions-20210122b.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects @ 2025-04-07 21:25 Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2025-04-07 21:25 UTC (permalink / raw) To: Hannu Krosing <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hannu Krosing <[email protected]> writes: > I have now met a not insignificant number of cases where pg_upgrade > performance is really bad when the database has a large number of > Large Objects. What version are you testing? We did some work in that area in the v17 cycle (a45c78e32). regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects @ 2025-04-08 04:52 Michael Paquier <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Michael Paquier @ 2025-04-08 04:52 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote: > What version are you testing? We did some work in that area in the > v17 cycle (a45c78e32). I am puzzled by the target version used here, as well. If there is more that can be improved, v19 would be the version to consider for future improvements at this stage. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects @ 2025-04-08 07:15 Hannu Krosing <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Hannu Krosing @ 2025-04-08 07:15 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Hackers <[email protected]>; Nathan Bossart <[email protected]> I was testing on version 17 On Tue, Apr 8, 2025 at 6:52 AM Michael Paquier <[email protected]> wrote: > > On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote: > > What version are you testing? We did some work in that area in the > > v17 cycle (a45c78e32). > > I am puzzled by the target version used here, as well. I was testing on version 17 Here is how you can easily test too (as --binary-upgrade does not dump the actual data it is ok for the test to not put anything there) hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5433 lodb hannuk@db01-c1a:~/work/lo-testing$ psql -p 5433 lodb psql (17.4 (Ubuntu 17.4-1.pgdg22.04+2)) Type "help" for help. lodb=# insert into pg_largeobject_metadata(oid, lomowner) SELECT i, 16384 FROM generate_series(1, 100_000_000) g(i); INSERT 0 100000000 Time: 162414.216 ms (02:42.414) lodb=# \q hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t pg_largeobject_metadata -p 5433 lodb | gzip > pg_largeobject_metadata.data.gz real 0m22.094s user 0m20.741s sys 0m2.085s hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t pg_largeobject_metadata --format=custom -p 5433 lodb -f pg_largeobject_metadata.dump real 0m20.226s user 0m18.068s sys 0m0.824s > If there is > more that can be improved, v19 would be the version to consider for > future improvements at this stage. If the internal format has changed in 16 the correct way would be to go through the data-only dump of pg_largeobject_metadata in all cases. Even for the 100M case where you get the restore in 2 minutes instead of 100 minutes hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5434 lodb hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434 --exit-on-error --transaction-size=1000 --dbname lodb pg_largeobject_metadata.dump real 2m2.277s user 0m2.594s sys 0m0.549s And even in case of the user-visible format change in acl format it is most likely that changing the visible format using some regexp magic, or even a dedicated function, would still me much faster than creating all the LOs though creation commands. ------ The commands I used to do the pg_upgrade-like test were hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file=lodb100m.dump -p 5433 lodb real 1m58.241s user 0m35.229s sys 0m17.854s hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434 --exit-on-error --transaction-size=1000 --dbname lodb lodb100m.dump real 100m54.878s user 3m23.885s sys 20m33.761s (I left out the --verbose part that pg_upgrade also sets as I did not want to get 100M lines of "large object created " messages ) also the postgres server at -p 5434 needs to be started with -b flag to accept the loading a dump from --binary-upgrade. In Debian/Ubuntu this can be directly passed to pg_ctlcluster as follows sudo pg_ctlcluster 17 target -o -b ---- Hannu > -- > Michael ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects @ 2025-04-08 07:26 Hannu Krosing <[email protected]> parent: Hannu Krosing <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Hannu Krosing @ 2025-04-08 07:26 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Hackers <[email protected]>; Nathan Bossart <[email protected]> Looked like a bit illogical order on re-reading it so I want to make clear that the pg_upgrade-like test showing 100min for 100 million LOs is at the end of last message and the proposed solution is at the beginning On Tue, Apr 8, 2025 at 9:15 AM Hannu Krosing <[email protected]> wrote: > > I was testing on version 17 > > > On Tue, Apr 8, 2025 at 6:52 AM Michael Paquier <[email protected]> wrote: > > > > On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote: > > > What version are you testing? We did some work in that area in the > > > v17 cycle (a45c78e32). > > > > I am puzzled by the target version used here, as well. > > I was testing on version 17 > > Here is how you can easily test too (as --binary-upgrade does not dump > the actual data it is ok for the test to not put anything there) > > hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5433 lodb > hannuk@db01-c1a:~/work/lo-testing$ psql -p 5433 lodb > psql (17.4 (Ubuntu 17.4-1.pgdg22.04+2)) > Type "help" for help. > > lodb=# insert into pg_largeobject_metadata(oid, lomowner) SELECT i, > 16384 FROM generate_series(1, 100_000_000) g(i); > INSERT 0 100000000 > Time: 162414.216 ms (02:42.414) > lodb=# > \q > > hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t > pg_largeobject_metadata -p 5433 lodb | gzip > > pg_largeobject_metadata.data.gz > real 0m22.094s > user 0m20.741s > sys 0m2.085s > > hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t > pg_largeobject_metadata --format=custom -p 5433 lodb -f > pg_largeobject_metadata.dump > real 0m20.226s > user 0m18.068s > sys 0m0.824s > > > If there is > > more that can be improved, v19 would be the version to consider for > > future improvements at this stage. > > If the internal format has changed in 16 the correct way would be to > go through the data-only dump of pg_largeobject_metadata in all cases. > Even for the 100M case where you get the restore in 2 minutes instead > of 100 minutes > > hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5434 lodb > hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434 > --exit-on-error --transaction-size=1000 --dbname lodb > pg_largeobject_metadata.dump > > real 2m2.277s > user 0m2.594s > sys 0m0.549s > > And even in case of the user-visible format change in acl format it is > most likely that changing the visible format using some regexp magic, > or even a dedicated function, would still me much faster than creating > all the LOs though creation commands. > > ------ > The commands I used to do the pg_upgrade-like test were > > hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --schema-only > --quote-all-identifiers --binary-upgrade --format=custom > --file=lodb100m.dump -p 5433 lodb > real 1m58.241s > user 0m35.229s > sys 0m17.854s > > hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434 > --exit-on-error --transaction-size=1000 --dbname lodb lodb100m.dump > real 100m54.878s > user 3m23.885s > sys 20m33.761s > > (I left out the --verbose part that pg_upgrade also sets as I did not > want to get 100M lines of "large object created " messages ) > > also the postgres server at -p 5434 needs to be started with -b flag > to accept the loading a dump from --binary-upgrade. In Debian/Ubuntu > this can be directly passed to pg_ctlcluster as follows > > sudo pg_ctlcluster 17 target -o -b > > ---- > Hannu > > > > > > -- > > Michael ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-08 07:26 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-17 15:28 [PATCH 2/3] Allow composite types in bootstrap Justin Pryzby <[email protected]> 2025-04-07 21:25 Re: Horribly slow pg_upgrade performance with many Large Objects Tom Lane <[email protected]> 2025-04-08 04:52 ` Re: Horribly slow pg_upgrade performance with many Large Objects Michael Paquier <[email protected]> 2025-04-08 07:15 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]> 2025-04-08 07:26 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox