question

ShafinAnwarsha-7774 avatar image
0 Votes"
ShafinAnwarsha-7774 asked OuryBa-MSFT commented

Slow perfomance of Azure Postgresql DB

We configured Azure database for PostgreSQL server with performance configuration like: General Purpose, 4 vCore(s), 100 GB
We currently have data in database of only around 1 GB and we have not scaled our application cause it is not required as of now.
The performance of PostgreSQL is very low. It is taking around 15 to 30 second in simple query to execute from PG Admin as well.
Can you please help us to get it resolved?
Can we edit performance configuration if so then how?

azure-database-postgresql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT commented

HI @ShafinAnwarsha-7774 Thank you for posting your question on Microsoft Q&A.

This may be due for many reasons.

Please check the following to help you identify the cause of the slow performance

  1. Please navigate to your Azure Database for PostgreSQL and use the Metrics blade to check if you are maxing out any of your resource limits.
    If your workload is maxing out any of these server metrics please consider scaling up resources to accommodate the workload and review top queries consumers and tune them.

  2. Overall Slowness
    Disabling pg_stat_statements, this can be done by visiting the server parameters blade on the Azure Portal and set "pg_stat_statements.track" to NONE.
    Some customers workload have seen up to a 50 percent performance improvement with disabling this. The tradeoff you make when you disable pg_stat_statements is the inability to troubleshoot performance issues.
    Network latency: Check the network latency between the client and the database service instance. You can check the network latency by running simple query as
    SELECT 1
    Consider disabling the following server parameters to reduce the overhead on the database side. Those server parameters are helpful to understand your workload but proven to cause performance degradation when enabled.

  • log_duration = OFF

  • log_min_duration_statement = -1

  • log_statement_stats = OFF
    Checkpoint: Periodic flush of the cached data to the disk to ensure the durability. This is an IO intense job and cause some performance impact. For larger databases, please consider changing the following two server parameters:
    bgwriter_delay = 20
    checkpoint_completion_target = 0.2
    Autovacuum:
    PostgreSQL uses multiversion concurrency control (MVCC) to allow greater database concurrency. Every update results in an insert and delete, and every delete results in rows being soft-marked for deletion. Soft-marking identifies dead tuples that will be purged later. To carry out these tasks, PostgreSQL runs a vacuum job. More information can be found here.
    "autovacuum_*" server parameters needs to be tuned to be more effective and less invasive.

Check the query performance insight to identify what your longest running queries are, how they change over time, and what waits are affecting them.

Please try the above and let us know the result.

Regards,
Oury


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @ShafinAnwarsha-7774 I Checking if you were able to go through the steps I provided above to find the cause of the slow performance on your Azure PostgreSQL database .

Please let me know the result.

Regards,
Oury

0 Votes 0 ·

@ShafinAnwarsha-7774 Please let me know if you were able to check the instructions I provided above. Let me know if you want to discuss further on the same

Regards,
Oury



0 Votes 0 ·

@OuryBa-MSFT I already set this parameter but performance is not improved yet. Can you please help me to resolve it?

0 Votes 0 ·

Hi @ShafinAnwarsha-7774 sorry for the delay in my response.

Have you tried using PostgreSQL flexible server instead since it provides better performance. Flexible server service is available in three SKU tiers.
Flexible server service is available in three SKU tiers: Burstable, General Purpose, and Memory Optimized. The General Purpose and Memory Optimized are better suited for production workloads requiring high concurrency, scale, and predictable performance.
This is just a suggestion. I need to work closely with the Product group to understand the cause and to troubleshoot this issue.

Regards,
Oury

0 Votes 0 ·