Flask + Gevent PyWSGI + MySQL Connector: Sometime got hung when get_connection from pool

Question

I'm developing a Flask server with Gevent PyWSGI and MySQL Connector as DB Driver. After monkey_patch to make it serving multiple requests concurrently, I found sometimes my server got hung with 2 or more concurrent requests and I have to restart server to make it work. Detail:

I set up MySQL Connection Pool (pool_size=10) and sharing this single pool instance over the application.

class PoolManager:
    def __init__(self, *args, **kwargs):
        self.args = args
        self.kwargs = kwargs
        self._init()

    def _init(self):
        self._pool = MySQLConnectionPool(*self.args, **self.kwargs)

    def get_connection(self):
        #logging greenlet id
        print("Start get connection from pool....")
        print("GID {}".format(id(gevent.getcurrent()))) 
        new_cnx = self._pool.get_connection()
        print(" -------CONN_ID: {}".format(new_cnx.__getattr__("connection_id")))
        return new_cnx

When running, at the beginning, everything seems to be okay, I saw the pool initialize 10 connection to MySQL and it works fine. Every coming requests have been served concurrently.
After a random while, I did 2 more requests.. The stdout be stuck at exactly logging line above:

Start get connection from pool....
GID 123xxxxx
Start get connection from pool....
GID 321xxxxx

And then nothing happens. MySQL connections (via show processlist) are sleeping all and my server got hung. So, my question is:

Does I got race condition here? Where're the problems? Gevent Pywsgi or Mysql Connector?
Which is correct approach here? A MysqlConnectionPool can not be reused over threads and should I init each connection for each greenlet?

score 0 · Answer 1 · answered Oct 30 '20 at 20:37

You may need a specific mysql module for gevent. UltraMysql is located here: https://github.com/esnme/ultramysql

You should look here at this example. This is working great in all my production environments.

Python Postgres psycopg2 ThreadedConnectionPool exhausted

Flask + Gevent PyWSGI + MySQL Connector: Sometime got hung when get_connection from pool

1 Answers1