AWS re:Post을(를) 사용하면 다음에 동의하게 됩니다. AWS re:Post 이용 약관

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;
1개 답변
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
전문가
답변함 8달 전
profile picture
전문가
검토됨 8달 전
  • 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.

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인