How can I trace SQLAlchemy table connection / locks

Viewed 156

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?

1 Answers

My solution was to amend my connection engine to refer to the session:

report_mktgsales_df = pd.read_sql_query(sql_code, con=db.session.bind).drop(columns=['company'])

Previous to this, I had been using "db.engine" and this was leaving a trail of unclosed connections.

Related