I am trying to play around with deploying Flask application. When I run the code using docker swarm on Amazon's EC2 I started to get following errors:
Error message:
sqlalchemy.exc:OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.[SQL: SELECT visitor."ID" AS "visitor_ID", visitor.username AS visitor_username, visitor.visits AS visitor_visits FROM visitor](Background on this error at: http://sqlalche.me/e/e3q8)
Stack trace
Traceback (most recent call last):
File "/usr/local/bin/gunicorn", line 8, in <module>
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/wsgiapp.py", line 58, in run
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/base.py", line 228, in run
File "/usr/local/lib/python3.7/site-packages/gunicorn/app/base.py", line 72, in run
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 202, in run
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 545, in manage_workers
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 616, in spawn_workers
File "/usr/local/lib/python3.7/site-packages/gunicorn/arbiter.py", line 583, in spawn_worker
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/base.py", line 140, in init_process
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/sync.py", line 123, in run
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/sync.py", line 67, in run_for_one
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/sync.py", line 29, in accept
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/sync.py", line 134, in handle
File "/usr/local/lib/python3.7/site-packages/gunicorn/workers/sync.py", line 175, in handle_request
File "/usr/local/lib/python3.7/site-packages/newrelic/api/wsgi_application.py", line 665, in _nr_wsgi_application_wrapper_
File "/usr/local/lib/python3.7/site-packages/newrelic/api/wsgi_application.py", line 193, in __init__
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2463, in __call__
File "/usr/local/lib/python3.7/site-packages/newrelic/api/wsgi_application.py", line 555, in _nr_wsgi_application_wrapper_
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
File "/usr/local/lib/python3.7/site-packages/newrelic/hooks/framework_flask.py", line 45, in _nr_wrapper_handler_
File "/app/src/views.py", line 5, in index
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3233, in all
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3389, in __iter__
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3414, in _execute_and_instances
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
File "/usr/local/lib/python3.7/site-packages/newrelic/hooks/database_psycopg2.py", line 51, in execute
File "/usr/local/lib/python3.7/site-packages/newrelic/hooks/database_dbapi2.py", line 25, in execute
Initially I thought that this might be a result of not closing db connections after using them, but when I added db.session.close() the error still occurs.
Full code can be found here, the most important parts are:
views.py file:
from models import Visitor, db
def index():
visitors = Visitor.query.all()
response = [
{"id": visitor.ID, "username": visitor.username, "visits": visitor.visits}
for visitor in visitors
]
db.session.close()
return {"results": response}
def increment_visits(username: str) -> dict:
if username == "favicon.ico":
return {}
visitor = Visitor.query.filter_by(username=username).first()
if visitor is None:
visitor = Visitor(username=username, visits=1)
db.session.add(visitor)
else:
visitor.visits += 1
db.session.commit()
return {"id": visitor.ID, "username": visitor.username, "visits": visitor.visits}
main.py file:
import sys
from flask import Flask
from config import SQLALCHEMY_DATABASE_URI, HOST, PORT, DEBUG, DB_POOL_SIZE
from models import db
def make_app() -> Flask:
flask_app = Flask(__name__)
flask_app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
flask_app.config['SQLALCHEMY_POOL_SIZE'] = DB_POOL_SIZE
add_urls(flask_app)
db.init_app(app=flask_app)
return flask_app
def add_urls(flask_app: Flask):
from views import index, increment_visits
flask_app.add_url_rule('/', view_func=index)
flask_app.add_url_rule('/<username>/', view_func=increment_visits)
app = make_app()
if __name__ == '__main__':
if 'createdb' in sys.argv:
app.app_context().push()
db.create_all()
else:
app.run(host=HOST, port=PORT, debug=DEBUG)
models.py:
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Visitor(db.Model):
ID = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
visits = db.Column(db.Integer(), default=1)
Docker services:
ubuntu@srv1:~$ docker stack ps --filter "desired-state=running" demo
ID NAME IMAGE NODE DESIRED STATE CURRENT STATE ERROR PORTS
frbdra27cdcb demo_web.1 gonczor/aws-simple-app:prod srv1 Running Running 7 hours ago
0by6yhvr9n4a demo_nginx.1 gonczor/aws-simple-app-nginx:prod srv1 Running Running 3 days ago
ym2t3we6r5b1 demo_db.1 postgres:11 srv1 Running Running 4 days ago
luwgpr3jnsj8 demo_web.2 gonczor/aws-simple-app:prod srv1 Running Running 7 hours ago
I have no clue where this error might come from, so I am happy to provide any further details.
EDIT
Output of requested command:
simple_app=# SELECT sum(numbackends) FROM pg_stat_database;
sum
-----
2
(1 row)
I think I found a solution, though I have very vague why it works. I found an answer in this reddit post, so here we go.
The error mainly occurred after a long idleness. When I was flooding the app with requests during benchmarking nothing happened. It seems that some connections were kept by SQLAlchemy for too long. I lowered the POOL_RECYCLE_TIME to 10 minutes and updated the settings format to avoid deprecation that will be introduced in version 3.0 of Flask-SQLAlchemy.
DB_POOL_SIZE = int(os.environ.get('DB_POOL_SIZE', '10'))
DB_POOL_RECYCLE = int(os.environ.get('DB_POOL_RECYCLE', '60'))
SQLALCHEMY_ENGINE_OPTIONS = {
'pool_size': DB_POOL_SIZE,
'pool_recycle': DB_POOL_RECYCLE,
}
As I say, I don't fully understand why this was a problem, but after introducing those changes no errors occurred. Anyway comments and links to materials explaining this issue are welcome.