Reputation: 713
I am using pymysql client to connect to mysql in my flask API, everything works fine for some days(around 1-2 days) after that suddenly it starts to throw this error
Traceback (most recent call last):
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1039, in _write_bytes
self._sock.sendall(data)
TimeoutError: [Errno 110] Connection timed out
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "Main.py", line 194, in post
result={'resultCode':100,'resultDescription':'SUCCESS','result':self.getStudentATData(studentId,args['chapterId'])}
File "Main.py", line 176, in getStudentATData
cur.execute("my query")
File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 855, in query
self._execute_command(COMMAND.COM_QUERY, sql)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1092, in _execute_command
self._write_bytes(packet)
File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1044, in _write_bytes
"MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (TimeoutError(110, 'Connection timed out'))")
And if restart the application it again works fine, i have tried everything but cant seem to get away with this, can anyone help? As suggested, i am implemented a retry kind of mechanism but that didn't solve the problem
def connect(self):
#db connect here
def cursor(self):
try:
cursor = self.conn.cursor()
except Exception as e:
print(e)
self.connect()
cursor = self.conn.cursor()
return cursor
And consuming it like DB().cursor()
Upvotes: 8
Views: 10149
Reputation: 25293
First of all, you need to decide whether you want to maintain a persistent connection to MySQL. The latter performs better, but need a little maintenance.
Default wait_timeout
in MySQL is 8 hours. Whenever a connection is idle longer than wait_timeout
it's closed. When MySQL server is restarted, it also closes all established connections. Thus if you use a persistent connection, you need to check before using a connection if it's alive (and if not, reconnect). If you use per request connection, you don't need to maintain the state of connection, because connection are always fresh.
A non-persistent database connection has evident overhead of opening connection, handshaking, and so on (for both database server and client) per each incoming HTTP request.
Here's a quote from Flask's official tutorial regarding database connections:
Creating and closing database connections all the time is very inefficient, so you will need to keep it around for longer. Because database connections encapsulate a transaction, you will need to make sure that only one request at a time uses the connection. An elegant way to do this is by utilizing the application context.
Note, however, that application context is initialised per request (which is kind of veiled by efficiency concerns and Flask's lingo). And thus, it's still very inefficient. However it should solve your issue. Here's stripped snippet of what it suggests as applied to pymysql
:
import pymysql
from flask import Flask, g, request
app = Flask(__name__)
def connect_db():
return pymysql.connect(
user = 'guest', password = '', database = 'sakila',
autocommit = True, charset = 'utf8mb4',
cursorclass = pymysql.cursors.DictCursor)
def get_db():
'''Opens a new database connection per request.'''
if not hasattr(g, 'db'):
g.db = connect_db()
return g.db
@app.teardown_appcontext
def close_db(error):
'''Closes the database connection at the end of request.'''
if hasattr(g, 'db'):
g.db.close()
@app.route('/')
def hello_world():
city = request.args.get('city')
cursor = get_db().cursor()
cursor.execute('SELECT city_id FROM city WHERE city = %s', city)
row = cursor.fetchone()
if row:
return 'City "{}" is #{:d}'.format(city, row['city_id'])
else:
return 'City "{}" not found'.format(city)
For a persistent connection database connection there are two major options. Either you have a pool of connections or map connections to worker processes. Because normally Flask WSGI applications are served by threaded servers with fixed number of threads (e.g. uWSGI), thread-mapping is easier and as efficient.
There's a package, DBUtils, which implements both, and PersistentDB
for thread-mapped connections.
One important caveat in maintaining a persistent connection is transactions. The API for reconnection is ping
. It's safe for auto-committing single-statements, but it can be disrupting in between a transaction (a little more details here). DBUtils takes care of this, and should only reconnect on dbapi.OperationalError
and dbapi.InternalError
(by default, controlled by failures
to initialiser of PersistentDB
) raised outside of a transaction.
Here's how the above snippet will look like with PersistentDB
.
import pymysql
from flask import Flask, g, request
from DBUtils.PersistentDB import PersistentDB
app = Flask(__name__)
def connect_db():
return PersistentDB(
creator = pymysql, # the rest keyword arguments belong to pymysql
user = 'guest', password = '', database = 'sakila',
autocommit = True, charset = 'utf8mb4',
cursorclass = pymysql.cursors.DictCursor)
def get_db():
'''Opens a new database connection per app.'''
if not hasattr(app, 'db'):
app.db = connect_db()
return app.db.connection()
@app.route('/')
def hello_world():
city = request.args.get('city')
cursor = get_db().cursor()
cursor.execute('SELECT city_id FROM city WHERE city = %s', city)
row = cursor.fetchone()
if row:
return 'City "{}" is #{:d}'.format(city, row['city_id'])
else:
return 'City "{}" not found'.format(city)
To give a little clue what performance implications are in numbers, here's micro-benchmark.
I ran:
uwsgi --http :5000 --wsgi-file app_persistent.py --callable app --master --processes 1 --threads 16
uwsgi --http :5000 --wsgi-file app_per_req.py --callable app --master --processes 1 --threads 16
And load-tested them with concurrency 1, 4, 8, 16 via:
siege -b -t 15s -c 16 http://localhost:5000/?city=london
Observations (for my local configuration):
pymysql
has to parse MySQL protocol in pure Python, which is the bottleneck),mysqld
's CPU utilisation is ~55% for per-request and ~45% for persistent connection.Upvotes: 15
Reputation: 139
As I see, you have two options:
Create new connection for every query, then close it. Like this:
def db_execute(query):
conn = MySQLdb.connect(*)
cur = conn.cursor()
cur.execute(query)
res = cur.fetchall()
cur.close()
conn.close()
return res
Upvotes: 4
Reputation: 485
Have you tried doing a dB ping , and if that fails reconnecting before each call? The other thing I found with flask was if I didn’t close the connection after each call I would end up with situations like this.
Sorry for lack of detail but I’m typing this on a phone and scrolling through all your code was had work :-)
class MyDatabase():
def __init__(self, host, user, passwd, db, charset):
self.host = host
self.usr = user
self.passwd = passwd
self.db = db
self.curclass = pymysql.cursors.DictCursor
self.charset = charset
self.connection = pymysql.connect(host=self.host, user=self.usr, passwd=self.passwd, db=self.db,
cursorclass=self.curclass, charset=self.charset)
def get_keywords(self):
self.connection.connect()
cur = self.connection.cursor()
sql = """
SELECT * FROM input_keywords
"""
rows = None
try:
cur.execute(sql)
rows = cur.fetchall()
except Exception as e:
print(e)
self.connection.rollback()
finally:
cur.close()
self.connection.commit()
self.connection.close()
return rows
This then lets Flask create a connection for each request, and close it at the end.
So any method I call uses this pattern. This also allows for multiple requests etc (websites do that)
I'm not saying its perfect, but for every request to the db, you are creating, and closing the db connection so it should never time out.
This is very basic, and again, you could combine it with the ping() and allow this to create a new connection etc
Upvotes: 0
Reputation: 5372
I don't believe this is an issue with Flask/pymysql as much as it is a symptom of your MySQL timeout configurations. I assume this is some sort of cloud database instance?
Have a look at this:
https://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep
And I'd post your question there with specifics about your setup and you may be able to get a configuration answer.
A Python solution would be to use something like sqlalchemy & flask-sqlalchemy then set the configuration variable SQLALCHEMY_POOL_RECYCLE = 3600
to recycle connections after an hour (or whatever value you desire). Alternatively if you don't want to add that much bulk to your project you could implement a connection "timer" feature to recycle the connection yourself in the background:
from datetime import datetime, timedelta
class MyConnectionPool(object)
"""Class that returns a database connection <= 1 hour old"""
refresh_time = timedelta(hours=1)
def __init__(self, host, user, pass):
self.host = host
self.user = user
self.pass = pass
self.db = self._get_connection()
@property
def connection(self):
if self.refresh <= datetime.now():
self.db = self._get_connection()
return self.db
def _get_connection(self):
self.refresh = datetime.now() + self.refresh_time
return pymysql.connect(
host=self.host,
user=self.user,
passwd=self.pass
)
Upvotes: 2