Google Cloud SQL Postgres - randomly slow queries from Google Compute / Kubernetes

Question

I've been testing Google Cloud SQL with Postgresql, but I have random queries taking ~3s instead of a few ms.

Troubleshooting I did:

• The queries themselves aren't problems, rerunning the same query will work.
• Indexes are properly set. The database is also very very small, it shouldn't do this, even if there weren't any index.
• The Kubernetes container is connecting to the database through SQL Proxy (I followed this https://cloud.google.com/sql/docs/postgres/connect-kubernetes-engine). It is not the problem though as I tried to connect directly to the database, with the same issue.
• I configured net.ipv4.tcp_keepalive_time to 60 to make sure the connection weren't dropping.
• I also have a pool of connection that are never disconnected to make sure it wasn't from that.
• When I run queries directly through my local Postgresql client, I never have the problem.
• I don't have this issue when developing locally either and connecting to my local database.

What I'm getting at is: I feel there's some weird connection/link issue between my Google Compute instances and my Google SQL instance that I can't seem to figure out. Any idea?

Edit: I also noticed these logs in my SQL Cloud instance every 30s: ERROR: recovery is not in progress HINT: Recovery control functions can only be executed during recovery. STATEMENT: SELECT pg_is_xlog_replay_paused(), current_timestamp

Horrify

Are you running the same query multiple times? Does your query have 'GROUP_BY'? These kind of statements will require memory. You might want to use some profiling tools such as EXPLAIN or slow query log. If you have queries that are using temp files, you might want to increase the value for the temp_file_limit flag in your instance. – Skier

Verify if your GKE disk is experiencing some disk bottlenecks when having the slow queries? – Skier

1. I don't think the queries themselves are the problem. I connected the SQL instance to my local dev machine and run the software against it, it worked fine. – Horrify

2. I will look at the GKE disk thanks – Horrify

Hoh, I also have these random errors in my SQL Cloud instance, every 30 seconds (added to the question) – Horrify

Have you also consider increasing the size of your instance? If you disk is not experiencing bottlenecks and you believe that this might be an issue on the Cloud SQL, you might want to open a Public Issue Tracker to review this issue. You will need to provide the steps to reproduce to further investigate, eg. your configuration, a DB example, the query used, machine type, etc. – Skier

BTW, the Public Issue Tracker I was referring is for the slow queries. Regarding the logs in your instance every 30 secs, it's a know issue and it should be fixed any time when a non-critical roll-out will be done. This shouldn't cause any other issues except making the log spammy. – Skier

Ok thanks! Still investigating at the moment, I don't see any problem with the disk. I cannot reproduce easily, so it's definitely a limiting factor. – Horrify

Any updates on this? I am having the same issue. – Rhoades

Really? What are you seeing? No update. – Horrify

Same issue here. Any update? – Debatable

Same issue here. Using mysql, kubernetes and node.js applications. Usual query time is ~10-25ms and we get random 500-1500ms every so often. – Warrin

how much data do you have in the DB? Maybe the index doesn't fit in memory anymore at some point. Maybe setup you program to query every 10sec, is it slow in a consistent interval? Do you have inserts or other queries running in parallel that are blocking your query? – Quandary

Answer 1

That's an interesting problem you are facing. So my knowledge on Kubernetes isn't that great, but I do have a general understanding so let's see if I can provide some suggestions.

To start with, the API that you linked to in your question does mention that it is still in beta. So I do believe there would still be issues to patch in maximizing speed performance.

Secondly, from what I understand, Kubernetes is a great tool for handling stateless workloads. Thus, handling data where state is required for queries would be a slow operation. This article (although not entirely related) does explain some of the pitfalls of Kubernetes (not all the questions are relevant)

Thirdly, could you explain your use case a little bit? Do you really need to use Kubernetes or will another tool like a powerful Compute Engine Instance or or a Dataflow job resolve the the issue? Are you making your database queries through a programming language or an application call?

Thanks, and do let me know!

Parlay