Ecto high database time with Cloud SQL

Questions / Help
,
1

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.

2

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?

1 Like
3

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 socket socket_dir: System.get_env("POSTGRESQL_SOCKET"). Instance IP assignment is set to Public IP. Tell me if you need more information about this.

4

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.

1 Like
5

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.

1 Like
6

Can you please tell me if you did some additional steps to connect to cloud sql through unix socket
I am having trouble connecting to the db from my cloud_run instance.

Here’s the discussion thread

7

Nah man, I worked with a lot of stuff but not with CloudSQL. I distinctly remember having used socket_dir once 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.