Re: MySQL communication link failure
The server has been running with the new settings for a couple of days now and it looks good so far. Thanks everyone for your help! -Stijn Stijn de Witt wrote: Martin Kalén wrote: Stijn de Witt wrote: Could you review these settings for the evictor thread solution? Do you think these settings are ok? validationQuery=select 1 minEvictableIdleTimeMillis=6 timeBetweenEvictionRunsMillis=12 numTestsPerEvictionRun=10 testWhileIdle=true testOnBorrow=false testOnReturn=false Could I combine these with autoReconnect=true? Would that be useful? Which of the two options would you use? This looks like a good setup to me! Personally, I would recommend you to set testOnBorrow=true and leave the autoReconnect URL paramater out of the equation. Thanks for all the info Martin, I will do as you say, leave autoReconnect=true out, use an evictor thread and set testOnBorrow=true. I will let you know how it worked out. Greetings, -Stijn - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MySQL communication link failure
Martin Kalén wrote: Stijn de Witt wrote: Could you review these settings for the evictor thread solution? Do you think these settings are ok? validationQuery=select 1 minEvictableIdleTimeMillis=6 timeBetweenEvictionRunsMillis=12 numTestsPerEvictionRun=10 testWhileIdle=true testOnBorrow=false testOnReturn=false Could I combine these with autoReconnect=true? Would that be useful? Which of the two options would you use? This looks like a good setup to me! Personally, I would recommend you to set testOnBorrow=true and leave the autoReconnect URL paramater out of the equation. Thanks for all the info Martin, I will do as you say, leave autoReconnect=true out, use an evictor thread and set testOnBorrow=true. I will let you know how it worked out. Greetings, -Stijn - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MySQL communication link failure
Martin Kalén wrote: So you should never combine autoReconnect for MySQL JDBC URL and testOnBorrow for a Commons Pool-based Connection pool (like OJB's default- and DBCP-based pools). If you do, you will just create additional overhead by checking the same things twice. The advantage of mastering the Commons Pool options is that this will work with all database servers that OJB supports, not just MySQL. Do you know if the autoReconnect=true option alone will fix it? I have seen mention of autoReconnectForPools option? We are not planning on migrating away from MySql anytime soon. As I understand it, I shouldn't 'play it save' and use autoReconnect=true in combination with the Evictor thread solution? Could you review these settings for the evictor thread solution? Do you think these settings are ok? validationQuery=select 1 minEvictableIdleTimeMillis=6 timeBetweenEvictionRunsMillis=12 numTestsPerEvictionRun=10 testWhileIdle=true testOnBorrow=false testOnReturn=false Could I combine these with autoReconnect=true? Would that be useful? Which of the two options would you use? Hope this was not too much info and that it did not confuse you more! Regards, Martin Definitely not too much info, thank you very much! -Stijn - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MySQL communication link failure
Stijn de Witt wrote: The advantage of mastering the Commons Pool options is that this will work with all database servers that OJB supports, not just MySQL. Do you know if the autoReconnect=true option alone will fix it? I have seen mention of autoReconnectForPools option? We are not planning on migrating away from MySql anytime soon. As I understand it, I shouldn't 'play it save' and use autoReconnect=true in combination with the Evictor thread solution? The MySQL-specific autoReconnectForPools will not apply when using OJB connection pooling, only if you use a MySQL JDBC-driver configured connection pool. The Oracle JDBC-driver has a similar feature in recent versions. Bascially, what the JDBC-driver specific pooling feature does is saving you in case the application does not provide pooling. Since OJB provides connection pooling I would recommend _not_ to use MySQL driver-controlled pooling to avoid getting a messy configuration. (Random thought, not verified: If you activate a MySQL connection pooling together with OJB dito, you might end up allocating a whole MySQL connection pool for each Connection that you check out from the OJB pool.) The MySQL URL-paramater autoReconnect=true and Commons Pool testOnBorrow=true will both solve your immediated problem and with just a few msecs apart as to when it happens (autoReconnect is like testJustAfterBorrow). Could you review these settings for the evictor thread solution? Do you think these settings are ok? validationQuery=select 1 minEvictableIdleTimeMillis=6 timeBetweenEvictionRunsMillis=12 numTestsPerEvictionRun=10 testWhileIdle=true testOnBorrow=false testOnReturn=false Could I combine these with autoReconnect=true? Would that be useful? Which of the two options would you use? This looks like a good setup to me! Personally, I would recommend you to set testOnBorrow=true and leave the autoReconnect URL paramater out of the equation. I just like to keep all settings consistent and leave all validation up to Commons Pool API, without relying on the JDBC-driver to do additional checks. (But this is just a matter of taste and I would think you can't measure any big performance issues between the two. I would assume that the driver-internal method would have the chance of a quicker validation mechanism than issuing a validation query through JDBC but the big overhead should be TCP/IP socket creation and network traffic anyway so it probably matters very little). The benefit of doing what you do: combine an evictor thread with testing on borrow makes the probability of Connections being broken on borrow less (since the eviction runs will probably catch some of those). This should ie raise the probability for faster response- times on borrow (no need to discard object from pool and create new Connection if this was done while idle). I use probability just because you can't tell this deterministically without making some sort of mathematical proof of the whole application and all external factors. ;) Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MySQL communication link failure
Stijn de Witt wrote: We have developed a web application using OJB. All seems to work fine, but after some hours of being deployed we run into a problem. snip/ java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1394) snip/ I have seen a number of solutions mentioned: 1) Specify the MySQL autoReconnect=true property in the connection string: jdbc:mysql://localhost:3306/database name?autoReconnect=true This is a feature specific to the MySQL JDBC-driver (MySQL Connector/J), since you specify this in the JDBC URL it will work for every JDBC- client connecting to your MySQL server (including OJB). To relate to the Commons Pool configuration options, this would be equivalent to something like testJustAfterBorrow=true. 2) Make sure the used pool drops idle connections before MySQL does This is the so called evictor thread solution, and is best for optimal response times when something is about to happen in your application. You configure this with the following options in Commons Pool: validationQuery=SELECT 1 (for MySQL, a simple statement that returns 1 row) testWhileIdle=true (activates the evictor thread) timeBetweenEvictionRunsMillis=no of millisecs between eviction runs numTestsPerEvictionRun=no of Connection to check each run minEvictableIdleTimeMillis=min no of passive msecs before eligable for eviction When you set testWhilIdle=true and activate the separate evictor thread, an event will be triggered each [timeBetweenEvictionRunsMillis] millisecons. When this event (eviction run) is triggered, the following will happen: 1. the evictor thread grabs [numTestsPerEvictionRun] Connection instances from the pool 2. for each Connection that has been idle in the pool more than [minEvictableIdleTimeMillis], the validation query will be performed 3. for each Connection where the validation query failed, discard the instance from pool and re-evaluate minIdle 4. if idle threshold is now below minIdle, new Connection instances are fed into the pool If you don't specify minIdle, step #4 is not performed. 3) Make sure the connection is validated before being used with a validation query This is the testOnBorrow=true setting, validationQuery is specified as above. The exact same procedure as in the idle object evictor thread will happen when OJB tries to borrow/use a Connection from the pool. 4) Disable pooling with ConnectionFactoryNotPooledImpl Bad! Imagine that your OJB application in rapid fire needs to open and close Connection instances. If, for each borrow of a Connection the TCP/IP socket + MySQL overhead has to be performed your application performance will be degraded severely. Additional to this, when creating new Connection instances the Java virtual machine has to manage more garbage collection / memory allocation and the JDBC driver has to perform static initializer code when creating the connections. Not using connection pooling is more for testing or debugging I would say, always use some sort of pooling in a production environment. 5) Use a DataSource managed by the AppServer (Tomcat / JBoss) i.c.w JNDI lookup This is a re-iteration of your suggestion 1-4, only that you move configuration responsibility from OJB properties and repository to the AppServer. The obvious advantage is that several apps can use the same pool, centrally configured in the AppServer. Now I am getting a bit lost in all these options, and testing them is very hard. Does anyone here have experience with this issue and know of an easy way to fix it? Option 1) with autoReconnect seems most attractive to me, but how should I set such a connection parameter using OJB? Is it even possible? Like Danilo pointed out, this is well possible with OJB and is almost identical to setting: validationQuery=SELECT 1 testOnBorrow=true So you should never combine autoReconnect for MySQL JDBC URL and testOnBorrow for a Commons Pool-based Connection pool (like OJB's default- and DBCP-based pools). If you do, you will just create additional overhead by checking the same things twice. The advantage of mastering the Commons Pool options is that this will work with all database servers that OJB supports, not just MySQL. A few general hints to choose between all those different options: 1. You consider Connection management overhead to be a minimal bottleneck in your application and don't want to care too much. You just want to guarantee that the Connection instance OJB gets from the pool is not broken after 8 idle hrs to avoid exceptions. Use: testOnBorrow=true testOnReturn=false testWhileIdle=false 2. You are aware that Connection instances will often be idle long enough for MySQL to drop the connection and you want instant response times in your application once it wakes up again. Use: testOnBorrow=true testOnReturn=false testWhileIdle=true Just don't set the timeBetweenEvictionRunsMillis to low, since the evictor thread will then become a
Re: MySQL communication link failure
Hi, (...) Now I am getting a bit lost in all these options, and testing them is very hard. Does anyone here have experience with this issue and know of an easy way to fix it? Option 1) with autoReconnect seems most attractive to me, but how should I set such a connection parameter using OJB? Is it even possible? you can do this in OJB by just adding the ?autoReconnect=true at the end of the dbalias attribute in the jdbc-connection-descriptor tag in your repository.xml bye danilo - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: MySQL communication link failure
Danilo Tommasina wrote: Now I am getting a bit lost in all these options, and testing them is very hard. Does anyone here have experience with this issue and know of an easy way to fix it? Option 1) with autoReconnect seems most attractive to me, but how should I set such a connection parameter using OJB? Is it even possible? you can do this in OJB by just adding the ?autoReconnect=true at the end of the dbalias attribute in the jdbc-connection-descriptor tag in your repository.xml bye danilo Thanks, I'll try this out, let you know if it helped. -Stijn - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
