为grails tomcat jndi提供最好的mysql数据源属性 [英] best mysql datasource properties for grails tomcat jndi

查看：136
本文介绍了为grails tomcat jndi提供最好的mysql数据源属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

JNDI，mysql 5.6和tomcat 7的大多数例子都在DataSource.groovy中定义了它： 

  dataSource {
 pooled = true 
 driverClassName =com.mysql.jdbc.Driver
 dialect ='org.hibernate.dialect.MySQL5InnoDBDialect'
} 
 environments：
 production {
 dataSource {
 dbCreate =update
 jndiName =java：comp / env / myDatasourceName 
} 
}

以及在tomcat的conf / context.xml中： 

 < WatchedResource> WEB-INF / web.xml< / WatchedResource> 
<资源名称=myDatasourceNameauth =Containertype =javax.sql .DataSource
 maxActive =100maxIdle =30maxWait =10000
 username =rootpassword =passworddriverClassName =com.mysql.jdbc.Driver
 url =jdbc：mysql：// localhost：3306 / my_db_name/> 
< / Context>

问题是的，这是一个基本的小批量生产系统的正确设置，它应该重新连接，如果数据库下跌然后等等？



在DataSource.xml中是一个非常复杂的例子：

pre $ dataSource {
dbCreate =update
// url =jdbc： h2：prodDb; MVCC = TRUE; LOCK_TIMEOUT = 10099; DB_CLOSE_ON_EXIT = FALSE
properties {
// Tomcat JDBC池的文档
// http://tomcat.apache.org/tomcat -7.0-doc / jdbc-pool.html＃Common_Attributes
// https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/PoolConfiguration.html
jmxEnabled = true
initialSize = 5
maxActive = 50
minIdle = 5
maxIdle = 25
maxWait = 10000
maxAge = 10 * 60000
timeBetweenEvictionRunsMillis = 5000
minEvictableIdleTimeMillis = 60000
va lidationQuery =SELECT 1
validationQueryTimeout = 3
validationInterval = 15000
testOnBorrow = true
testWhileIdle = true
testOnReturn = false
ignoreExceptionOnPreLoad = true
// http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JDBC_interceptors
jdbcInterceptors =ConnectionState; StatementCache（max = 200）
defaultTransactionIsolation = java .sql.Connection.TRANSACTION_READ_COMMITTED //安全缺省值
//控制泄漏连接
abandonWhenPercentageFull = 100 //只有池满时设置才有效
removeAbandonedTimeout = 120000
removeAbandoned = true
//使用JMX控制台在运行时更改此设置
logAbandoned = false //导致堆栈跟踪记录开销，仅用于调试
/ *
// JDBC驱动器r属性
// Mysql例如
dbProperties {
//特定于Mysql的驱动程序属性
// http://dev.mysql.com/doc/connector-j/en /connector-j-reference-configuration-properties.html
//让Tomcat JDBC Pool处理重新连接
autoReconnect = false
//截断行为
jdbcCompliantTruncation = false
// mysql 0-date conversion
zeroDateTimeBehavior ='convertToNull'
// Tomcat JDBC池的StatementCache被用来代替，所以禁用mysql驱动程序的缓存
cachePrepStmts = false
cacheCallableStmts = false
// Tomcat JDBC池的StatementFinalizer跟踪
dontTrackOpenResources = true
//性能优化：减少在mysql驱动程序代码中引发的SQLException数量
holdResultsOpenOverStatementClose = true
//启用MySQL查询缓存 - 使用服务器准备工具将禁用查询缓存
useServerPrepStmts = false
//元数据缓存
cacheServerConfiguration = true
cacheResultSetMetadata = true
metadataCacheSize = 100
//超时TCP / IP
connectTimeout = 15000
socketTimeout = 120000
//定时器调整（禁用）
maintainTimeStats = false
enableQueryTimeouts = false
// misc tuning
noDatetimeStringSync = true
}
* /
}
}
code>

现在我不知道这些参数的95％是什么，但是我想我需要它们吗？

我不明白的是这个


  1. 当然，tomcat会将连接池离子，所以应该在grails dataSource pooled = true？

  2. 当然，tomcat将处理连接的重新连接和验证，所以为什么定义validationQuery =SELECT 1？特别是在mysql dbPrperties部分中，它的意思是autoREconnect = false。
  3. 有人可以为少数用户提供适用于mysql的通用默认数据源定义吗？我可以调整最小/最大活动/空闲等，但这不是全部在tomcat中完成，而不是grails？

  4. userServerPrepStmts = false为什么有人想要禁用准备好的语句？如果我忽略了所有这些复杂的例子，并且每个人都发布了超级smiple，那么它会正常工作吗？ ？例如，当数据库关闭和重新启动时，它会重新连接吗？它会不会泄漏等。

谢谢！

$ b $ ab

解决方案

AFAIK，Tomcat与管理数据库连接没有关系，因为 Grails 2.3.6 ，这些是推荐的错误数据源属性： 

 属性{
 //参见http://grails.org/doc/latest/guide /conf.html#dataSource for documentation 
 jmxEnabled = true 
 initialSize = 5 
 maxActive = 50 
 minIdle = 5 
 maxIdle = 25 
 maxWait = 10000 
 maxAge = 10 * 60000 
 timeBetweenEvictionRunsMillis = 5000 
 minEvictableIdleTimeMillis = 60000 
 validationQuery =SELECT 1
 validationQueryTimeout = 3 
 validationInterval = 15000 
 testOnBorrow = true 
 testWhileIdle = true 
 testOnReturn = false 
 jdbcInterceptors =ConnectionState; StatementCache（max = 200）
 defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED 
}


Most examples of JNDI, mysql 5.6 and tomcat 7 have this defined in DataSource.groovy:

dataSource {
   pooled = true
   driverClassName = "com.mysql.jdbc.Driver"
   dialect = 'org.hibernate.dialect.MySQL5InnoDBDialect'
}
environments:
   production {
        dataSource {
        dbCreate = "update"
        jndiName = "java:comp/env/myDatasourceName
    }
}

and in the conf/context.xml in tomcat:

<Context>
   <WatchedResource>WEB-INF/web.xml</WatchedResource>
      <Resource name="myDatasourceName" auth="Container" type="javax.sql.DataSource"
       maxActive="100" maxIdle="30" maxWait="10000"
       username="root" password="password" driverClassName="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost:3306/my_db_name"/>
 </Context>

The question is, is this the correct setup for a basic low volume production system, which should reconnect if the db goes down then up etc?

In the DataSource.xml is a hugely complex example:

    dataSource {
        dbCreate = "update"
        //url = "jdbc:h2:prodDb;MVCC=TRUE;LOCK_TIMEOUT=10099;DB_CLOSE_ON_EXIT=FALSE"
        properties {
           // Documentation for Tomcat JDBC Pool
           // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes
           // https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/PoolConfiguration.html
           jmxEnabled = true
           initialSize = 5
           maxActive = 50
           minIdle = 5
           maxIdle = 25
           maxWait = 10000
           maxAge = 10 * 60000
           timeBetweenEvictionRunsMillis = 5000
           minEvictableIdleTimeMillis = 60000
           validationQuery = "SELECT 1"
           validationQueryTimeout = 3
           validationInterval = 15000
           testOnBorrow = true
           testWhileIdle = true
           testOnReturn = false
           ignoreExceptionOnPreLoad = true
           // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JDBC_interceptors
           jdbcInterceptors = "ConnectionState;StatementCache(max=200)"
           defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED // safe default
           // controls for leaked connections 
           abandonWhenPercentageFull = 100 // settings are active only when pool is full
           removeAbandonedTimeout = 120000
           removeAbandoned = true
           // use JMX console to change this setting at runtime
           logAbandoned = false // causes stacktrace recording overhead, use only for debugging
           /*
           // JDBC driver properties
           // Mysql as example
           dbProperties {
               // Mysql specific driver properties
               // http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
               // let Tomcat JDBC Pool handle reconnecting
               autoReconnect=false
               // truncation behaviour 
               jdbcCompliantTruncation=false
               // mysql 0-date conversion
               zeroDateTimeBehavior='convertToNull'
               // Tomcat JDBC Pool's StatementCache is used instead, so disable mysql driver's cache
               cachePrepStmts=false
               cacheCallableStmts=false
               // Tomcat JDBC Pool's StatementFinalizer keeps track
               dontTrackOpenResources=true
               // performance optimization: reduce number of SQLExceptions thrown in mysql driver code
               holdResultsOpenOverStatementClose=true
               // enable MySQL query cache - using server prep stmts will disable query caching
               useServerPrepStmts=false
               // metadata caching
               cacheServerConfiguration=true
               cacheResultSetMetadata=true
               metadataCacheSize=100
               // timeouts for TCP/IP
               connectTimeout=15000
               socketTimeout=120000
               // timer tuning (disable)
               maintainTimeStats=false
               enableQueryTimeouts=false
               // misc tuning
               noDatetimeStringSync=true
           }
           */
        }
    }

Now I dont know what 95% of those parameters do, but I guess I need them all?

What I dont understand is this

  1. surely tomcat will pool the connections, so should pooled=true in the grails dataSource?
  2. Surely tomcat will handle reconnecting and validation of connection, so why define validationQuery = "SELECT 1"? Expecially as its saying "autoREconnect=false in the mysql dbPrperties section.
  3. Could someone come up with a general purpose default dataSource definition suitable for mysql for a small number of users? I can tune the min/max active/idle etc, but is this not all done in tomcat, not grails?
  4. "userServerPrepStmts=false" why would anyone want to disable prepared statements? We have been drilled for the last 15 years to only ever use prepared statements otherwise the DB cant cache them.
  5. If I ignore all this complex example, and went with the super smiple one everyone posts, will it work properly? e.g. will it reconnect when the db goes down and up? will it not leak etc.

Thanks!

解决方案

AFAIK, Tomcat has nothing to do with managing the database connection. Since Grails 2.3.6, these are the recommended default datasource properties:

properties {
  //see http://grails.org/doc/latest/guide/conf.html#dataSource for documentation
   jmxEnabled = true
   initialSize = 5
   maxActive = 50
   minIdle = 5
   maxIdle = 25
   maxWait = 10000
   maxAge = 10 * 60000
   timeBetweenEvictionRunsMillis = 5000
   minEvictableIdleTimeMillis = 60000
   validationQuery = "SELECT 1"
   validationQueryTimeout = 3
   validationInterval = 15000
   testOnBorrow = true
   testWhileIdle = true
   testOnReturn = false
   jdbcInterceptors = "ConnectionState;StatementCache(max=200)"
   defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
}

这篇关于为grails tomcat jndi提供最好的mysql数据源属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
服务器开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆