04-数据库连接池调优
注:高并发情况下,Tomcat原生连接池可能存在性能不佳的表现,此次使用高性能Druid连接池
1、引入Druid依赖
<!-- 引入Druid依赖 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>${druid.version}</version> </dependency>
2、数据源配置
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="defaultAutoCommit" value="true" /> <property name="maxActive" value="1000"></property> <property name="maxWait" value="300000"></property> <property name="validationQuery" value="SELECT 1 FROM DUAL"></property> <property name="testWhileIdle" value="false"></property> <property name="testOnBorrow" value="true"></property> <property name="testOnReturn" value="false"></property> <property name="timeBetweenEvictionRunsMillis" value="60000"></property> <property name="removeAbandoned" value="true" /> <property name="removeAbandonedTimeout" value="300" /> </bean>
注:
maxActive:最大激活连接数
maxWait :最大等待连接时间
validationQuery:验证数据库连接的有效性
testWhileIdle:申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testOnBorrow:申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
testOnReturn:归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
timeBetweenEvictionRunsMillis:test idle 线程的时间间隔
removeAbandoned:超过时间限制是否回收
removeAbandonedTimeout:超出时间,单位秒
3、配置Druid自带监控页面,以下以Spring boot配置为例,访问http://localhost:port/druid 即可访问
@Bean public ServletRegistrationBean druidServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(); servletRegistrationBean.setServlet(new StatViewServlet()); servletRegistrationBean.addUrlMappings("/druid/*"); Map<String, String> initParameters = new HashMap<String, String>(); initParameters.put("loginUsername", "admin");// 用户名 initParameters.put("loginPassword", "admin");// 密码 initParameters.put("resetEnable", "false");// 禁用HTML页面上的“Reset All”功能 initParameters.put("allow", ""); // IP白名单 (没有配置或者为空,则允许所有访问) // initParameters.put("deny", "192.168.20.38");// IP黑名单 // (存在共同时,deny优先于allow) servletRegistrationBean.setInitParameters(initParameters); return servletRegistrationBean; } @Bean public FilterRegistrationBean filterRegistrationBean() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(); filterRegistrationBean.setFilter(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; }