数据库连接池优化

一、数据库瓶颈解决方案

　　在大流量时，数据库会成为系统瓶颈，其总体的解决方案如下：

　　　　1、项目架构使用缓存，90%请求拦截上游服务器 ———— 读操作

　　　　2、数据归档处理 （把一些用户不访问的数据直接归档处理，缓解数据库压力）--使用master ,slave架构

　　　　3、停机扩容

　　影响数据库性能因数：

　　　　1、服务器硬件

　　　　2、操作系统

　　　　3、存储引擎

　　　　4、数据库表结构（冗余设计）

　　　　5、SQL 语句

　　　　6、磁盘 IO

　　　　7、网卡流量

　　　　8、慢查询

　　总结来看：

　　　　1、低效 SQL 语句

　　　　2、并发 cpu 的问题（SQL 并不支持多核心 cpu 并发模式执行，一个 SQL 语句只能在一个 cpu 进行执行）

　　　　3、连接数： max-connections

　　　　4、大表（数据多，字段多）

　　　　5、大事务

　　　　6、超高 cpu 使用率

　　其中大表、大事务、低效SQL都是属于项目设计和开发时需要注意的，而连接数是需要进行提前设置好的。

二、连接数调优

　　首先从数据库连接池选型上，目前比较主流的有HikariCP 和阿里的Druid，HikariCP读性能很好，但是写性能一般，阿里的Druid就比较均衡，无论读写都很好，因此使用了阿里的Druid。

　　相关的配置项如下所示，都加了注释。

spring:
  application:
    name: sugo-seckill-web
  datasource:
    #url: jdbc:mysql://127.0.0.1:3306/shop?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
    url: jdbc:mysql://172.20.10.14:3306/shop?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&connectionTimeout=3000&socketTimeout=1200
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
    druid:
      #初始化线程数
      initial-size: 1
      # 最小线程数
      min-idle: 5
      # 最大线程数
      max-active: 10
      # 获取连接最大等待时间
      max-wait: 1200
      # 销毁线程的间隔时间
      time-between-eviction-runs-millis: 600000
      # 配置一个连接在池中最大空闲时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      # 设置从连接池获取连接时是否检查连接有效性，true时，每次都检查;false时，不检查
      test-on-borrow: true
      #设置往连接池归还连接时是否检查连接有效性，true时，每次都检查;false时，不检查
      test-on-return: true
      # 设置从连接池获取连接时是否检查连接有效性，true时，如果连接空闲时间超过minEvictableIdleTimeMillis进行检查，否则不检查;false时，不检查
      test-while-idle: true
      # 检验连接是否有效的查询语句。如果数据库Driver支持ping()方法，则优先使用ping()方法进行检查，否则使用validationQuery查询进行检查。(Oracle jdbc Driver目前不支持ping方法)
      validation-query: select 1 from dual
      # 是否长链接
      keep-alive: true
      # 是否删除异常连接
      remove-abandoned: true
      # 异常连接的判断条件（80毫秒没有使用的连接，防止线程阻塞）
      remove-abandoned-timeout: 80
      log-abandoned: true
      #打开PSCache，并且指定每个连接上PSCache的大小，Oracle等支持游标的数据库，打开此开关，会以数量级提升性能，具体查阅PSCache相关资料
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置间隔多久启动一次DestroyThread，对连接池内的连接才进行一次检测，单位是毫秒。
      #检测时:
      #1.如果连接空闲并且超过minIdle以外的连接，如果空闲时间超过minEvictableIdleTimeMillis设置的值则直接物理关闭。
      #2.在minIdle以内的不处理。

三、连接池参数优化

　　其实数据库连接池优化主要就是优化最大连接数、获取连接等待时间、连接超时时间和会话超时时间四项，其他的就直接按照上述的参数配置即可，基本上不需要调整。

（一）压测

　　使用jmeter进行压测

　　1、创建一个线程组--数据库连接池测试

　　2、添加--配置原件--JDBC Connection Configuration 

　　　　需要配置连接池名字、最大连接数、最大等待时间等，还需要配置验证sql、数据库信息

　　　　　　

 

 　　　　然后还需要添加一下驱动包，在测试计划中添加jar包即可。

　　3、添加--取样器--JDBC Request，需要添加连接池名字（mysql），sql语句，处理方式（这里选择预处理），sql的参数和参数类型

　　　　　　

 

　　　　4、添加--监听器--结果树

　　　　5、添加--监听器--聚合报告

　　　　6、添加--监听器--TPS

　　　　7、添加--监听器--RT　　

（二）参数调整

　　1、max-active : 最大连接数

　　　　在数据库连接池优化时，最重要的就是最大连接数的优化，太小太大都会影响性能，那么如何设置最大连接数，就成为优化的关键。

　　　　试验： max-active = 10 , TPS 测试的结果如下所示：

　　　　　　

 

 

 　　

　　　　试验： max-active = 20 ，TPS 测试结果： TPS = 18000 <= max-active=10

　　　　试验： max-active = 30 ，TPS 测试结果： TPS = 10000

　　　　结论： max-active 设置值范围： 【10 ~ 20】

　　2、Max-wait

　　　　含义： 表示从连接池获取连接的超时时间；单位是 ms ; 这个参数只管理获取连接的超时时间。

　　　　获取连接造成线程等待超时的原因：

　　　　　　（1）连接池未初始化（导致一开始获取不到连接，导致超时）

　　　　　　（2）连接长时间使用，已经被释放

　　　　　　（3）连接使用中，需要新建连接

　　　　　　（4）连接池中连接耗尽，需要等待用完后归还；

　　　　推荐设置：内网状态：max-wait=800 ms , 网络状态不是特别好，推荐设置大于等于 1200ms，因为 tcp 连接重试一般 1s;

　　　　例子：Max-wait = 0 – 无限等待，大量流量涌入后，导致线程池队列非常长，看起来 TPS 上升了，实际上处理吞吐能力下降的；

　　3、连接属性

　　　　（1）connectionTimeout : 配置建立 TCP 连接的超时时间，追加到连接后面即可；

　　　　（2）socketTimeout ： 配置发送请求后，等待响应的超时时间，这里有个问题，如果建立了连接，但是在会话的时候，一直拿不到结果，那么该线程就一直阻塞，导致可用资源下降。

　　　　结论：Jdbc 请求连接后面：&connectionTimeout=3000&socketTimeout=1200