zatae
Ecto high database time with Cloud SQL
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.
Marked As Solved
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 pricing to CPU is always allocated so 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.
Also Liked
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?
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:
https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#explain/4
Using the anlayze: true opt would provide the best info.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
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.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Courses that'll move you from confusion to "Aha, now I get it!"