Usando AWS re:Post, accetti AWS re:Post Termini di utilizzo

Aurora Serverless V2 with PosgresSQL returning slow queries and high ACUs

0

We have setup Aurora serverless V2 in our production environment. Workload is managed well, but when it comes to return larger datasets, it takes nearly 1 minute to return response of larger datasets, resulting in high ACUs. Pricing is also going very high.

I have used minimum 1 and max 4 ACUs for beginning. Going with more ACUs may increase a cost, which may be out of our pocket as we are just a startup. Not sure if Aurora is the right choice for us.

Here is the one the sample query that is resulting in high ACUs and slow response,

SELECT COUNT(DISTINCT c.id) FROM customer c JOIN customer_event ce ON c.id = ce.customer_id LEFT JOIN customer_event_property cep_1 ON ce.id = cep_1.customer_event_id LEFT JOIN event_property ep_1 ON ce.event_id = ep_1.event_id AND ep_1.id = cep_1.event_property_id LEFT JOIN customer_event_property cep_2 ON ce.id = cep_2.customer_event_id LEFT JOIN event_property ep_2 ON ce.event_id = ep_2.event_id AND ep_2.id = cep_2.event_property_id WHERE ( ce.customer_id = c.id and ( (ce.event_id = x AND cep_1.event_property_id = 89 AND cep_1.value_jsonb ->> 'value' = 'New Arrival' AND cep_2.event_property_id = y AND cep_2.value_jsonb ->> 'value' = 'xyz') OR (ce.event_id = a AND cep_1.event_property_id = b AND (cep_1.value_jsonb ->> 'value')::numeric >= 100) ) )

AND c.brand_id = xxyyzz;
posta 8 mesi fa631 visualizzazioni
1 Risposta
0

Hello Zain,

Slow queries and high ACUs in Aurora Svless with PostgreSQL, especially with complex queries like yours, can be addressed by optimizing your database and query design. Consider the following strategies:

  • Indexing: Ensure appropriate indexes are in place for the columns involved in joins and where conditions. Indexes on customer_id, event_id, and **event_property_id **might improve performance.
  • Query Optimization: Simplify the query if possible. Break down complex queries into smaller parts or use common table expressions (CTEs) to make them more efficient.
  • Partitioning: If your tables are large, consider partitioning them based on frequently queried columns or ranges to reduce the amount of data scanned.
  • Monitoring and Analyzing: Use Amazon Aurora's performance insights to identify slow queries and the reasons behind them. Look for full table scans and inefficient joins.
  • Adjusting Workload: Review and adjust your serverless V2 configuration to ensure it aligns with your workload requirements. Sometimes, fine-tuning the scaling policy can help manage costs better.

Implementing these strategies can help reduce query times and control ACUs, potentially leading to cost savings.

profile picture
ESPERTO
con risposta 8 mesi fa
profile picture
ESPERTO
verificato 8 mesi fa
  • Indexes are there all required columns involved in join and where conditions. Right now our database is not that large, may be 4 to 5 million records. But I will go up in the future.

  • When managing costs and performance for Aurora databases, a critical analysis of your application's resource usage patterns is essential. For predictable and constant workloads, provisioned Aurora instances can be more cost-effective than Aurora Serverless. Provisioned instances offer a stable cost structure and allow for better budget control, especially when you can accurately predict your application's performance demands. Adding read replicas to provisioned instances can further optimize workload management and cost.

    Experience shows that in scenarios where CPU/memory usage is consistently high, the expenses associated with Aurora Serverless's dynamic scaling can surpass those of provisioned instances. Opting for provisioned instances with read replicas has proven to be a financially prudent approach in such cases, providing both cost efficiency and reliable performance.

    Moreover, the minimum ACU requirement for databases with significant data volumes may render Aurora Serverless less suitable. A thorough review of your application’s historical data and usage trends can guide you in choosing the right database service model. This long-term perspective helps in making an informed decision, balancing between the scalability of Serverless and the predictable costs of provisioned instances.

  • I highly recommend extracting CPU/Memory data from CloudWatch into a CSV spreadsheet for a comprehensive analysis. You can find numerous case studies on the internet detailing various scenarios and comparisons between Aurora Serverless and provisioned instances. These studies often illustrate how different usage patterns can impact cost-effectiveness and performance, helping to make an informed decision based on your application's specific needs.

    In one of my applications, after analyzing the CPU/memory usage and calculating the costs, I found that using Aurora Serverless would significantly increase expenses compared to provisioned instances. By opting for a provisioned instance, I gained more computational power throughout the day while incurring lower costs. This approach proved to be more efficient and cost-effective, aligning better with my application's performance needs and budget constraints.

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande