Issue: Some issues with connection pooling
Author: arnuschky
State: closed
Created: 2015-05-21 10:50:33

Using latest peewee with a pooled mysql db (I know, sorry... ;) ) It's a single-thread multi-greenlet application.

What we see is that the size of the connections `inuse` monotonically increases, until it hits `max_connections`. Still debugging that right now...

`stale_timeout` is somewhat of a misnomer, right? If I read the code correctly, it's more of a maximum age after which a connection is closed no matter its usage, right?

A curious observation is that the pool seems to use multiple connections to the database but the database reports only a single connection.


`lsof` confirms that the app opens only a single connection to the database.


This is getting stranger still. I have a different connection for every greenlet, and the low-level mysql code seems to create a new connection object to the database for each instance (as expected). However, `lsof` is showing only a single connection. 

If I manually open two connections via `mysql.connect` using the python interpreter, `lsof` shows two connections.

What am I missing here?

BTW, we're running with `threadlocals=True`


> If I read the code correctly, it's more of a maximum age after which a connection is closed no matter its usage, right?

Yes.

> multi-greenlet application

I have to think this is the issue / cause. Can you share the relevant parts of your code? Are you calling `gevent.monkey.patch_all()`, or are you being more selective?


I'm just using gevent-socketio within gunicorn (both most recent). Gunicorn's gevent worker does the monkey patching: https://github.com/benoitc/gunicorn/blob/master/gunicorn/workers/ggevent.py#L61


Ahh, actually I think this is the problem. In your code, the peewee module's instance of the `threading` module may not be patched at the time it is imported. This means that peewee's `Database` classes will use a `threading.local` instead of a _greenlet local_.

To fix this, in your WSGI script or the entry point of your application, at the very top of the module, first import, try adding:

``` python
from gevent import monkey; monkey.patch_all()
```

Let me know if that fixes the issue.


I've worked many hours on playhouse. It needs many cares to continue
developing. Close all connection does not work correctly because of Id of
resource not representing original mysql connection. To see MySQL
connections run command "show full processlist;"
Certainly some connections are created with pool but are not closed with
it. It means some connections stay in _in_use or don't close correctly! See
sleep times in process list of MySQL. I'm using gevent pool for some tasks
with peewee and this is my observation. Configure your MySQL to close sleep
connections with more than some certain time .e.g 200 seconds. Playhouse
picks up one connection from pool for each thread and if no connection is
available new connection will be created.
On May 21, 2015 6:31 PM, "Charles Leifer" notifications@github.com wrote:

> Ahh, actually I think this is the problem. In your code, the peewee
> module's instance of the threading module may not be patched at the time
> it is imported. This means that peewee's Database classes will use a
> threading.local instead of a _greenlet local_.
> 
> To fix this, in your WSGI script or the entry point of your application,
> at the very top of the module, first import, try adding:
> 
> from gevent import monkey; monkey.patch_all()
> 
> Let me know if that fixes the issue.
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/608#issuecomment-104286302.


Can't do, if I add `patch_all` to the WSGI script, the worker doesn't start at all with the most recent gunicorn 19.x. (We had `patch_all` in our WSGI script in a previous version.)


@arnuschky maybe `patch_thread` then instead?


@emamirazavi, sorry to hear you're having issues with the pool module. The module is unit-tested extensively (378 lines of tests compared to 185 of the module itself). It is also quite simple from an implementation standpoint. I'm inclined to believe that these bugs may be at the application level and not in peewee or the pool (especially because you mentioned using gevent and that requires some care to get right). If you can provide me a test-case showing otherwise that would be great.


@coleifer, thank you, that did the trick! It's now creating multiple real connections to the db.

It remains that the `_in_use` dictionary never shrinks, but this might be now due to my code. Peewee is creating many connections for a single socket of gevent-socketio: one for the request, one for the main greenlet, and then one for each greenlet that I launch. My problem is that I have troubles terminating all of them properly once the socket closes.

Is it somehow possible to share a database connection among several greenlets, even when running with threadlocals? This all-or-nothing sharing is a bit limiting in my case...


I can confirm that connections are now properly removed from `_in_use` on close; it's just the combination of Peewee, gevent-socketio, and my code that causes only a single connection to be properly closed while the others linger.

To repeat my question above: is it somehow possible to share a database connection among several greenlets, even when running with threadlocals? This all-or-nothing sharing is a bit limiting in my case.


> Is it somehow possible to share a database connection among several greenlets, even when running with threadlocals? This all-or-nothing sharing is a bit limiting in my case...

Not easily, no, though I'm not sure why you'd want to. The main greenlet really shouldn't need a connection unless of course you're making queries from it. I obviously don't know anything about your code or your app, so it's hard for me to speculate.

> It remains that the _in_use dictionary never shrinks, but this might be now due to my code.

The `_in_use` should grow when you open a connection and shrink when you close one. The fact that you're using socketio may have something to do with the odd behavior, but I really don't know.

I really like gevent, but unfortunately you need to know something of the implementation to reason about its behavior.

I'm closing this issue as invalid, since the problem was monkey-patches not being applied correctly during import time, and this isn't an issue with peewee.


> To repeat my question above: is it somehow possible to share a database connection among several greenlets, even when running with threadlocals? This all-or-nothing sharing is a bit limiting in my case.

Again, I'm not sure how your app works, or when you are opening/closing connections, or the interaction between the socketio greenlets and the rest of the app.

In a typical web app (using gevent or otherwise), peewee stores the conn on a threadlocal (or greenlet-local). Additionally, typical web apps will spawn a thread or greenlet to handle each request. So request comes in to server, server spawns thread/greenlet, app opens connection and generates HTTP response, app closes DB connection after sending response, server shuts down greenlet. Socketio adds another layer of complexity and I'm just not familiar with it.


Ok, thank you for your answers. 

To rephrase my last question more precisely: currently, when using threadlocals, Peewee automatically creates a new connection when I call `connect()` on my Peewee db object. So whenever I use Peewee with multiple greenlets, Peewee always creates a new connection for each greenlet. To get around this, my only choice is to pass around an already connected instance of my database object, right?


> currently, when using threadlocals, Peewee automatically creates a new connection when I call connect()

Yes, that is the correct behavior. If you are using a pool, then the connections will be managed by the pool, but the drift of your statement is correct. If you're using the pool, only `max_connections` will be opened.

> To get around this, my only choice is to pass around an already connected instance of my database object, right?

Wait, I thought you were using the connection pool because you _wanted_ multiple connections? I'm confused... What exactly do you want?


Every thread has its own connection. You must have one connection for each
thread. Thread local brings you this ability. Each time you call connect in
pool mode, one ready connection from pool will be returned to you or if no
connection exists, new connection will be created and will be returned.
On May 21, 2015 10:17 PM, "Charles Leifer" notifications@github.com wrote:

> currently, when using threadlocals, Peewee automatically creates a new
> connection when I call connect()
> 
> Yes, that is the correct behavior. If you are using a pool, then the
> connections will be managed by the pool, but the drift of your statement is
> correct. If you're using the pool, only max_connections will be opened.
> 
> To get around this, my only choice is to pass around an already connected
> instance of my database object, right?
> 
> Wait, I thought you were using the connection pool because you _wanted_
> multiple connections? I'm confused... What exactly do you want?
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/608#issuecomment-104369523.


I'm using Flask, Gunicorn and Gevent thread pool. I'm just closing
connection on flask tears down, because pooling automatically open one
connection. My problem is that sometimes one connection in pool stay with
no assignment and count of this [sleep] connections up to 150 after a day!
Connection in playhouse.pool just is saved in thread local, and in use or
connections properties don't know MySQL connection resource to handle
connection correctly. I should tell that this observation read after
installing Gunicorn and running my app as a preloaded application. Any
ideas?


> You must have one connection for each
> thread.

If you specify `threadlocals=False` when initializing your database then this is not true, one conn will be shared.


What I want is basically this: each socket worker (which is a greenlet) has a single connection which is shared with its sub-greenlets.

What I have is that my app sets up a new db connection before the request, as usual. However, this happens in the main gunicorn worker and not the socket worker greenlet. Hence, there's a new connection for the socket greenlet, which starts a few sub-greenlets that each get a new db instance as well. Closing connections later is even more messy depending on whether there was an exception and in which greenlet it occurred.

The first problem with the app creating a new connection is easy enough to fix but the second is harder: how can the sub-greenlets share a single connection with their parent, the socket greenlet?


I think you will have to write your own connection management code in this case, since it's rather unique.


It seems so. :) Thanks for all your help!