zatae
I have a Phoenix framework project running on Cloud Run. The database is hosted on Cloud SQL.
For some reason, I have really high db response time for even really simple requests and nearly no data:
2022-04-05 20:24:31.035 CEST
18:24:31.035 [debug] QUERY OK source="items" db=298.8ms queue=0.1ms idle=900.7ms
2022-04-05 20:24:31.035 CEST
SELECT t0."id", t0."name", t0."identifier", t0."enabled", t0."user_id", t0."inserted_at", t0."updated_at" FROM "items" AS t0 WHERE (t0."identifier" = $1) ["f2356731-fbd4-4a10-8aa2-c89adf48a98d"]
2022-04-05 20:27:15.077 CEST
18:27:15.076 [debug] QUERY OK source="items" db=692.3ms idle=807.2ms
2022-04-05 20:27:15.077 CEST
SELECT t0."id", t0."name", t0."identifier", t0."enabled", t0."user_id", t0."inserted_at", t0."updated_at" FROM "items" AS t0 WHERE (t0."identifier" = $1) ["f2356731-fbd4-4a10-8aa2-c89adf48a98d"]
SELECT COUNT(*) FROM items;
count
-------
16
(1 row)
Any idea ? I tried various type of instance size (both SQL & Run container) but this seems that this is not the problem.
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #blog-post
- #phoenix_html
- #iex
- #ai
- #graphql
- #genstage
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 6- Show Best Posts
- Show All Posts (oldest first)
- Show All Posts (newest first)
mylan
Just a shot in the dark, but are they in the same region? And are you using the internal database address or are you using the public address?
zatae
Sorry for the lack of data related to the configuration, I was in a hurry!
So, both instances are on same region : us-central1 for Cloud Run and us-central1-f for Cloud SQL. (I assume these are the same). I use
:postgrex, "0.15.13"&:ecto, "3.7.1"and connect to database through socketsocket_dir: System.get_env("POSTGRESQL_SOCKET"). Instance IP assignment is set to Public IP. Tell me if you need more information about this.joey_the_snake
If you show us the explain plan it would help seeing what’s happening at the db level. You can do so using this function:
Using the
anlayze: trueopt would provide the best info.zatae
Finally found what’s was wrong : I have a process running in background, pulling messages from a GCP PubSub and processing it with some db queries (the ones that were really slow). And it seems that without any incoming traffic from the outside (GCP PubSub pull requests obviously aren’t), the container goes in some kind of sleep mode after a few seconds.
To overcome this, you just need to update your container’s configuration
CPU allocation and pricingtoCPU is always allocatedso the container never goes in “sleep mode” and is always awake to query database without any delay. This was more like a GCP issue than a Elixir one but still, it might be useful for some other ppl.Thanks for your help.
rathoud96
Can you please tell me if you did some additional steps to connect to cloud sql through
unix socketI am having trouble connecting to the db from my
cloud_runinstance.Here’s the discussion thread
dimitarvp
Nah man, I worked with a lot of stuff but not with CloudSQL. I distinctly remember having used
socket_dironce to connect to a local Postgres and it worked. Can’t help you further I am afraid. Must be something in the configuration of CloudSQL or something else.