Sqlalchemy doesn't seem to manage thread-unsafe monetdb connections very well

[aris-koning]

Hi,

In our application we are trying to launch multiple parallel monetdb queries through sqlalchemy using scoped_session to generate thread_local session objects. However it seems that pymonetdb / sqlalchemy-monetdb cannot handle the parallel queries very well. It either hangs or give all sorts of exceptions like "connection already closed" and logical errors on the query response. This can already happen with four parallel queries. It almost always hangs with ten or more parallel queries. I am comparing this with postgresql/psycopg2 which doesn't show any problems. See my test script

import concurrent.futures
import sys

from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine

"""
Usage: python test.py (m|p) (nr_of_queries)
m := monetdb/pymapi
p := postgresql/psycopg2


make sure you have MonetDB running (I was using v11.29.3 "Mar2018")
and a postgresql server. (I was using  PostgreSQL 9.5.14 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit)

On both servers make sure the database 'devdb' is present and accesible.

make sure you have installed
- pymonetdb==1.0.6
- SQLAlchemy==1.0.11
- sqlalchemy-monetdb==0.9.3
- psycopg2==2.7.5
"""

def _pool_task(sql, Session):

    result = None
    session = Session()
    result = session.execute(sql)
    return result



if sys.argv[1] == "m":
    engine = create_engine('monetdb:///devdb')
elif sys.argv[1] == "p":
    engine = create_engine('postgresql://frank:frankspassword@localhost/devdb')
else:
    raise ValueError("Unknown database {}".format(sys.argv[1]))

# The scoped_session object 'Session' is a factory to generate thread_local sqlalchemy.orm.session.Session object
Session = scoped_session(sessionmaker(autocommit=False, autoflush=True, bind=engine))

nr_queries = int(sys.argv[2])

queries = ["select sum(value) + {} from generate_series(1, 100) as foo(value);".format(i) for i in range(0, nr_queries)]

results = []

with concurrent.futures.ThreadPoolExecutor(max_workers=len(queries)) as executor:
    for future in concurrent.futures.as_completed([executor.submit(_pool_task, query, Session) for query in queries]):
        results.append(future.result())

data = [c.fetchall() for c in results]

print(data)

[gijzelaerr]

might be related to MonetDB/pymonetdb#37

unfortunately, i don't have time/funding to look too much into this.

[aris-koning]

Ok, thanks for the feedback. I'll mention this to Martin.