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

14
votes

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

1
postgresqlgoogle-cloud-sql
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. – Marilu
Verify if your GKE disk is experiencing some disk bottlenecks when having the slow queries? – Marilu
Any updates on this? I am having the same issue. – Mike Furlender
Same issue here. Any update? – jeremymarc
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. – A_V

1 Answers

0
votes

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!