How do you get PDO persistent connections to Google's Cloud SQL to work?

Question

I've noticed that while reasonably fast, the connection to the database (google's cloud SQL mysql-compatible one) is a large part of the request. I'm using PDO for the abstraction.

So, since that's the case the obvious solution is to enable PHP's PDO persistent connections.

To my understanding, and I've verified this in PHP's source code (links bellow), the way these work is as follows:

• when you connect with the persistent flag on, PHP caches the connection using a hash of the connection string, username and password for the key
• when you try to re-connect in another request it checks if a persistent connection exists then checks the liveness of the connection (which is driver specific; mysql version is what is executed in my case) and kills the cached version it if it fails the test
• if the cached version was killed a new connection is created and returned; otherwise you get to skip the overhead of creating the connection (around 30x faster creation process based on xdebug profiles executed directly in devel versions on the cloud)

Everything sounds good so far? Not sure how all of this works when say you have a cached connections and two requests hit it (stress testing it doesn't appear to cause issues), but otherwise sounds okey and in testing works fine.

Well, here's what happens in the real world after some time passes...

Once a connection "dies" PDO will stall the entire request for 60s or more. This happens I believe after maybe 1h or more; so for a short while everything will work just fine and PDO will connect super fast to Cloud SQL. I've tried several ways to at least mitigate the stalling being more then 1s but to no result (ini_set socket timeout wont affect it, expires flag on PDO is ignored I believe, exception and status checks for the "has gone away" are useless since it stalls on making the connection, etc). I assume most likely the connection "expires" but reasons are unknown to me. I assume Cloud SQL drops it since its not in "show processlist;", but it's possible I'm not looking at it correctly.

---

Is there any secret sauce that makes PDO persistent connections work with Cloud SQL for more then a brief time?

Are persistent connections to Cloud SQL not supported?

Answer 1

You haven't described where your application is running (e.g. Compute Engine, App Engine, etc), so I will make an educated guess based on the described symptoms.

It's likely your TCP keepalive time is set too high on the application host. You can change the settings via these instructions.

Assuming a Linux host, the following command will show your current setting, in seconds:

cat /proc/sys/net/ipv4/tcp_keepalive_time

TCP connections without any activity for a period of time may be dropped anywhere along the path between the application host and Cloud SQL, depending on how everything in the communication path is configured.

TCP keepalive sends a periodic "ping" on idle connections to work around this problem/feature.

Cloud SQL supports long-running connections.