[ZODB-Dev] Re: Connection pool makes no sense

[Юдыцкий Игорь Владисла]

Thanx to everyone who has taken part of this discussion. Tim, Dieter,
Dave. Soory for keeping silence, it gets messy just before New Year
comes.

Back to what i have played with after your advice.

1. I've tried to add gc.collect() in App.ZApplication.Cleanup.__del__(),
so when the connection is released we do gc.collect. And it works. But
makes server too slow.
2. I've tried to gc.set_threshold() in an ZApplicationWrapper.__init__()
to perform gc cycles more often and watched gc.cycles by
gc.set_debug(gc.DEBUG_STATS) - result zope starts very slow and works
very slow, it cleans unused connections, but not all depending on
thresholdX values.
3. I've tried to put gc.collect() to _reduce_size method. It works too.

But all this does not cause the required behavor. 
#1 cleans the best way - but application becomes unusable because of low
latensy.
#2 Better then #1 but slows down zope startup and causes often
application freezing while it cleans garbage. And not all connection
objects are cleaned while collecting... some remain opened for an
unpredictable amount of time.
#3 Does clean connections but slows down the application right after
activity gets lower, so in pool.available we have more then desired
number of connection instances, pool tries to clean them up and freezes
the application and this makes another wave of growing pool because of
application speed has become lower...

So there is no apropriate way relying on gc in my view.

Another problem is an immediate cleaning of pool.available list right
after we get a little low request activity. There is no reason to
beleive that activity will not get higher in a few seconds, but we are
already cleaning connection poll and may be launching gc.collect, that
slows the whole application. Having bigger pool_size good to avoid often
poll shrinking while activity burst.
And from the other hand it's not likely to have big poll_size because
while low activity period there is no need to keep so many opened
connections to ZODB and especially to RDB.

So my understanding to have idle period rather ten pool_size.
In case when we do not need any RDB connectio or any other connection
pool may shrink to one or zero connections (one is better not to slow
latency and having cache of objects). In case of high request activity
pool has the size of number of connections that required to serve all
parallel requests depending on application speed.
And when we have activity vector down we are cleaning the pool according
to the logic of idle period. If connection is pushed more then idle
period ago then we do not need it any more.
I've implemented it this way:

    def push(self, c):
        assert c not in self.all
        assert c not in self.available
        c._pushed = time()
        self.all.add(c)
        self.available.append(c)

   def repush(self, c):
        assert c in self.all
        assert c not in self.available
        c._pushed = time()
        self.available.append(c)
        self._reduce_size()

    def _reduce_size(self):
        t = time()
        for i in xrange(len(self.available)):
            if t - self.available[i]._pushed > 60: # here we nedd to use
configuration parameter value, but for testing that will do...
                c = self.available.pop(0)
                self.all.remove(c)
            else:
                break # since we have lifo stack there is no need to
check other connections

It works fine, if we go collect garbage of course.

So I can't use invoking of gc.collect() in my production application
instance, the reasons are given upper. And to close unused connections i
use another function that knows RDB DA handle oid and calls it's close()
method before self.all.remove(c) is called. But it's no good as you can
see. So i'm hoping that you will find the way to reimplement connection
class to let him die not waiting for it's turn comes in many many gc
cycles.

Once again i would like to thank everybody for getting into my problem,
willing to help. Hope that new versions of ZODB will have more flexible
mechanism of forgetting connections, likely having idle period parameter
rather ten pool_size.

Happy New Year to everyone who is reading this!!!
Best wishes to you! I wish you to be healthy and all your family members
too! Long live ZODB!
:)

P.S.
Call me Pitcher, i don't like 'OP' name.

В Сбт, 31/12/2005 в 09:20 +0100, Dieter Maurer пишет:
> Tim Peters wrote at 2005-12-30 14:51 -0500:
> >[Dieter Maurer]
> >> They did not tell us about their application. But, Zope's database
> >> adapters work like this. They use the ZODB cache (and its pool) as an RDB
> >> connection pool. Thus, if the ZODB caches are not released, the RDB
> >> connections won't.
> >
> >I believe you, but I'm not sure what to make of it; for example,
> >
> >1. The OP is the only person who has this problem?
> >
> >2. Other people have this problem too, and don't know what to do about
> >   it either, but never complain?
> 
> I expect (but the original poster may step in) that the problem
> could occur in Zope only when the number of threads exceeds
> the pool size (or additional connections are used in an application
> specific way) as otherwise, there are no "dropped" connections.
> 
> Because formerly, it made no sense to have more worker threads than
> that given by the pool size, this situation is likely to occur
> rarely.
> 
> > ...
> >>> If not, you may have better luck on the zope-db list (which is
> >>> devoted to using other databases with Zope):
> >
> >> The problem is not with the RDB but with the ZODB connections that are
> >> magically not garbage collected. He will probably not find help on
> >> "zope-db".
> >
> >That suggestion was based on a guess that #3 (above) is most likely.  Of
> >course I don't know, but #1 and #2 seem unlikely on the face of it.  If
> >other people using RDB don't have this problem, then zope-db is the right
> >place to ask how they manage to avoid it.
> 
> If the poster has no evidence that the ZODB connections are definitely kept
> but just see that the relational database connections remain open,
> the reason might indeed be at a completely different place:
> 
>   There are some reports on "zope-db" about DAs leaking relational
>   database connections.
>   
>   The problem is not related to ZODB connection handling.
>   Instead, the relational database connection is kept open
>   even if the DA object was invalidated (and especially cleared).
> 
> 
> We observed such behaviour with Zope 2.7/ZODB 3.2 and "ZPsycopgDA".
> 
>    When we used "resetCaches" (which should in principle release
>    the old caches (I also added an "minimizeCache" because
>    the cyclic gc does not work with Zope 2.7's ExtensionClass objects)),
>    a new set of Postgres connections was opened without
>    the old ones being closed.
> 
>    We worked around this problem by:
> 
>      *  avoiding to use "resetCaches"
> 
>      *  restarting Zope once a weak to get rid of
>         stale Postgres connections
> 
> > ...
> >OTOH, if that's not
> >what's going on here, I'd expect to have heard about this here more than
> >once in the last 5 years ;-)
> 
> The older ZODB code (ZODB 3.2 and before) was seriously flawed
> with respect to cache release handling.
> 
> Fortunately, it was very rare that caches need to be released.
> 
> I found the flaws because we used temporary connections extensively
> and, of course, their caches need to go away with the temporary
> connection. I had a hard fight to get rid of the memory leaks
> induced by those flaws.
> 
> 
> Now (ZODB 3.4 and above) caches might get released more often.
> True, the garbage collector now has a chance to collect cycles
> including "ExtensionClass" objects, but it is very easy to
> defeat the GC -- an object with a "__del__" method is sufficient.
> 
> > Perhaps because the OP is unique in allowing
> >hundreds (or thousands -- whatever) of Connections to be active
> >simultaneously?  Don't know.
> 
> He did not say that.
> 
>   If his hypothesis is indeed true and some connections exceeding
>   the pool size are kept indefinitely, then already slightly
>   exceeding the pool size may lead to an unbound number of connections
>   provided the "exceedance" occurs frequently enough.
> 
> 
> >I suggested before that forcing calls to gc.collect() would give more
> >evidence.  If that doesn't help, then it's most likely that the application
> >is keeping Connections alive.  Since I'm not familiar with the RDB code, I
> >suppose it's even possible that such code uses __del__ methods, and creates
> >cycles of its own, that prevent cyclic gc from reclaiming them.  In that
> >case, there are serious problems in that code.
> 
> There is also another "gc" attribute holding the garbage cycles
> not released. The poster may examine them to check whether they
> contain indeed ZODB connection objects.
>