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@gmail.com 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@gmail.com 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@gmail.com:
I do not know what it made stop for now.
But I did this:
- 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.
- 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@gmail.com
On Mon, Jul 5, 2010 at 11:44 PM, Bob Jolliffe bobjolliffe@gmail.com > > >>> wrote:
Searching back through some old mail I see something similar has
surfaced before in this bug report:
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@gmail.com 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@gmail.com > > >>> >> 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@gmail.com 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@gmail.com > > >>> >>> > wrote:
Is postgres out of the question?
On Jul 4, 2010 5:59 AM, “Orvalho Augusto” orvaquim@gmail.com > > >>> >>> >> 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
wrote: > > Hi > > It looks…
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp