Cannot set pool size for Flask_sqlalchemy

user_223960 0
Viewed 4954

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.

flask-sqlalchemy flask sqlalchemy python-3.x python
2 Answers

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.

user_1002970

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.

user_105481370
Related