Cannot set pool size for Flask_sqlalchemy

Question

I would like to set the sqlalchemy pool size to something other than the default.

I have a flask app. I set the config from a file file using app.config.from_pyfile('config.py')

Right before I initializing the db I've dumped the config and it includes:

'SQLALCHEMY_DATABASE_URI': 'mysql://readonly:password@localhost/xyz',
'SQLALCHEMY_ECHO': False,
'SQLALCHEMY_MAX_OVERFLOW': 0,
'SQLALCHEMY_POOL_SIZE': 1,
'SQLALCHEMY_RECORD_QUERIES': False,
'SQLALCHEMY_TRACK_MODIFICATIONS': False,

Right after loading the config and doing that dump we are calling some code like:

db: SQLAlchemy = SQLAlchemy()
db.init_app(app)

I'm running this on apache with mod_python with one process and one thread. I'm using this in the httpd.conf

WSGIDaemonProcess browse user=busybody group=busybody processes=1 threads=1 lang='en_US.UTF-8' locale='en_US.UTF-8' python-home=/users/x/virtualenvs/browse-wfalcMKM home=/users/x/browse
WSGIScriptAlias /abs /users/e-prints/browse/wsgi.py/abs

Then I hammer this with siege and 100 concurrent connections.

I'm getting 20 processes for the user 'readonly' in mysql SHOW PROCESSLIST; What I expect to see is 1 process for the user 'readonly' in the SHOW PROCESSLIST;

There are no other applications using the user 'readonly' and when I stop the httpd/python/flask app there are zero 'readonly' users in the SHOW PROCESSLIST;

I'm basing my hopes to configuring things the way I'm trying from the docs here: http://flask-sqlalchemy.pocoo.org/2.3/config/

I'm using python 3.6 and mysql 5.1.73. It appears I have Flask_SQLAlchemy 2.3.2, mysqlclient 1.3.13, and SQLAlchemy 1.2.12. This is on linux. Apache with mod-wsgi 4.5.15.

Update:

I added a debug and I'm seeing db.engine.pool.size is set to what I expect it to be.

I'm not sure if this is how it is supposed to work but I see that I'm getting different pool objects in different requests:

[Mon Oct 29 12:17:25 2018]  - ERROR: "pool size = 1"
[Mon Oct 29 12:17:25 2018]  - ERROR: "engine object = Engine(mysql://browse_readonly:***@localhost/arXiv?charset=utf8)"
[Mon Oct 29 12:17:25 2018]  - ERROR: "pool object = <sqlalchemy.pool.QueuePool object at 0x7f2342b97da0>"

[Mon Oct 29 12:17:35 2018]  - ERROR: "pool size = 1"
[Mon Oct 29 12:17:35 2018]  - ERROR: "engine object = Engine(mysql://browse_readonly:***@localhost/arXiv?charset=utf8)"
[Mon Oct 29 12:17:35 2018]  - ERROR: "pool object = "sqlalchemy.pool.QueuePool object at 0x7f2342b39400>"

Update 2: I'm using apache and mod-python. I added that to the body of the question above. Update 3: My mistake, we are using mod-wsgi.

What WSGI server are you using? I suspect that you are using a server that uses child processes to scale up, so each child process has their own pool. — Martijn Pieters
– Martijn Pieters, Commented Oct 30, 2018 at 11:49

Martijn Pieters · Accepted Answer · 2018-10-30 14:36:54Z

5

You are setting the pool size correctly. But you almost certainly have multiple, separate WSGI processes:

I'm not sure if this is how it is supposed to work but I see that I'm getting different pool objects in different requests:
[...]  
[Mon Oct 29 12:17:25 2018]  - ERROR: "pool object = <sqlalchemy.pool.QueuePool object at 0x7f2342b97da0>"

[...]
[Mon Oct 29 12:17:35 2018]  - ERROR: "pool object = "sqlalchemy.pool.QueuePool object at 0x7f2342b39400>"

Flask-SQLAlchemy uses a straightforward one-engine-with-one-pool per Flask app setup, the only way you can have multiple QueuePool instances in by having multiple Flask instances, which outside of more exotic multi-app setups means you are serving the app in a multi-process WSGI server.

WSGI servers typically use multiple worker processes to scale up performance, together with threads. Each worker process is independent and has its own Flask instance, each with its own SQLAlchemy engine and pool. It depends on your exact WSGI server how you'd configure it to only use threading.

It's otherwise fine and normal that separate processes need their own connection to MySQL.

edited Oct 30, 2018 at 14:36

answered Oct 30, 2018 at 12:03

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian C. Over a year ago

We are using mod-python with apache prefork.

Brian C. Over a year ago

Actually it is mod-wsgi 4.5.15.

Martijn Pieters Over a year ago

@BrianC.: Apache pre-fork creates new processes, but it depends on your mod_wsgi config how many actual WSGI processes there'll be; e.g. if you have set up a WSGI daemon process or not. At any rate, each separate process must have, by necessity, its own connection to MySQL.

EAW · Accepted Answer · 2018-10-27 21:25:56Z

2

It is definitely possible to set CONNECTION_POOL_SIZE at the time of engine creation with

app.config['SQLALCHEMY_POOL_SIZE'] = 1
db = SQLAlchemy(app)

This can be confirmed to have worked with

print("pool size = {}".format(db.engine.pool.size()))

Some of the configuration parameters cannot be changed after engine creation so the problem may be you are creating the engine with db = SQLAlchemy() and then applying the configuration afterwards. This is not the whole story however as the default pool size is 5 and there must be something else going if you are seeing 20 concurrent connections.

answered Oct 27, 2018 at 21:25

EAW

9527 silver badges11 bronze badges

2 Comments

Brian C. Over a year ago

As far as I can figure, the 20 as the number of connections is coming from 10 from overflow (SQLAlchemy's default) and 10 for pool size set by Flask_SQLAlchemy in for mysql drivers in apply_driver_hacks().

Brian C. Over a year ago

I've added a debugging statement and I get the size I expect. Currently this is 1. I'm running with two processes, three threads, pool size 1 and overflow of 0 and I'm seeing over 30 connections to the database. I would expect 2 connections. So I'm still not sure how to get this to work.

Collectives™ on Stack Overflow

Cannot set pool size for Flask_sqlalchemy

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related