[Python] How to enable connection pooling in Flask-SQLAlchemy?

I'm using Flask-SqlAlchemy with a MySQL database. And recently I started getting this error:

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1040, 'Too many connections')


After some digging, it seems that I'm not using the connection pooling.

Based on my research SQLALCHEMY_POOL_SIZE is now deprecated in v2.4 and shouldn't be used anymore.

SQLAlchemy==1.3.7
Flask-SQLAlchemy==2.4.1


So what is the correct way of doing it?

Config:

SQLALCHEMY_DATABASE_URI = 'mysql://rootASSWORD@localhost/main_db'

SQLALCHEMY_BINDS = {
'radius': 'mysql://rootASSWORD@localhost/radius_db',
'cache': 'mysql://rootASSWORD@localhost/cache_db',
}


Code:

def make_app():
app = Flask(__name__, template_folder="../templates")
app.config.from_object(config)
db.init_app(app)

app = my_fabric.make_app()


According to SqlAlchemy, I'm supposed to do this via create_engine

engine = create_engine("mysql+pymysql://userw@host/db", pool_size=20, max_overflow=0))


But Flask-SqlAlchemy is supposed to abstract this out. So I don't know how this should be configured.

UPDATE:

I'm running uWSGI with two processes.

I have now increased the max_connections to 500. It's hard to say if I have high traffic, but my database statistics after 16 hours showing this:

mysql> show status like '%onn%';
+-------------------------------------------------------+---------------------+
| Variable_name | Value |
+-------------------------------------------------------+---------------------+
| Aborted_connects | 5 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3897 |
| Locked_connects | 0 |
| Max_used_connections | 167 |
| Max_used_connections_time | 2019-11-29 00:11:51 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 117 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 97 |
+-------------------------------------------------------+---------------------+


AND

mysql> SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Connections | 3896 |
| Threads_cached | 8 |
| Threads_connected | 97 |
| Threads_created | 365 |
| Threads_running | 2 |
+-------------------+-------+


AND

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.01 sec)

Continue reading...