pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance for SQL queries on Azure PostgreSQL PaaS instance
7+ messages / 6 participants
[nested] [flat]
* Performance for SQL queries on Azure PostgreSQL PaaS instance
@ 2022-04-12 09:10 Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-12 14:23 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Frits Jalvingh <jal@etc.to>
2022-04-13 08:34 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Laurenz Albe <laurenz.albe@cybertec.at>
2022-04-14 20:20 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance overland <overland@recarea.com>
0 siblings, 3 replies; 7+ messages in thread
From: Kumar, Mukesh @ 2022-04-12 09:10 UTC (permalink / raw)
To: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
@ 2022-04-12 14:23 ` Frits Jalvingh <jal@etc.to>
2022-04-12 15:06 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Tomas Vondra <tomas.vondra@enterprisedb.com>
2 siblings, 1 reply; 7+ messages in thread
From: Frits Jalvingh @ 2022-04-12 14:23 UTC (permalink / raw)
To: Kumar, Mukesh <mkumar@peabodyenergy.com>; +Cc: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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?
On Tue, Apr 12, 2022 at 4:16 PM Kumar, Mukesh <MKumar@peabodyenergy.com>
wrote:
> 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
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-12 14:23 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Frits Jalvingh <jal@etc.to>
@ 2022-04-12 15:06 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tomas Vondra @ 2022-04-12 15:06 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; Kumar, Mukesh <mkumar@peabodyenergy.com>; +Cc: pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
@ 2022-04-13 08:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-04-13 08:42 ` RE: Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-13 11:17 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance andrew cooke <andrew@acooke.org>
2 siblings, 2 replies; 7+ messages in thread
From: Laurenz Albe @ 2022-04-13 08:34 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-13 08:34 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-04-13 08:42 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
1 sibling, 0 replies; 7+ messages in thread
From: Kumar, Mukesh @ 2022-04-13 08:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.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 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 <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 will be paying 1000 times the network latency per request.
Yours,
Laurenz Albe
--
Cybertec | https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Na6zYPRuqYDPkzx...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-13 08:34 ` Re: Performance for SQL queries on Azure PostgreSQL PaaS instance Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-04-13 11:17 ` andrew cooke <andrew@acooke.org>
1 sibling, 0 replies; 7+ messages in thread
From: andrew cooke @ 2022-04-13 11:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Kumar, Mukesh <MKumar@peabodyenergy.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.com>
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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance for SQL queries on Azure PostgreSQL PaaS instance
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
@ 2022-04-14 20:20 ` overland <overland@recarea.com>
2 siblings, 0 replies; 7+ messages in thread
From: overland @ 2022-04-14 20:20 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; pgsql-performance; MUKESH KUMAR <mukesh.kumar14@tcs.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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-04-14 20:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-12 09:10 Performance for SQL queries on Azure PostgreSQL PaaS instance Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-12 14:23 ` Frits Jalvingh <jal@etc.to>
2022-04-12 15:06 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2022-04-13 08:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-04-13 08:42 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-04-13 11:17 ` andrew cooke <andrew@acooke.org>
2022-04-14 20:20 ` overland <overland@recarea.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox