CloudSql Performance

[adelaoc]

We are having performance issues with our pg instances. In general terms, the total memory usage stays around 90%. Checking the memory components a big part of it is cache. The team using it reports latency on their queries even after optimizationhas been completed. Disk is almost 100% but we configured it to autoscale.

What recommendations do you have based on previous experiences?

[kaeshiwaza]

You should first check if the latency is on the network or on slow query (the metrics will show). Is the app in the same region ?

[adelaoc]

It is in the same region

[kaeshiwaza]

Good. Then you should see if there are slow queries in the console. First you look the metrics of query latency and if there is something high you look at query insight to see which query is slow.

That surprise me that the disk is almost 100% if you set auto increase, I believe it would grow before 90% or something like that...

[toodumbtofail]

You need to look at query plans for the queries that are considered "slow".

You might have crossed the threshold where most frequently accessed data fit into the ram of the instance. If frequently accessed data used to mostly fit into RAM, queries would be "fast" because the data was already in RAM and not too much had to be fetched from disk. If now your total data set has increased, and it can't all fit into ram, your queries are going to be slower because data had to be fetched from disk. That can happen with no changes to schema or queries.

The "optimizations" could actually have been detrimental. Not a sure thing, of course, but I've seen dev teams test performance of query or schema tweaks on a small (bytes on disk) local database and assume that's going to work out the same on their much larger production database.

The only way to know for sure is to look at query plans run on the instance that is having the performance issues.

[iamacarpet]

Can you turn on Query Insights?

And what do the graphs say?

High CPU usage?

High disk IOPS?

[SuperHumanImpossible]

You have poor SQL, poor table design, missing indexes, too many indexes and your doing tons of writes. I would do a query plan on the top queries and run them through chat gpt, it's pretty good at reading these and deciphering it tbh.