Google Colab ↔ Cloud SQL: Diagnosing Intermittent Connection Pool Exhaustion

Answer

To avoid “Connection pool exhausted” errors, configure the SQLAlchemy QueuePool so that the total possible connections (pool_size + max_overflow) stay below the Cloud SQL instance’s max_connections limit, leaving a safety margin for other services.

Likely explanation

The intermittent errors occur when concurrent threads in the Colab notebook request more connections than the pool can provide, causing the pool to block until pool_timeout expires. If the pool’s configured ceiling plus any overflow exceeds the instance’s max_connections (or a lower limit imposed by the Cloud SQL Proxy), the database rejects new connections and SQLAlchemy raises the exhaustion error.

Confirmed facts

• SQLAlchemy’s default QueuePool uses pool_size=5 and max_overflow=10 unless overridden.
• Cloud SQL instances have a fixed max_connections that depends on the machine type (e.g., db‑f1‑micro allows ~100 connections).
• The Cloud SQL Proxy merely forwards TCP traffic; each client connection still counts toward the instance’s max_connections. Proxy version 2 can multiplex connections but does not create additional database connections unless the -max_connections flag is set.

Steps to diagnose and fix

1. Determine the instance’s max_connections (run SHOW VARIABLES LIKE 'max_connections'; via a SQL client or check the Cloud SQL console). If you do not know this value, please provide it so the calculation can be precise.
2. Estimate the number of concurrent workers in your notebook (e.g., number of threads or parallel cells).
3. Choose a safety margin (10‑20 % of max_connections) for other services such as the Cloud SQL Proxy admin thread or monitoring.
4. Compute the target pool size: pool_size = floor((max_connections * (1 - safety_margin)) / concurrent_workers). Set max_overflow to a small value (e.g., 0‑2) to absorb short bursts.
5. In the notebook, create the engine with these parameters, for example:

   from sqlalchemy import create_engine
   engine = create_engine(
       connection_url,
       pool_size=4,
       max_overflow=1,
       pool_timeout=30,
       pool_recycle=1800
   )
   

6. After each batch of queries, log the pool state:

   checked = engine.pool.checkedout()
   idle = engine.pool.qsize()
   overflow = engine.pool.overflow()
   print(f"Checked out: {checked}, Idle: {idle}, Overflow: {overflow}")
   

   If checked approaches pool_size + max_overflow and errors appear, increase the pool size or reduce concurrency.
7. Verify the Cloud SQL Proxy invocation does not include a -max_connections flag lower than your intended pool size. A typical start command is:

   ./cloud_sql_proxy -instances=PROJECT_ID:REGION:INSTANCE_NAME=tcp:0.0.0.0:3306

   Ensure no extra -max_connections is present.
8. Run a controlled burst test (e.g., 20 threads each executing a simple SELECT 1 loop) and confirm that the logged pool values stay below the configured ceiling and no “Connection pool exhausted” errors are raised.

What to provide for a precise recommendation

Please share the max_connections value of your Cloud SQL instance (or the machine type so it can be looked up). With that number the exact pool_size and max_overflow can be calculated.