Peewee: 0.5 second connection overhead

[willgdjones]

I'm having some issues with my connection requests ever since I have followed the best practices defined in the docs:

Code in docs:

from flask import Flask
from peewee import *

database = SqliteDatabase('my_app.db')
app = Flask(__name__)

# This hook ensures that a connection is opened to handle any queries
# generated by the request.
@app.before_request
def _db_connect():
    database.connect()

# This hook ensures that the connection is closed when we've finished
# processing the request.
@app.teardown_request
def _db_close(exc):
    if not database.is_closed():
        database.close()

After this change, all handled requests have a 0.5 second overhead in response time.

I'm currently investigating and was wondering if this was something that could be somehow caused by Peewee's handling/closing of new connections, or if it is something else.

Thanks for your help.

Will

[NicolasCaous]

Your snippet is opening and closing a database connection at every request. There is an overhead time built into it (You can't escape it). Maybe you should consider altering the way in which you manage connections. Something like:

Open a connection at startup.

On @app.before_request verify that the connection wasn't closed by the database (or by any other reason). If it's closed, open it again, otherwise, recycle the connection.

You may be interested in a pooled connection.

Edit

This is a bad advice, see @coleifer comment for clarification.

[willgdjones]

Hi @NicolasCaous - thanks for those thoughts and link.

I'll take your first suggestion. Do you know of the helper that checks if the connection is open? I am searching for it now.

[NicolasCaous]

Do you know of the helper that checks if the connection is open?

This one should do it.

[coleifer]

The reason for opening and closing connections has to do with thread-safety and being explicit. Most wsgi apps will use either threads or green-threads. Peewee's Database implementation, by default, stores connection state in a threadlocal. Opening a connection when the request starts is a good idea because it is explicit -- and it won't fail some arbitrary time later when the application actually tries to query the db. Closing a connection when the request is done is important so that you release the resources (which are associated with the thread that made the request). It is important to clean these up!

Now, 0.5 seconds is a lot of overhead for establishing a db connection...I would suggest you have some serious issues in your network. But it should be on the order of milliseconds. See this blog post for a bit of discussion.

That being said, you can avoid the setup cost by using a connection pool. Peewee provides these, and they use the same APIs as the regular non-pooled dbs, so you can drop it in.

When using a connection pool with peewee it is absolutely imperative that you close() the connection when you're done using it (e.g., in the request teardown). Closing it releases the connection so it can be used again by a subsequent request. If you do not close it, your pool will grow and grow, which is bad.

So: follow the best practices. They are there for a reason.

You can mitigate the setup costs by using a connection pool, but you still need to call connect and close so the resources are recycled properly!

Lastly, 500ms is too much, I think you probably should investigate your network setup -- somethings very wrong there.

[coleifer]

Regarding @NicolasCaous comment,

On @app.before_request verify that the connection wasn't closed by the database (or by any other reason). If it's closed, open it again, otherwise, recycle the connection.

Unless your WSGI server is single-threaded (ie no python threads, no green threads), this is bad advice.

As I said, Peewee stores connection state in a threadlocal. So if a greenlet is spawned to handle each request, it will have no visibility into the connection(s) being used by other greenlets, so it will always appear that the connection has not been opened.

The proper fix is to use a connection pool. http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pool

[willgdjones]

Hi @coleifer - thank you for your detailed comments!

I will go for connection pooling - it's clear that it is the best solution here.

Regarding the 500ms overhead, I'm not sure where this could originate from. I'm running a Flask server on Heroku on 2 standard-1x machines that are running 2 web processes and connecting to a Postgres server on Amazon RDS. It could be something to do with that.

[willgdjones]

I see that the standard connection parameters look something like this:

db = PooledPostgresqlExtDatabase(
    'my_app',
    max_connections=32,
    stale_timeout=300,  # 5 minutes.
    user='postgres')

Is it possible to use a database connection string here?

Thanks!

[NicolasCaous]

Yes, you can use a connection string to configure the pooled database connection.

http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#connect

The kwargs are parsed from the URL query parameters.

[NicolasCaous]

Regarding heroku, are you using your own RDS database or the database provided by heroku?

[willgdjones]

I'm using my own RDS database.

[coleifer]

Oh no...that's certainly the problem. You're connecting to your database over the public internet, which I don't think is ever a good idea. That introduces a lot of latency as every query has to go out across the internet. Typically you'll deploy your database in the same network as your application server, and you'll never expose it to the public internet.