I have a flask routine that has a rather simple Flask-SQLAlchemy database structure comprising user and a few other simple tables. To run my flask site in AWS, I have composed a docker independently for the python / flask GUI, and a separate docker to host the MySQL database (with permanent storage).
The challenge I am running into appears to be lingering connections. Originally, I was receiving a number of connection errors:
TimeoutError: QueuePool limit of size 25 overflow 15 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/13/3o7r)
I was able to address this (and at least prolong the time until i needed to restart the flask docker) by adding the following modifications:
app.config['SQLALCHEMY_POOL_SIZE'] = 80
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 20
app.config['SQLALCHEMY_POOL_RECYCLE'] = 1800
I also attempted to address by adding db.close() statements after various database accesses, but my username access / session variables ceased to function properly to retain user identity between pages.
Finally, because I utilize an SSE stream (below) that is interconnected with database access, I boosted the pool_size and max_overflow values and gained some additional uptime. My thinking here was that users should only remain on the stream page for a limited period of time and that my timeout limits would close the database after a period and return the pool connections.
@games.route('/stream_game_play_channel')
def stream_game_play_channel():
@stream_with_context
def eventStream():
channel = session.get('channel')
game_id = int(left(channel, 5))
cnt = 0
while cnt < 1000:
#print(f'cnt = 0 process running from: {current_user.username}')
time.sleep(0.5)
ntime = redisChannel.get(channel)
#print(f'Still running...{ntime}')
if cnt == 0:
msgs = db.session.query(Messages).filter(Messages.game_id == game_id) \
.filter(Messages.type == 'gamePlay')
db.session.close()
msg_list = [f'{i.msg_from:>20}: {i.message}' for i in msgs]
cnt += 1
ltime = ntime
lmsg_list = msg_list
for i in msg_list:
yield "data: {}\n\n".format(i)
elif ntime != ltime:
#print(f'cnt > 0 process running from: {current_user.username}')
#time.sleep(1)
db.session.commit()
msgs = db.session.query(Messages).filter(Messages.game_id == game_id) \
.filter(Messages.type == 'gamePlay')
db.session.close()
msg_list = [f'{i.msg_from:>20}: {i.message}' for i in msgs]
for i in itertools.islice(msg_list, len(lmsg_list), len(msg_list)):
yield "data: {}\n\n".format(i)
lmsg_list = msg_list
ltime = ntime
cnt += 1
return Response(eventStream(), mimetype="text/event-stream")
Unfortunately, by increasing the pool size further I am now seeing my MySQL database hit limits.
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1040, 'Too many connections')
How might I begin to diagnose which parts of my code are not recycling pool connections?