[Netarchivesuite-devel] About the DBConnect class, running out of connections

Nicolas Giraud nikokode at gmail.com
Mon Mar 28 10:47:41 CEST 2011


Hi again,

So I've started implementing an alternative implementation of DBConnect,
using c3p0, but the prblem runs much deeper actually. As nowhere in the DAO
code we ever close connections, our DAO code is not proper and we very
quickly exhaust the pool, connections are never returned to the pool so we
cannot effectively use a pool.

So we have a real and critical issue with the way our DAO code is designed.
Currently we have two options :

- have a kind of connection reaper process in DBConnect that closes idle
connections (provided we are able to detect this properly)
- refactor DBConnect to use a connection pool and refactor all DAO code so
that a connection is returned to the pool (by closing it) at the end of
every DAO business method.

In my opinion only solution 2 is viable and proper.

Another alternative option is to keep DBConnect roughly the way it is, but
replace this WeakHashMap by a standard HashMap, and have a reaper process
that releases connections owned by dead threads.

I would be interested to have your opinion on this, meanwhile I will open a
Jira issue.

Best regards,

Nicolas Giraud

2011/3/25 Nicolas Giraud <nikokode at gmail.com>

> Ok, thanks for the tip.
>
> Actually I ran into this problem on my dev platform since 3.13 if I
> remember correctly, but since it never showed up in production, I never
> investigated it thoroughly like today.
>
> One thing I tried, is replacing the WeakHashMap mapping connections by
> threads by a default HashMap, and logging whether threads are alive or not.
> And it shows that there are still valid DB connections for dead threads. So
> that's really a waste and a unsafe code here. A simple workaround would be
> to have a thread periodically do the cleanup ... but I'll rather look at
> c3p0.
>
> As soon as I am sure I fully understood the problem I'll file a Jira bug.
>
> Regards,
>
>
> Nicolas
>
> 2011/3/25 Søren Vejrup Carlsen <svc at kb.dk>
>
>>  Hi Nicolas.
>>
>> I believe, that we are already using “c3p0”  in the wayback module, which
>> uses hibernate to access a simple derby database.
>>
>> Look especially at the code in the package dk.netarkivet.wayback.indexer
>>
>>
>>
>> I’m curious that you didn’t get this problem until launching 3.15.1. Why
>> can that be?
>>
>>
>>
>> Best Regards
>>
>> Søren
>>
>>
>>
>> *Fra:* netarchivesuite-devel-bounces at lists.gforge.statsbiblioteket.dk[mailto:
>> netarchivesuite-devel-bounces at lists.gforge.statsbiblioteket.dk] *På vegne
>> af *Nicolas Giraud
>> *Sendt:* 25. marts 2011 16:14
>>
>> *Til:* netarchivesuite-devel at lists.gforge.statsbiblioteket.dk
>> *Emne:* Re: [Netarchivesuite-devel] About the DBConnect class, running
>> out of connections
>>
>>
>>
>> The problem occurs whether the DB server is on the same machine or a
>> remote host.
>>
>> I am considering to set up a real connection pool. As we have different
>> target systems, I'd go for c3p0 or dbcp. Do you guys have some experience
>> with these APIs, would you recommend one? C3p0 seems really simple to put in
>> place.
>>
>> Best,
>>
>> Nicolas
>>
>> 2011/3/25 Søren Vejrup Carlsen <svc at kb.dk>
>>
>> Hi Nicolas.
>>
>> You could be right about NAS never closing the database connections. But
>> the connections to the database timeout automatically,
>>
>> don’t they?
>>
>>
>>
>> We’ve had similar problems with derby in our production environment
>> running 3.14.1.
>>
>>
>>
>> But we didn’t see in our test-environment.
>>
>> Are you running the database on the same server as the processes running?
>>
>>
>>
>> Best Regards
>>
>> Søren
>>
>>
>>
>> *Fra:* netarchivesuite-devel-bounces at lists.gforge.statsbiblioteket.dk[mailto:
>> netarchivesuite-devel-bounces at lists.gforge.statsbiblioteket.dk] *På vegne
>> af *Nicolas Giraud
>> *Sendt:* 25. marts 2011 14:57
>> *Til:* netarchivesuite-devel at lists.gforge.statsbiblioteket.dk
>> *Emne:* [Netarchivesuite-devel] About the DBConnect class, running out of
>> connections
>>
>>
>>
>> Hi,
>>
>> On our new production environment, we have run into a error where the DB
>> has too many open connections. I've started investigating on this, by
>> looking at the DBConnect class.
>>
>> First the "connection pool" in it seems bugged, as if any Thread in the
>> WeakHashMap is garbage collected, the associated connection might still be
>> open and is not closed. Also I don't see anywhere in all of the code a call
>> to Connection#close(), except in DomainDBDao#close() which by the way is
>> never called.
>>
>> Where are DB connections closed ? It seems they are never closed, unless I
>> have completely misunderstood the code.
>>
>> Any help will be greatly appreciated.
>>
>> Cheers,
>>
>> --
>> Nicolas Giraud
>>
>> ---------------------------------------------------------------------------------------------
>> Développeur Archives du Web - Bibliothèque Nationale de France
>> Web Archiving Developper - National Library of France
>>
>> ---------------------------------------------------------------------------------------------
>>
>>
>> _______________________________________________
>> Netarchivesuite-devel mailing list
>> Netarchivesuite-devel at lists.gforge.statsbiblioteket.dk
>>
>> https://lists.gforge.statsbiblioteket.dk/mailman/listinfo/netarchivesuite-devel
>>
>>
>>
>>
>> --
>> Nicolas Giraud
>>
>> ---------------------------------------------------------------------------------------------
>> Développeur Archives du Web - Bibliothèque Nationale de France
>> Web Archiving Developper - National Library of France
>>
>> ---------------------------------------------------------------------------------------------
>>
>> _______________________________________________
>> Netarchivesuite-devel mailing list
>> Netarchivesuite-devel at lists.gforge.statsbiblioteket.dk
>>
>> https://lists.gforge.statsbiblioteket.dk/mailman/listinfo/netarchivesuite-devel
>>
>>
>
>
> --
> Nicolas Giraud
>
> ---------------------------------------------------------------------------------------------
> Développeur Archives du Web - Bibliothèque Nationale de France
> Web Archiving Developper - National Library of France
>
> ---------------------------------------------------------------------------------------------
>



-- 
Nicolas Giraud
---------------------------------------------------------------------------------------------
Développeur Archives du Web - Bibliothèque Nationale de France
Web Archiving Developper - National Library of France
---------------------------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ml.sbforge.org/pipermail/netarchivesuite-devel/attachments/20110328/b47f7ae5/attachment-0002.html>


More information about the Netarchivesuite-devel mailing list