dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

OK here's what I've done:

- set wait_timeout on mysql to 60 seconds.
- start up dhis2 and watch the connections using mysql workbench.  I
also turned up c3p0 logging with 'log4j.category.com.mchange = DEBUG,
console' to watch the pool being managed at the dhis side.
- 3 connections are made prior to login and sit idel
- after 60 seconds of idle time mysql throws them out
- try to login to dhis now and you get the dreaded error

Two ways which keep everything alive are:
(i)  test the idle connections every now and again (eg
-Dc3p0.idleConnectionTestPeriod=30).  This way from the mysql
perspective they never go idle so are never thrown out.
(ii)  set a max age for idle connections - Orvalho's solution (eg
-Dc3p0.maxIdleTime=30) - before they are recreated afresh.

Either of the above seem to do the trick as far as mysql is concerned.
 But if you don't do one or the other (or perhaps a combination of the
two) then your connection with mysql will fall over eventually.

One problem I have is that setting these properties via the hibernate
properties file is not working for me.  They are simply ignored :-(
This is a problem, particularly as you might want to change the pool
size settings from the default (which seems from observation to be
minimum 3 / maximum 15 connections) to something which scales better
to your use case.  Again I am sure this is the case for the Indian
servers.  Anyway I have tried the following in my
hibernate.properties:

########################################################
# connection pool configuration
#
########################################################

# keep idle connections around for 30 minutes
hibernate.c3p0.timeout = 1800

# test idle connections every 5 minutes
hibernate.c3p0.idle_test_period = 300

# minimum pool size
hibernate.c3p0.min_size = 5

# maximum pool size
hibernate.c3p0.max_size = 20

#########################################################

But it has no effect :-(  Probably I am doing something stupid.  Maybe
someone can investigate further.

Regards
Bob


On 9 July 2010 10:17, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:
> Hi Orvalho
>
> Thanks for looking in to this.  Its important that we get to the bottom of it.
>
> 2010/7/9 Orvalho Augusto <orvaquim@xxxxxxxxx>:
>> I do not know what it made stop for now.
>>
>> But I did this:
>> 1. Increase MySQL connection timeout for iddle connections by adding on
>> my.cnf under mysqld section
>> wait_timeout = 80000
>> interactive_timeout = 80000
>>
>> Place both. wait_timeout is affected by interactive_timeout (the MySQL doc
>> says so).
>
> So you have increased the time Mysql will sit with idle connections
> from 8 hours to 22 hours.  It will still drop them eventually -
> particularly over the weekend :-)   From mysql's perspective its a
> performance/resource thing.  Each open connection is hogging a thread.
>  So if a connection sits idle for too long its going to reclaim it
> eventually.  The trick is to make our client (hibernate+c3p0) a bit
> defensive to this behaviour.
>
>>
>> 2. c3p0
>> JAVA_OPTS I added this -Dc3p0.maxIdleTime=1800 -Dc3p0.maxIdleTime=3600
>> -Dc3p0.maxPoolSize=20
>>
>> And I have placeded the c3p0 jar files under $JAVA_HOME/jre/lib/ext [is not
>> beautiful].
>
> There is already a c3p0 jar shipped with dhis alongside hibernate (in
> the WEB-INF/lib directory) so you shouldn't need to add your extra one
> which as you say is not beautiful.
>
> Also, to the best of my knowledge (I'm going to try some settings in a
> minute), the c3p0 settings can be configured in the
> hibernate.properties file which is slightly more beautiful than
> setting JAVA_OPTS.
>
> Regarding the actual values to set, I think you might be on to
> something.  My suggestion was to set c3p0 so that it periodically
> checked connections to see if they were still live.  That's a good
> defensive strategy where you don't have control of both ends of the
> puzzle ie. mysql and c3p0.  But where you know the idle time on the
> mysql server (as you do because you set it), then it might well be
> sufficient to set the maxIdle time on c3p0 to be just under the
> maxidle time of mysql.  So c3p0 should never hold open idle
> connections for longer than mysql's timeout period.  That is your case
> currently though the difference is quite extreme.
>
> Note that by my reading of the defaults then the out-of-the-box
> hibernate/c3p0/mysql setup is fragile.  Mysql has a default timeout of
> it's connections of 8 hours.  c3p0 has a default of unlimited time.
> So you folk running longstanding production mysql servers *must* be
> seeing this.  Again would be good to hear of any database experiences
> from India.  You guys have the most experience of running mysql
> production servers.
>
> I'm going to do a quick experiment with ridiculously short timeouts
> and let you know what I find ....
>
> Cheers
> Bob
>>
>> On weekend I will remove one of them and see what happens. But It has gone
>> for now.
>>
>> Caveman
>>
>>
>> 2010/7/8 Lars Helge Øverland <larshelge@xxxxxxxxx>
>>>
>>> On Mon, Jul 5, 2010 at 11:44 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>> wrote:
>>> > Searching back through some old mail I see something similar has
>>> > surfaced before in this bug report:
>>> > https://bugs.launchpad.net/dhis2/+bug/534567
>>> >
>>> > Also there was an issue with one of the servers in India which also
>>> > used mysql where I had a look at the log file and found an
>>> > inexplicably large number of threads blocked in a write operation
>>> > which I now suspect is also related.
>>> >
>>> > It seems that some care needs to be taken to manage the mysql
>>> > connection pool over time.  And as I suggested above I believe
>>> > (suspect!) the way to do that might be to proactively manage the
>>> > connection threads using c3p0's idleTestperiod property.  Note I am no
>>> > hibernate or c3p0 expert.
>>> >
>>> > I do recall from the "old days" perhaps around 2.01 we were commonly
>>> > setting c3p0 related properties in the hibernate.properties file.  I
>>> > also remember (but can't find a reference) some suggestion of dropping
>>> > this.  Lars, do you remember why we don't have these parameters set in
>>> > the hibernate.properties any more?  I have a funny feeling that they
>>> > are required to keep long running mysql installations alive and
>>> > kicking.
>>> >
>>>
>>> Sorry, can't remember.
>>>
>>> Indians have been running dhis on mysql for a long time now, good if
>>> you could provide some input...
>>>
>>>
>>>
>>> > Regards
>>> > Bob
>>> >
>>> > On 5 July 2010 17:39, Orvalho Augusto <orvaquim@xxxxxxxxx> wrote:
>>> >> Thanks for help.
>>> >>
>>> >> Postgres I will use if I do not have a solution.
>>> >>
>>> >> I will try things on the connection pool.
>>> >>
>>> >> One very important note:
>>> >> I have used DHIS 2.01 for almost a year and never happened to have
>>> >> these
>>> >> errors on the same Fedora Server as described before. I have tried some
>>> >> snapshot versions and again never had this.
>>> >> I jumped from 2.01 to 2.04 and I start with MySQL troubles.
>>> >>
>>> >> Caveman
>>> >>
>>> >>
>>> >> On Mon, Jul 5, 2010 at 10:20 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>> >> wrote:
>>> >>>
>>> >>> Hi Orvalho
>>> >>>
>>> >>> Whereas I do agree postgres might solve your problems it does seem
>>> >>> like a drastic solution to this problem.  The fact is mysql does
>>> >>> actually work and apparently work well so it should be possible to
>>> >>> solve this.
>>> >>>
>>> >>> Trying to think logically here ... you have another similar setup
>>> >>> where everything seems ok.  I suppose the load could be quite
>>> >>> different on the two servers, but lets discount that for a bit.  If
>>> >>> you are reusing old stale and broken connections then the only three
>>> >>> places i can think to look are:
>>> >>> (i)  the jdbc driver (version compatibility).  I think you checked
>>> >>> that.
>>> >>> (ii) tcp/ip problems
>>> >>> (iii) the connection pool
>>> >>>
>>> >>> Regarding (ii) can you check the value of bind-address in
>>> >>> /etc/mysql/my.cnf (or whatever config file you are using).  This
>>> >>> should be set to 127.0.0.1 unless you are connecting to the db from a
>>> >>> different host.  At least this interface will always be up so you
>>> >>> shouldnt get intermittent network errors on it.
>>> >>>
>>> >>> Regarding (iii) I suggest (as above) that you look at setting up c3p0
>>> >>> parameters to periodically test and discard stale connections.  Does
>>> >>> anybody (maybe Indian team) have a good sample config?
>>> >>>
>>> >>> Regards
>>> >>> Bob
>>> >>>
>>> >>> On 4 July 2010 13:09, Orvalho Augusto <orvaquim@xxxxxxxxx> wrote:
>>> >>> > It will give some job because we developed a tool to convert data
>>> >>> > from
>>> >>> > one
>>> >>> > database to MySQL.
>>> >>> >
>>> >>> > It is the only thing I can try to do to solve.
>>> >>> >
>>> >>> > Caveman
>>> >>> >
>>> >>> >
>>> >>> >
>>> >>> > On Sun, Jul 4, 2010 at 8:16 AM, Knut Staring <knutst@xxxxxxxxx>
>>> >>> > wrote:
>>> >>> >>
>>> >>> >> Is postgres out of the question?
>>> >>> >>
>>> >>> >> On Jul 4, 2010 5:59 AM, "Orvalho Augusto" <orvaquim@xxxxxxxxx>
>>> >>> >> wrote:
>>> >>> >>
>>> >>> >> I do not know what to do. I have found this:
>>> >>> >>
>>> >>> >>
>>> >>> >>
>>> >>> >> http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-troubleshooting.html
>>> >>> >>
>>> >>> >> And what makes me feel really bad is:
>>> >>> >> "22.3.5.3.4: I have a servlet/application that works fine for a
>>> >>> >> day,
>>> >>> >> and
>>> >>> >> then stops working overnight MySQL closes connections after 8 hours
>>> >>> >> of
>>> >>> >> inactivity. You either need to use a connection pool that handles
>>> >>> >> stale
>>> >>> >> connections or use the "autoReconnect" parameter"
>>> >>> >>
>>> >>> >> and
>>> >>> >> "The autoReconnect facility is deprecated, and may be removed in a
>>> >>> >> future
>>> >>> >> release. "
>>> >>> >>
>>> >>> >>
>>> >>> >> So what we do?
>>> >>> >>
>>> >>> >> Caveman
>>> >>> >>
>>> >>> >> On Mon, Jun 28, 2010 at 12:59 PM, Bob Jolliffe
>>> >>> >> <bobjolliffe@xxxxxxxxx>
>>> >>> >> wrote: > > Hi > > It looks...
>>> >>> >>
>>> >>> >> _______________________________________________
>>> >>> >> Mailing list: https://launchpad.net/~dhis2-devs
>>> >>> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> >>> >> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> >>> >> More help   : https://help.launchpad.net/ListHelp
>>> >>> >>
>>> >>> >
>>> >>> >
>>> >>> >
>>> >>> >
>>> >>
>>> >>
>>> >>
>>> >>
>>> >
>>> > _______________________________________________
>>> > Mailing list: https://launchpad.net/~dhis2-devs
>>> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> > Unsubscribe : https://launchpad.net/~dhis2-devs
>>> > More help   : https://help.launchpad.net/ListHelp
>>> >
>>
>>
>>
>>
>