Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Laurenz Albe]

On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> We have recently done the migration from Oracle Database Version 12C to Azure
> PostgreSQL PaaS instance version 11.4 and most of the application 
> functionality
> testing has been over and tested successfully 
>  
> However, there is 1 process at application level which is taking approx. 10 
> mins
> in PostgreSQL and in oracle it is taking only 3 mins.
>  
> After investigating further we identified that process which is executed from
> application end contains 500 to 600 no of short SQL queries into the database.
> We tried to run the few queries individually on database and they are taking
> less than sec in Postgres Database to execute, and we noticed that in Oracle
> taking half of the time as is taking in PostgreSQL. for ex . in oracle same
> select statement is taking 300 millisecond and in PostgreSQL it is taking
> approx. 600 millisecond which over increases the execution of the process.
>  
> Oracle Database are hosted on ON- Prem DC with dedicated application server on
> OnPrem and same for PostgreSQL.

How can a database hosted with Microsoft be on your permises?

Apart from all other things, compare the network latency.  If a single request
results in 500 database queries, you will be paying 1000 times the network
latency per request.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

RE: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Kumar, Mukesh]

Hi Albe , 

I mean to say that , we have everything hosted on Oracle is on On - Prem DC and 
everything hosted on Azure PostgreSQL on Microsoft Azure Cloud like Application 
Server and PaaS Instance,

Please revert in case of any query

Thanks and Regards, 
Mukesh Kumar

-Original Message-
From: Laurenz Albe  
Sent: Wednesday, April 13, 2022 2:04 PM
To: Kumar, Mukesh ; pgsql-performa...@postgresql.org; 
MUKESH KUMAR 
Subject: Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> We have recently done the migration from Oracle Database Version 12C 
> to Azure PostgreSQL PaaS instance version 11.4 and most of the 
> application functionality testing has been over and tested 
> successfully
>  
> However, there is 1 process at application level which is taking 
> approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins.
>  
> After investigating further we identified that process which is 
> executed from application end contains 500 to 600 no of short SQL queries 
> into the database.
> We tried to run the few queries individually on database and they are 
> taking less than sec in Postgres Database to execute, and we noticed 
> that in Oracle taking half of the time as is taking in PostgreSQL. for 
> ex . in oracle same select statement is taking 300 millisecond and in 
> PostgreSQL it is taking approx. 600 millisecond which over increases the 
> execution of the process.
>  
> Oracle Database are hosted on ON- Prem DC with dedicated application 
> server on OnPrem and same for PostgreSQL.

How can a database hosted with Microsoft be on your permises?

Apart from all other things, compare the network latency.  If a single request 
results in 500 database queries, you will be paying 1000 times the network 
latency per request.

Yours,
Laurenz Albe
--
Cybertec | 
https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Na6zYPRuqYDPkzxkeKGFLkUk5TtVvDNeBotFXA-DpoSA8sO0hMkFnUll1op05OICvy74bGAGSzuTfzBWN-4PfzlYkK0vvQ$
 

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[andrew cooke]

On Wed, Apr 13, 2022 at 10:34:24AM +0200, Laurenz Albe wrote:
> On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> > We have recently done the migration from Oracle Database Version 12C to 
> > Azure
> > PostgreSQL PaaS instance version 11.4 and most of the application 
> > functionality
> > testing has been over and tested successfully 
> >  
> > However, there is 1 process at application level which is taking approx. 10 
> > mins
> > in PostgreSQL and in oracle it is taking only 3 mins.
> >  
> > After investigating further we identified that process which is executed 
> > from
> > application end contains 500 to 600 no of short SQL queries into the 
> > database.
> > We tried to run the few queries individually on database and they are taking
> > less than sec in Postgres Database to execute, and we noticed that in Oracle
> > taking half of the time as is taking in PostgreSQL. for ex . in oracle same
> > select statement is taking 300 millisecond and in PostgreSQL it is taking
> > approx. 600 millisecond which over increases the execution of the process.
> >  
> > Oracle Database are hosted on ON- Prem DC with dedicated application server 
> > on
> > OnPrem and same for PostgreSQL.
> 
> How can a database hosted with Microsoft be on your permises?

Not OP, but it couldn't it be
https://azure.microsoft.com/en-us/overview/azure-stack/ ?

> Apart from all other things, compare the network latency.  If a single request
> results in 500 database queries, you will be paying 1000 times the network
> latency per request.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com
> 
> 
> 

RE: An I/O error occurred while sending to the backend (PG 13.4)

[l...@laurent-hasson.com]

>From: Ranier Vilela ranier...@gmail.com
>Sent: Thursday, March 03, 2022 13:22
>
>
>You are welcome.
>
>regards,
>Ranier Vilela



Hello all,

After a lot of back and forth, someone in IT informed us that the database VM 
is under a backup schedule using Veeam. Apparently, during the backup window, 
Veeam creates a snapshot and that takes the VM offline for a couple of minutes… 
And of course, they scheduled this right at the busiest time of the day for 
this machine which is during our nightly ETL. Their backup doesn’t perform very 
week either, which explained why the failure seemed to randomly happen at 
various points during our ETL (which takes about 2h30mn).

They moved the schedule out and the issue has not happened again over the past 
3 weeks. This looks like it was the root cause and would explain (I think) how 
the database and the client simultaneously reported a connection timeout.

Thank you so much for all your help in trying to figure this out and exonerate 
Postgres.

Thank you,
Laurent.

Re: An I/O error occurred while sending to the backend (PG 13.4)

[Justin Pryzby]

On Wed, Apr 13, 2022 at 03:36:19PM +, l...@laurent-hasson.com wrote:
> After a lot of back and forth, someone in IT informed us that the database VM 
> is under a backup schedule using Veeam. Apparently, during the backup window, 
> Veeam creates a snapshot and that takes the VM offline for a couple of 
> minutes… And of course, they scheduled this right at the busiest time of the 
> day for this machine which is during our nightly ETL. Their backup doesn’t 
> perform very week either, which explained why the failure seemed to randomly 
> happen at various points during our ETL (which takes about 2h30mn).
> 
> They moved the schedule out and the issue has not happened again over the 
> past 3 weeks. This looks like it was the root cause and would explain (I 
> think) how the database and the client simultaneously reported a connection 
> timeout.
> 
> Thank you so much for all your help in trying to figure this out and 
> exonerate Postgres.

Great, thanks for letting us know.
This time it wasn't postgres' fault; you're 2 for 3 ;)

One issue I've seen is if a vmware snapshot is taken and then saved for a long
time.  It can be okay if VEEM takes a transient snapshot, copies its data, and
then destroys the snapshot.  But it can be bad if multiple snapshots are taken
and then left around for a long time to use as a backup themselves.

-- 
Justin