Обсуждение: Performance for SQL queries on Azure PostgreSQL PaaS instance

On 4/12/22 16:23, Frits Jalvingh wrote:
> You might be comparing apples and pears..
> 
> Your Oracle is running on prem while Postgres is running on Azure. Azure
> does not really have disks; it seems to have just a bunch of old people
> writing the data on paper - I/O on Azure is ridiculously slow. What
> disks/hardware does the on-prem Oracle have?
> 

Right. It'd be good to do some basic system benchmarks first, e.g. using
"fio" or similar tools, before comparing query timings. It's quite
possible this is due to Azure storage being slower than physical drives
in the on-premise system.

If that does not explain this, I suggest picking a single query and
focus on it, instead of investigating all queries at once. There's a
nice wiki page explaining what info to provide:

https://wiki.postgresql.org/wiki/Slow_Query_Questions


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 2022-04-12 at 09:10 +0000, 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

Hi Albe , 

I mean to say that , we have everything hosted on Oracle is on On - Prem DC and everything hosted on Azure PostgreSQL
onMicrosoft 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 <laurenz.albe@cybertec.at> 
Sent: Wednesday, April 13, 2022 2:04 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; pgsql-performance@postgresql.org; MUKESH KUMAR <mukesh.kumar14@tcs.com>
Subject: Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On Tue, 2022-04-12 at 09:10 +0000, 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
willbe 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$

On Wed, Apr 13, 2022 at 10:34:24AM +0200, Laurenz Albe wrote:
> On Tue, 2022-04-12 at 09:10 +0000, 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
> 
> 
> 

Azure VM's are incredibly slow. I couldn't host a OpenStreetMap
database because the disk IO would die off from reasonable performance
to about 5KB/s and the data import wouldn't finish. Reboot and it would
be fine for a while then repeat. $400 a month for that. 

You are better off on bare metal outside of Azure, otherwise it is
going to be cloudy misery. I'm saving hundreds renting a bare metal
machine in a data center and I get the expected performance on top of
the cost savings. 


-----Original Message-----
From: "Kumar, Mukesh" <MKumar@peabodyenergy.com>
To: pgsql-performance@postgresql.org
<pgsql-performance@postgresql.org>, MUKESH KUMAR
<mukesh.kumar14@tcs.com>
Subject: Performance for SQL queries on Azure PostgreSQL PaaS instance
Date: Tue, 12 Apr 2022 09:10:23 +0000

Hi Team,
 
Greetings !!
 
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.
We are using below specifications for PostgreSQL
PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage
on general purpose tier ) = 8 Core and 40 Gb of Memory
PostgreSQL version - 11.4
 
We have tried running maintenance Jobs like vaccum, analyze, creating
indexes, increasing compute but no sucess
 
 
I am happy to share my server parameter for PostgreSQL for more
information.
 
Please let us know if this is expected behavior in PostgreSQL or is
there any way i can decrease the time for the SQL queries and make it a
comparison with Oracle
 
Regards,
Mukesh Kumar