dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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
>> >
>
>
>
>