dhis2-devs team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

Yes wait_time for 22 hours is too much. I reset to default.

I confirm that placing the c3p0 on the hibernate.properties does not work.
The JAVA_OPTS thing was the solution.

My DHIS works fine so far...

Caveman



On Fri, Jul 9, 2010 at 1:46 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:

> 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<https://launchpad.net/%7Edhis2-devs>
> >>> >>> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >>> >>> >> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> >>> >>> >> More help   : https://help.launchpad.net/ListHelp
> >>> >>> >>
> >>> >>> >
> >>> >>> >
> >>> >>> >
> >>> >>> >
> >>> >>
> >>> >>
> >>> >>
> >>> >>
> >>> >
> >>> > _______________________________________________
> >>> > Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> >>> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >>> > Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> >>> > More help   : https://help.launchpad.net/ListHelp
> >>> >
> >>
> >>
> >>
> >>
> >
>