Python, sqlalchemy: how to improve performance of encrypted sqlite database?

Question

I have a simple service application: python, tornado web server, sqlite database. The database is encrypted.

The problem is that processing even very simple http request takes about 300msec.

From logs I can see that most of that time takes processing of the very first sql request, no matter how simple this first request is. Subsequent sql requests are processed much faster. But then server starts processing next http request, and again the first sql request is very slow.

If I turn off the database encryption the problem is gone: processing time of sql requests does not depend on if the request is first or not and my server response time decreases by factor 10 to 15.

I do not quite understand what's going on. Looks like sqlalchemy reads and decrypts the database file each time it starts new session. Is there any way to workaround this problem?

Answer 1

Due to how pysqlite, or the sqlite3 module, works SQLAlchemy defaults to using a NullPool with file-based databases. This explains why your database is decrypted per each request: a NullPool discards connections as they are closed. The reason why this is done is that pysqlite's default behaviour is to disallow using a connection in more than one thread, and without encryption creating new connections is very fast.

Pysqlite does have an undocumented flag check_same_thread that can be used to disable the check, but sharing connections between threads should be handled with care and the SQLAlchemy documentation makes a passing mention that the NullPool works well with SQLite's file locking.

Depending on your web server you could use a SingletonThreadPool, which means that all connections in a thread are the same connection:

engine = create_engine('sqlite:///my.db',
                       poolclass=SingletonThreadPool)

If you feel adventurous and your web server does not share connections / sessions between threads while in use (for example using a scoped session), then you could try using a different pooling strategy paired with check_same_thread=False:

engine = create_engine('sqlite:///my.db',
                       poolclass=QueuePool,
                       connect_args={'check_same_thread':False})