RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Andrew Dunstan > Sent: Friday, September 24, 2021 16:57 > To: l...@laurent-hasson.com; Julien Rouhaud > Cc: Tom Lane ; Ranier Vilela ; > Justin Pryzby ; pgsql-performa...@postgresql.org > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and > 13.4 > > > On 9/13/21 4:36 PM, Andrew Dunstan wrote: > > On 9/13/21 11:53 AM, l...@laurent-hasson.com wrote: > >>> -Original Message- > >>> From: Andrew Dunstan > >>> Sent: Monday, September 13, 2021 11:36 > >>> To: l...@laurent-hasson.com; Julien Rouhaud > >>> Cc: Tom Lane ; Ranier Vilela > ; > >>> Justin Pryzby ; pgsql- > >>> performa...@postgresql.org > >>> Subject: Re: Big Performance drop of Exceptions in UDFs between > V11.2 > >>> and 13.4 > >>> > >>> > >>> On 9/13/21 10:32 AM, l...@laurent-hasson.com wrote: > >>> > > >>> > Hello all, > >>> > > >>> > Any further update or guidance on this issue at this time? > >>> > > >>> > >>> Wait for a new installer. Our team is working on it. As I have > >> previously > >>> advised you, please be patient. > >>> > >>> > >>> cheers > >>> > >>> > >>> andrew > >>> > >>> -- > >>> Andrew Dunstan > >>> EDB: https://www.enterprisedb.com > >> > >> > >> Hello Andrew, > >> > >> I'll be as patient as is needed and appreciate absolutely all the > >> work you are all doing. I also know V14 is just around the corner too > >> so the team is super busy > >> > >> Just looking for some super-rough ETA for some rough planning on our > end. Is this something potentially for 13.5 later this year? Or something that > may happen before the end of Sept? Or still unknown? And I understand all > is always tentative. > >> > > This is not governed at all by the Postgres release cycle. The issue > > is not with Postgres but with the version of libintl used in the > > build. I can't speak for the team, they will publish an updated > > installer when they get it done. But rest assured it's being worked > > on. I got email about it just this morning. > > > > > > EDB has now published new installers for versions later than release 11, > containing Postgres built with an earlier version of gettext that does not > exhibit the problem. Please verify that these fix the issue. If you already > have Postgres installed from our installer you should be able to upgrade > using Stackbuilder. Otherwise, you can download from our usual download > sites. > > > cheers > > > andrew > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com [Laurent Hasson] Thank you Andrew!!! I may be able to check this over the weekend. Thank you, Laurent.
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
On 9/13/21 4:36 PM, Andrew Dunstan wrote: > On 9/13/21 11:53 AM, l...@laurent-hasson.com wrote: >>> -Original Message- >>> From: Andrew Dunstan >>> Sent: Monday, September 13, 2021 11:36 >>> To: l...@laurent-hasson.com; Julien Rouhaud >>> Cc: Tom Lane ; Ranier Vilela ; >>> Justin Pryzby ; pgsql- >>> performa...@postgresql.org >>> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 >>> and 13.4 >>> >>> >>> On 9/13/21 10:32 AM, l...@laurent-hasson.com wrote: >>> > >>> > Hello all, >>> > >>> > Any further update or guidance on this issue at this time? >>> > >>> >>> Wait for a new installer. Our team is working on it. As I have >> previously >>> advised you, please be patient. >>> >>> >>> cheers >>> >>> >>> andrew >>> >>> -- >>> Andrew Dunstan >>> EDB: https://www.enterprisedb.com >> >> >> Hello Andrew, >> >> I'll be as patient as is needed and appreciate absolutely all the work you >> are all doing. I also know V14 is just around the corner too so the team is >> super busy >> >> Just looking for some super-rough ETA for some rough planning on our end. Is >> this something potentially for 13.5 later this year? Or something that may >> happen before the end of Sept? Or still unknown? And I understand all is >> always tentative. >> > This is not governed at all by the Postgres release cycle. The issue is > not with Postgres but with the version of libintl used in the build. I > can't speak for the team, they will publish an updated installer when > they get it done. But rest assured it's being worked on. I got email > about it just this morning. > > EDB has now published new installers for versions later than release 11, containing Postgres built with an earlier version of gettext that does not exhibit the problem. Please verify that these fix the issue. If you already have Postgres installed from our installer you should be able to upgrade using Stackbuilder. Otherwise, you can download from our usual download sites. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: Performance for initial copy when using pg_logical to upgrade Postgres
I'm currently doing this with a 2.2 TB database. Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non PK indexes for the largest tables. Then just set it back to indisready = true after its done and run a REINDEX CONCURRENTLY on the indexes that were disabled. Got about a transfer speed of 100GB per 50 minutes with this method with consistent results. On Fri, Sep 24, 2021 at 11:49 AM Westwood, Giles wrote: > > > > > On Fri, Sep 24, 2021 at 3:39 PM Justin Pryzby > wrote: > >> On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: >> >> Did you see this thread and its suggestions to 1) set bulk load >> parameters; >> and, 2) drop indexes and FKs ? >> >> >> https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com >> >> > I did actually but I wanted to avoid getting my hands dirty with anything > schema wise. I've found another person with another similar situation:- > > https://github.com/2ndQuadrant/pglogical/issues/325 > >
Re: Performance for initial copy when using pg_logical to upgrade Postgres
On 9/24/21 10:28 AM, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) > using pg_logical for no downtime. The problem we have is how long the > initial copy is taking for the ~500GB database. If it takes say 20days > to complete, will we need to have 20days of WAL files to start > catching up when it's complete? > > I read an earlier thread which pointed me to the tool > pglogical_create_subscriber which does a pg_basebackup to start the > initial replication but this is only going to be useful for logical > clusters on the same version. > > I had hoped that the COPY could be parallelized more by > "max_sync_workers_per_subscription" which is set to 2. However there's > only a single process:- > > postgres 1022196 6.0 24.5 588340 491564 ? Ds Sep22 193:19 > postgres: main: xxx 10.xx.xx.xx(59144) COPY > > One of the best resources I've found of real world examples are thead > on gitlabs own gitlab about their Postgres migrations. They discussed > one method that might work:- > > 1. Setup 9.6 secondary via streaming > 2. Turn physical secondary into logical secondary > 3. Shutdown and upgrade secondary to 10 > 4. Turn secondary back on. > > In which case we would only need the time required to perform the upgrade. If you're using the pglogical extension, the best way is often to create the replica as a physical replica (using pg_basebackup for example), and then using the extension's utility program pglogical_create_subscriber to convert the physical replica to a logical replica, which you then upgrade and switch over to. Of course, test it out before doing this for real. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: Performance for initial copy when using pg_logical to upgrade Postgres
On Fri, Sep 24, 2021 at 3:39 PM Justin Pryzby wrote: > On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > > Did you see this thread and its suggestions to 1) set bulk load parameters; > and, 2) drop indexes and FKs ? > > > https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com > > I did actually but I wanted to avoid getting my hands dirty with anything schema wise. I've found another person with another similar situation:- https://github.com/2ndQuadrant/pglogical/issues/325
Re: Performance for initial copy when using pg_logical to upgrade Postgres
On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) using > pg_logical for no downtime. The problem we have is how long the initial > copy is taking for the ~500GB database. If it takes say 20days to complete, > will we need to have 20days of WAL files to start catching up when it's > complete? Did you see this thread and its suggestions to 1) set bulk load parameters; and, 2) drop indexes and FKs ? https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com -- Justin
Performance for initial copy when using pg_logical to upgrade Postgres
At Orcid we're trying to upgrade our Postgres database (10 to 13) using pg_logical for no downtime. The problem we have is how long the initial copy is taking for the ~500GB database. If it takes say 20days to complete, will we need to have 20days of WAL files to start catching up when it's complete? I read an earlier thread which pointed me to the tool pglogical_create_subscriber which does a pg_basebackup to start the initial replication but this is only going to be useful for logical clusters on the same version. I had hoped that the COPY could be parallelized more by "max_sync_workers_per_subscription" which is set to 2. However there's only a single process:- postgres 1022196 6.0 24.5 588340 491564 ? Ds Sep22 193:19 postgres: main: xxx 10.xx.xx.xx(59144) COPY One of the best resources I've found of real world examples are thead on gitlabs own gitlab about their Postgres migrations. They discussed one method that might work:- 1. Setup 9.6 secondary via streaming 2. Turn physical secondary into logical secondary 3. Shutdown and upgrade secondary to 10 4. Turn secondary back on. In which case we would only need the time required to perform the upgrade. -- Giles Westwood Senior Devops Engineer, ORCID
