How to resolve slow Hasura API requests?

[samkit-jain]

We are using Hasura Cloud with AWS Aurora for PostgreSQL (Standard). The DB instance type is db.r5.xlarge. The issue we are facing is that all the 3 reader instances (2 of those are from auto-scaling) consistently stay at 90% CPU usage throughout making the Hasura APIs slow. The writer instance barely crosses 10% CPU usage.

I have read https://hasura.io/docs/latest/databases/database-config/cloud-connection-pooling/ and reduced the total max connections to 4 (other parameters as default) and yet, I am seeing 30+ connections to my DB. Ideally, with a low max connections count, the connections should be reduced and pooled but from the RDS logs I can see 10s of connections and disconnections every few seconds.

From the OS Process Metrics logs on the AWS console, if the DB has 4 connections, the CPU usage is around 15-19% for each. If 2 connections, then around 20% for both.

I have tried tweaking the parameters of the DB like upgrading the instance from db.t4g.large to db.r5.xlarge, and changing the DB instance parameters but no luck.

The database has indexing enabled as well.

Furthermore, the API responds slowly but if I run the same SQL query directly on the DB, it gives a response in milliseconds.

Hasura provided caching @cached) cannot be used as it has a catch that it doesn’t work for the queries to which session variables are passed.

How can I ensure that my Hasura connections don't exhaust too much of the CPU and provide fast API response times?

[SamirTalwar]

Hey @samkit-jain, as this is a question about Hasura Cloud, I suggest you open a support ticket, which will allow engineers to connect your question to the logs and dig into it.

In general, discussions are better suited to questions about the product rather than behavior of the Cloud service.

[samkit-jain]

Appreciate the respobse @SamirTalwar but I think the question can be discussed here as the connection pooling issue and SQL query optimisation is related to the graphql-engine. If that is not the case, please let me know if there's any community forum where I can post and I shall close this discussion here. Thanks.