<div dir="ltr">Hi Kristof,<div><br></div><div>It has been a long time since I looked at this, but aren't we just caching the DB on the request object, thus ensuring there is one opened DB, and hence session, per request? The V4DB is essentially just wrapping the session.</div><div><br></div><div> - Daniel</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Sun, May 10, 2015 at 11:21 AM, Kristof Beyls <span dir="ltr"><<a href="mailto:kristof.beyls@arm.com" target="_blank">kristof.beyls@arm.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-GB" link="blue" vlink="purple"><div><p class="MsoNormal">Daniel, Tobias, Renato and myself have been looking a little bit at the potential underlying reason<br>for why <a href="http://llvm.org/perf/" target="_blank">http://llvm.org/perf/</a> is instable, and have found some clues. I want to share them here<br>to give people with more experience in the frameworks used by LNT (flask, sqlalchemy, wsgi, …)<br>a chance to check if our reasoning below seems plausible.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Daniel noticed the following backtrace in the log after  <a href="http://llvm.org/perf" target="_blank">http://llvm.org/perf</a> started giving “Internal Server Error”<br>again:<u></u><u></u></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">2015-05-08 22:57:05,309 ERROR: Exception on /db_default/v4/nts/287/graph [GET] [in /opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py:1423]<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">Traceback (most recent call last):<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py", line 1817, in wsgi_app<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    response = self.full_dispatch_request()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py", line 1477, in full_dispatch_request<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    rv = self.handle_user_exception(e)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py", line 1381, in handle_user_exception<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    reraise(exc_type, exc_value, tb)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py", line 1475, in full_dispatch_request<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    rv = self.dispatch_request()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/Flask-0.10.1-py2.7.egg/flask/app.py", line 1461, in dispatch_request<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return self.view_functions[rule.endpoint](**req.view_args)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/ui/decorators.py", line 67, in wrap<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    result = f(**args)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/ui/views.py", line 385, in v4_run_graph<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    ts = request.get_testsuite()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/ui/app.py", line 76, in get_testsuite<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    testsuites = self.get_db().testsuite<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/ui/app.py", line 55, in get_db<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    self.db = current_app.old_config.get_database(g.db_name, echo=echo)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/config.py", line 148, in get_database<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return lnt.server.db.v4db.V4DB(db_entry.path, self, echo=echo)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/LNT-0.4.1dev-py2.7.egg/lnt/server/db/v4db.py", line 108, in __init__<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    .filter_by(id = lnt.testing.PASS).first()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/query.py", line 2334, in first<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    ret = list(self[0:1])<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/query.py", line 2201, in __getitem__<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return list(res)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/query.py", line 2405, in __iter__<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return self._execute_and_instances(context)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/query.py", line 2418, in _execute_and_instances<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    close_with_result=True)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/query.py", line 2409, in _connection_from_session<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    **kw)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/session.py", line 846, in connection<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    close_with_result=close_with_result)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/session.py", line 850, in _connection_for_bind<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return self.transaction._connection_for_bind(engine)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/orm/session.py", line 315, in _connection_for_bind<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    conn = bind.contextual_connect()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/engine/base.py", line 1737, in contextual_connect<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    self.pool.connect(),<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/pool.py", line 332, in connect<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    return _ConnectionFairy._checkout(self)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/pool.py", line 630, in _checkout<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    fairy = _ConnectionRecord.checkout(pool)<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/pool.py", line 433, in checkout<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    rec = pool._do_get()<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">  File "/opt/venv/perf/lib/python2.7/site-packages/SQLAlchemy-0.9.6-py2.7.egg/sqlalchemy/pool.py", line 945, in _do_get<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">    (self.size(), self.overflow(), self._timeout))<u></u><u></u></span></p><p style="margin:0cm;margin-bottom:.0001pt"><span style="font-size:8.5pt;font-family:"Menlo","serif"">TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30<u></u><u></u></span></p><p class="MsoNormal"><u></u> <u></u></p><p>After browsing through the sqlalchemy documentation and bits of the LNT implementation,<br>it seems so far that the following pieces may be the key parts that cause the problem<br>shown in the log.<u></u><u></u></p><p><u></u> <u></u></p><p>The SQLAlchemy documentation seems to recommend to have a sqlalchemy session per web<br>request. Looking at the following pieces of LNT, I got the impression that instead a<br>session is shared between many or all requests:<u></u><u></u></p><p><u></u> <u></u></p><p>From ui/app.py, it shows Request.get_db() basically caches get_database from "config":<u></u><u></u></p><p>...<u></u><u></u></p><p>class Request(flask.Request):<u></u><u></u></p><p>...<u></u><u></u></p><p>    def get_db(self):<u></u><u></u></p><p>...<u></u><u></u></p><p>        if self.db is None:<u></u><u></u></p><p>            echo = bool(self.args.get('db_log') or self.form.get('db_log'))<u></u><u></u></p><p>            self.db = current_app.old_config.get_database(g.db_name, echo=echo)<br>...<u></u><u></u></p><p>        return self.db<u></u><u></u></p><p><u></u> <u></u></p><p>in config.py, it is shown that get_database returns a V4DB object by calling a constructor:<u></u><u></u></p><p>...<u></u><u></u></p><p>    def get_database(self, name, echo=False):<u></u><u></u></p><p>...<u></u><u></u></p><p>        # Instantiate the appropriate database version.<u></u><u></u></p><p>        if db_entry.db_version == '0.4':<u></u><u></u></p><p>            return lnt.server.db.v4db.V4DB(db_entry.path, self,<u></u><u></u></p><p>                                           db_entry.baseline_revision,<u></u><u></u></p><p>                                           echo)<br>...<u></u><u></u></p><p><u></u> <u></u></p><p>This constructor is in db/v4db.py:<u></u><u></u></p><p>...<u></u><u></u></p><p>class V4DB(object):<u></u><u></u></p><p>...<u></u><u></u></p><p>    def __init__(self, path, config, baseline_revision=0, echo=False):<u></u><u></u></p><p>...<u></u><u></u></p><p>        self.session = sqlalchemy.orm.sessionmaker(self.engine)()<u></u><u></u></p><p>...<u></u><u></u></p><p>        # Add several shortcut aliases.<u></u><u></u></p><p>        self.add = self.session.add<u></u><u></u></p><p>        self.commit = self.session.commit<u></u><u></u></p><p>        self.query = self.session.query<u></u><u></u></p><p>        self.rollback = self.session.rollback<br>...<u></u><u></u></p><p><u></u> <u></u></p><p><u></u> <u></u></p><p><u></u> <u></u></p><p>It seems like a single session object is created in this constructor that will ultimately<br>be shared across all Requests. It seems that instead, the request.get_db method should<br>create a new session for each request. And close that session when the request is finalized<br>which probably needs to be done by hooking into something Flask-specific.<u></u><u></u></p><p><u></u> <u></u></p><p>The self.add and following lines in the constructor show that it probably will be<br>non-trivial to refactor code so that there will not be a single session per v4db object.<u></u><u></u></p><p><u></u> <u></u></p><p>We’re not sure if making separate sessions per Request is going to solve the <a href="http://llvm.org/perf" target="_blank">http://llvm.org/perf</a><br>instability problems; but that’s the best idea we’ve got so far. <u></u><u></u></p><p><u></u> <u></u></p><p>Thanks,<span class="HOEnZb"><font color="#888888"><u></u><u></u></font></span></p><span class="HOEnZb"><font color="#888888"><p><u></u> <u></u></p><p>Kristof<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p></font></span></div></div></blockquote></div><br></div>