Load Testing SQL Alchemy: "TimeoutError: QueuePool limit of size 3 overflow 0 reached, connection timed out, timeout 30"

Question

I have a SQL-Alchemy based web-application that is running in AWS.

The webapp has several c3.2xlarge EC2 instances (8 CPUs each) behind an ELB which take web requests and then query/write to the shared database.

The Database I'm using is and RDS instance of type: db.m4.4xlarge. It is running MariaDB 10.0.17

My SQL Alchemy settings are as follows:

SQLALCHEMY_POOL_SIZE = 3
SQLALCHEMY_MAX_OVERFLOW = 0

Under heavy load, my application starts throwing the following errors:

TimeoutError: QueuePool limit of size 3 overflow 0 reached, connection timed out, timeout 30

When I increase the SQLALCHEMY_POOL_SIZE from 3 to 20, the error goes away for the same load-test. Here are my questions:

1. How many total simultaneous connections can my DB handle?
2. Is it fair to assume that Number of Number of EC2 instances * Number of Cores Per instance * SQLALCHEMY_POOL_SIZE can go up to but cannot exceed the answer to Question #1?
3. Do I need to know any other constraints regarding DB connection pool sizes for a distributed web-app like mine?

Answer 1

MySQL can handle virtually any number of "simultaneous" connections. But if more than a few dozen are actively running queries, there may be trouble.

Without knowing what your queries are doing, one cannot say whether 3 is a limit or 300.

I recommend you turn on the slowlog to gather information on which queries are the hogs. A well-tuned web app can easily survive 99% of the time on 3 connections.

The other 1% -- well, there can be spikes. Because of this, 3 is unreasonably low.