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 <[email protected]> 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 <[email protected]> wrote: > > Hi Orvalho > > > > Thanks for looking in to this. Its important that we get to the bottom > of it. > > > > 2010/7/9 Orvalho Augusto <[email protected]>: > >> 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 <[email protected]> > >>> > >>> On Mon, Jul 5, 2010 at 11:44 PM, Bob Jolliffe <[email protected]> > >>> 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 <[email protected]> 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 < > [email protected]> > >>> >> 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 <[email protected]> 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 <[email protected]> > >>> >>> > wrote: > >>> >>> >> > >>> >>> >> Is postgres out of the question? > >>> >>> >> > >>> >>> >> On Jul 4, 2010 5:59 AM, "Orvalho Augusto" <[email protected]> > >>> >>> >> 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 > >>> >>> >> <[email protected]> > >>> >>> >> wrote: > > Hi > > It looks... > >>> >>> >> > >>> >>> >> _______________________________________________ > >>> >>> >> Mailing list: > >>> >>> >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> > >>> >>> >> Post to : [email protected] > >>> >>> >> 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 : [email protected] > >>> > 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 Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
