druid连接池配置参数详解

mac2024-04-21 37

配置缺省值说明name 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是："DataSource-" + System.identityHashCode(this)jdbcUrl 连接数据库的url，不同数据库不一样。例如： mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnautousername 连接数据库的用户名password 连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilterdriverClassName根据url自动识别这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)initialSize0初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时maxActive8最大连接池数量maxIdle8已经不再使用，配置了也没效果minIdle 最小连接池数量maxWait 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。poolPreparedStatementsfalse是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。maxOpenPreparedStatements-1要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100validationQuery 用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。testOnBorrowtrue申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。testOnReturnfalse归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能testWhileIdlefalse建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。timeBetweenEvictionRunsMillis 有两个含义： 1) Destroy线程会检测连接的间隔时间2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明numTestsPerEvictionRun 不再使用，一个DruidDataSource只支持一个EvictionRunminEvictableIdleTimeMillis connectionInitSqls 物理连接初始化的时候执行的sqlexceptionSorter根据dbType自动识别当数据库抛出一些不可恢复的异常时，抛弃连接filters 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wallproxyFilters

类型是List<com.alibaba.druid.filter.Filter>，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

spring boot 配置druid监控：

spring.datasource.filters=stat,wall,log4j spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=1000

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

spring.datasource.druid.stat-view-servlet.enabled= true spring.datasource.druid.StatViewServlet.loginUsername=druid spring.datasource.druid.StatViewServlet.loginPassword=druid spring.datasource.druid.StatViewServlet.urlPattern=/druid/* spring.datasource.druid.filters=stat,wall,log4j,config

import java.io.IOException; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.ServletRequest; import javax.servlet.ServletResponse; import javax.servlet.annotation.WebServlet; import javax.servlet.http.Cookie; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import com.alibaba.druid.support.http.StatViewServlet; import cn.com.geostar.common.utils.CommonUtils; @WebServlet("/druid/*") public class DruidAccessTokenServlet extends StatViewServlet { /** * */ private static final long serialVersionUID = 1L; @Override public void service(ServletRequest req, ServletResponse res) throws ServletException, IOException { super.service(req, res); } @Override public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String contextPath = request.getContextPath(); String servletPath = request.getServletPath(); String requestURI = request.getRequestURI(); response.setCharacterEncoding("utf-8"); String accessToken = request.getParameter("accessToken"); //如果url或者在参数为空就从headers获取 if(StringUtils.isBlank(accessToken)) { accessToken = request.getHeader("accessToken"); } if(StringUtils.isBlank(accessToken)) { Cookie[] cookies = request.getCookies(); if(cookies!=null) { for (Cookie cookie : cookies) { if ("accessToken".equals(cookie.getName())) { accessToken=cookie.getValue(); } } } } if(StringUtils.isBlank(accessToken)) { response.getWriter().write("token is null"); return; }else { Map<String, Object> userByToken = CommonUtils.getUserByToken(accessToken,false); if(userByToken != null && !userByToken.isEmpty()) { Cookie cookie=new Cookie("accessToken", accessToken); cookie.setHttpOnly(true); response.addCookie(cookie); }else { response.getWriter().write("token 非法"); return; } } if (contextPath == null) { // root context contextPath = ""; } String uri = contextPath + servletPath; String path = requestURI.substring(contextPath.length() + servletPath.length()); if (!isPermittedRequest(request)) { path = "/nopermit.html"; returnResourceFile(path, uri, response); return; } if ("".equals(path)) { if (contextPath.equals("") || contextPath.equals("/")) { response.sendRedirect("/druid/index.html"); } else { response.sendRedirect("druid/index.html"); } return; } if ("/".equals(path)) { response.sendRedirect("index.html"); return; } if (path.contains(".json")) { String fullUrl = path; if (request.getQueryString() != null && request.getQueryString().length() > 0) { fullUrl += "?" + request.getQueryString(); } response.getWriter().print(process(fullUrl)); return; } // find file in resources path returnResourceFile(path, uri, response); } @Override public boolean ContainsUser(HttpServletRequest request) { return true; } @Override public boolean isRequireAuth() { return false; } }

关于druid重试机制的坑：

2019-04-09 10:09:36 [Druid-ConnectionPool-Create-2053591126] [ com.alibaba.druid.pool.DruidDataSource ] [ 53 ] [ ERROR ] create connection SQLException, url: jdbc:mysql://*.*.*.*:3306/*?characterEncoding=utf-8&useSSL=false, errorCode 1045, state 28000 java.sql.SQLException: Access denied for user 'malluser'@'*.*.*.*' (using password: YES) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219) ~[mysql-connector-java-6.0.6.jar:6.0.6] at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:149) ~[druid-1.1.6%20.jar:1.1.6] at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218) ~[druid-1.1.6%20.jar:1.1.6] at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:143) ~[druid-1.1.6%20.jar:1.1.6] at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1512) ~[druid-1.1.6%20.jar:1.1.6] at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1575) ~[druid-1.1.6%20.jar:1.1.6] at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2450) [druid-1.1.6%20.jar:1.1.6]

如果密码错误会报此错，并且会无限重试。错误日志疯狂刷屏，看着头都大了。为什么会出现这种情况呢，源码分析：

public class CreateConnectionTask implements Runnable { private int errorCount = 0; @Override public void run() { runInternal(); } private void runInternal() { for (;;) { // addLast lock.lock(); try { if (closed || closing) { createTaskCount--; return; } boolean emptyWait = true; if (createError != null && poolingCount == 0) { emptyWait = false; } if (emptyWait) { // 必须存在线程等待，才创建连接 if (poolingCount >= notEmptyWaitThreadCount // && !(keepAlive && activeCount + poolingCount < minIdle)) { createTaskCount--; return; } // 防止创建超过maxActive数量的连接 if (activeCount + poolingCount >= maxActive) { createTaskCount--; return; } } } finally { lock.unlock(); } PhysicalConnectionInfo physicalConnection = null; try { physicalConnection = createPhysicalConnection(); setFailContinuous(false); } catch (OutOfMemoryError e) { LOG.error("create connection OutOfMemoryError, out memory. ", e); errorCount++; if (errorCount > connectionErrorRetryAttempts && timeBetweenConnectErrorMillis > 0) { // fail over retry attempts setFailContinuous(true); if (failFast) { lock.lock(); try { notEmpty.signalAll(); } finally { lock.unlock(); } } if (breakAfterAcquireFailure) { lock.lock(); try { createTaskCount--; } finally { lock.unlock(); } return; } this.errorCount = 0; // reset errorCount if (closing || closed) { createTaskCount--; return; } createSchedulerFuture = createScheduler.schedule(this, timeBetweenConnectErrorMillis, TimeUnit.MILLISECONDS); return; } } catch (SQLException e) { LOG.error("create connection SQLException, url: " + jdbcUrl, e); errorCount++; if (errorCount > connectionErrorRetryAttempts && timeBetweenConnectErrorMillis > 0) { // fail over retry attempts setFailContinuous(true); if (failFast) { lock.lock(); try { notEmpty.signalAll(); } finally { lock.unlock(); } } if (breakAfterAcquireFailure) { lock.lock(); try { createTaskCount--; } finally { lock.unlock(); } return; } this.errorCount = 0; // reset errorCount if (closing || closed) { createTaskCount--; return; } createSchedulerFuture = createScheduler.schedule(this, timeBetweenConnectErrorMillis, TimeUnit.MILLISECONDS); return; } } catch (RuntimeException e) { LOG.error("create connection RuntimeException", e); // unknow fatal exception setFailContinuous(true); continue; } catch (Error e) { lock.lock(); try { createTaskCount--; } finally { lock.unlock(); } LOG.error("create connection Error", e); // unknow fatal exception setFailContinuous(true); break; } catch (Throwable e) { LOG.error("create connection unexecpted error.", e); break; } if (physicalConnection == null) { continue; } boolean result = put(physicalConnection); if (!result) { JdbcUtils.close(physicalConnection.getPhysicalConnection()); LOG.info("put physical connection to pool failed."); } break; } } } public class CreateConnectionThread extends Thread { public CreateConnectionThread(String name){ super(name); this.setDaemon(true); } public void run() { initedLatch.countDown(); long lastDiscardCount = 0; int errorCount = 0; for (;;) { // addLast try { lock.lockInterruptibly(); } catch (InterruptedException e2) { break; } long discardCount = DruidDataSource.this.discardCount; boolean discardChanged = discardCount - lastDiscardCount > 0; lastDiscardCount = discardCount; try { boolean emptyWait = true; if (createError != null && poolingCount == 0 && !discardChanged) { emptyWait = false; } if (emptyWait && asyncInit && createCount.get() < initialSize) { emptyWait = false; } if (emptyWait) { // 必须存在线程等待，才创建连接 if (poolingCount >= notEmptyWaitThreadCount // && !(keepAlive && activeCount + poolingCount < minIdle)) { empty.await(); } // 防止创建超过maxActive数量的连接 if (activeCount + poolingCount >= maxActive) { empty.await(); continue; } } } catch (InterruptedException e) { lastCreateError = e; lastErrorTimeMillis = System.currentTimeMillis(); if (!closing) { LOG.error("create connection Thread Interrupted, url: " + jdbcUrl, e); } break; } finally { lock.unlock(); } PhysicalConnectionInfo connection = null; try { connection = createPhysicalConnection(); setFailContinuous(false); } catch (SQLException e) { LOG.error("create connection SQLException, url: " + jdbcUrl + ", errorCode " + e.getErrorCode() + ", state " + e.getSQLState(), e); errorCount++; if (errorCount > connectionErrorRetryAttempts && timeBetweenConnectErrorMillis > 0) { // fail over retry attempts setFailContinuous(true); if (failFast) { lock.lock(); try { notEmpty.signalAll(); } finally { lock.unlock(); } } if (breakAfterAcquireFailure) { break; } try { Thread.sleep(timeBetweenConnectErrorMillis); } catch (InterruptedException interruptEx) { break; } } } catch (RuntimeException e) { LOG.error("create connection RuntimeException", e); setFailContinuous(true); continue; } catch (Error e) { LOG.error("create connection Error", e); setFailContinuous(true); break; } if (connection == null) { continue; } boolean result = put(connection); if (!result) { JdbcUtils.close(connection.getPhysicalConnection()); LOG.info("put physical connection to pool failed."); } errorCount = 0; // reset errorCount } } }

从源码中我们可以看到，线程中使用了无参for循环再一直尝试进行数据源连接，代码中【errorCount > connectionErrorRetryAttempts && timeBetweenConnectErrorMillis > 0】当满足该判断条件时就会进行重试连接，接下来我们看一下源码中这两个属性值设置的是什么呢？（源码过长，只展示我们需要的代码，其他属性信息可自行扒源码~）

private static final long serialVersionUID = 1L; public final static int DEFAULT_INITIAL_SIZE = 0; public final static int DEFAULT_MAX_ACTIVE_SIZE = 8; public final static int DEFAULT_MAX_IDLE = 8; public final static int DEFAULT_MIN_IDLE = 0; public final static int DEFAULT_MAX_WAIT = -1; public final static String DEFAULT_VALIDATION_QUERY = null; // public final static boolean DEFAULT_TEST_ON_BORROW = false; public final static boolean DEFAULT_TEST_ON_RETURN = false; public final static boolean DEFAULT_WHILE_IDLE = true; public static final long DEFAULT_TIME_BETWEEN_EVICTION_RUNS_MILLIS = 60 * 1000L; public static final long DEFAULT_TIME_BETWEEN_CONNECT_ERROR_MILLIS = 500; public static final int DEFAULT_NUM_TESTS_PER_EVICTION_RUN = 3; /*****************************华丽的分割线中间省略代码若干行*********************************/ protected volatile long timeBetweenEvictionRunsMillis = DEFAULT_TIME_BETWEEN_EVICTION_RUNS_MILLIS; protected int connectionErrorRetryAttempts = 1; protected boolean breakAfterAcquireFailure = false;

从中这段代码中我们可以看到connectionErrorRetryAttempts值为1，timeBetweenConnectErrorMillis值为60000，而breakAfterAcquireFailure值为false，因此当我们数据源连接失败后，就会不断的进行重试连接，因此我对于对于该如何解决这样的问题我们就有了答案:

1.若不想让重试，我们可以设置breakAfterAcquireFailure(true);connectionErrorRetryAttempts(0);

2.若想要设置多久重试，我们只需要设置timeBetweenConnectErrorMillis(time）；

action：经过亲测，直接在配置文件中配置属性并不能读取到（Druid设计时就这样，大神的思维暂时还不能参悟~），我们可直接将值写入程序当中，如下：

最新回复(0)