• 1. JBOSS连接池原理和调优 支付宝DBA 樊振华 www.dbafree.net 2011-10-10
  • 2. 概述JBOSS连接池工作原理 PreparedStatementCache的原理及优化 JBOSS连接池常见的错误
  • 3. 原理篇-JDBC连接数据库JDBC方式连接数据库 连接数据库步骤 加载JDBC驱动 获得数据库连接 执行SQL 关闭数据库连接 创建连接是很耗资源的过程 连接没有重复利用
  • 4. 原理篇-JDBC连接池方式连接池方式 连接池方式步骤 创建连接池和链接 应用通过连接池获得链接 链接创建后被复用 解决了链接复用的问题 复用仅仅在同一JVM范围内 JBOSS连接池
  • 5. 原理篇-JBOSS连接池数据结构核心数据结构是ArrayList 取链接时从链表的尾部取出一个 放回链接是加到链表尾部 连接1连接2连接4连接3HEADTAIL请求获取返还
  • 6. 原理篇-new一个连接池//连接池MAX连接数 this.maxSize = this.poolParams.maxSize; //可以使用的连接事件监听器初始化。 cls = new ArrayList(this.maxSize); /*创建一个对应连接事件监听器的信号集,用于连接的获取。 每次获取连接或者创建连接之前,都需要获取信号量, 当没有可用的信号量时,表示连接已经到达max值。 */ permits = new FIFOSemaphore(this.maxSize); /* 判断jboss配置文件中的prefill设置,默认为false。 如果设置为true,则将本连接池加入到一个临时pool(LinkedList)的最后, 加入的方式是串行的(线程安全)。 */ if(poolParams.prefill) { //fillPool执行了一个fillToMin的方法,即将连接池中的连接,填充到min值。 PoolFiller.fillPool(this); }
  • 7. 原理篇- fillerThread线程PoolFiller在构造函数中即完成fillerThread线程的启动: 包括valitionconnectionPOOLS是一个任务队列
  • 8. 原理篇- fillerThread 执行fillToMin的几个场景: prefill设置为true,启动时。 (prefill这个参数在jboss4.0.5版本以后才能够被支持) prefill为false,第一次getconnetion的时。 在IdleRemoveTimeout后,马上执行fillToMin(减少内存的开销) 在valitionconnection后,马上执行fillToMin
  • 9. 原理篇- fillToMin的实现while (true) { //获取信号量。确保连接没有达到MAX值。 if (permits.attempt(poolParams.blockingTimeout)) { //判断连接池是否已经shutdown?如果已经shutdown,则直接返回。 if (shutdown.get()) return; // 判断连接池中的连接是否已经达到min值,如果已经达到,则直接返回。 if (getMinSize() - connectionCounter.getGuaranteedCount() <= 0) return; // 创建一个连接去填充连接池。每次创建一个,因为这个是死循环。 ConnectionListener cl = createConnectionEventListener(defaultSubject, defaultCri); synchronized (cls) { cls.add(cl); } } permits.release(); }
  • 10. 原理篇- 连接池的初始化protected void initialize() { //将一个连接池对象注册到IdleRemover线程中,表示这个连接池使用IdleRemover来进行管理。IdleRemover线程是空闲连接清理线程,被唤醒的周期是poolParams.idleTimeout/2。即配置的idle-timeout-minutes参数/2。默认idle-timeout-minutes为30分钟,所以清理线程是15分钟运行一次。 if (poolParams.idleTimeout != 0) IdleRemover.registerPool(this, poolParams.idleTimeout); //将一个连接池对象注册到ConnectionValidator线程中,表示这个连接池使用ConnectionValidator来进行管理。IdleRemover线程是一个验证连接池的线程,被唤醒的周期是poolParams.backgroundValidation/2。即配置的background-validation-millis 参数/2。默认background-validation-millis为10分钟,所以验证线程是5分钟运行一次。 if (poolParams.backgroundValidation) { ConnectionValidator.registerPool(this, poolParams.backgroundInterval); } }将连接池注册到IdleRemover线程和ConnectionValidator线程中
  • 11. 原理篇- 清理线程和验证线程IdleRemover 和 ConnectionValidator,两个线程是基于容器存在的,管理容器中的所有连接池,两个线程的管理及实现方式基本是一致的。 IdleRemover默认是15分钟清理一次空闲的连接。 ConnectionValidator默认是5分钟进行一次连接验证。 IdleRemover是synchronized的。 ConnectionValidator是非synchronized操作。
  • 12. 原理篇- 清理线程如何工作?因为初始化的IdleTimeOut被设置为非常大的一个值,所以需要这个判断
  • 13. 原理篇- 清理线程如何工作?连接1连接2连接4连接3HEADTAIL请求获取返还从HEAD到TAIL进行销毁越靠近HEAD,空闲的时间越大清理操作是一个synchronized 的操作
  • 14. 原理篇- 清理线程主体/* 获取cls中的第一个连接,即头部的连接。 * 后面一节中会讲到,在getconnection时,都是从cls的尾部获取。 * 所以,cls头部的连接,肯定是最近最少被使用的。 */ ConnectionListener cl = (ConnectionListener) cls.get(0); //判断是否超时,return lastUse < timeout if (cl.isTimedOut(timeout)) { //销毁连接,并计数 connectionCounter.incTimedOut(); // 销毁这个连接,并加入到销毁队列 cls.remove(0); destroy.add(cl); } else{ //因为它们是一个LRU链表,头部的连接没有超时,其它的连接肯定不会超时。 break; } for (int i = 0; i < destroy.size(); ++i) //销毁Destory队列中的连接 { ConnectionListener cl = (ConnectionListener) destroy.get(i); doDestroy(cl); } // 销毁完空闲的连接后,将连接池填充到最小值的操作。 if (shutdown.get() == false && poolParams.minSize > 0) PoolFiller.fillPool(this);
  • 15. 原理篇- 验证线程主体 //获取信号量,若不能获取,表时当前的连接都在被使用。直接结束validate。 if (permits.attempt(poolParams.blockingTimeout)){ for (Iterator iter = cls.iterator(); iter.hasNext();) { cl = (ConnectionListener) iter.next(); long lastCheck = cl.getLastValidatedTime(); //返回 当前时间 - 上一次check时间 >= 设置的validate时间的第一个连接。 //即表示这个连接没有在backgroundInterval区间内进行check。 if ((System.currentTimeMillis() - lastCheck) >= poolParams.backgroundInterval) { if (cl.getState() != ConnectionListener.DESTROY) { doDestroy(cl); break; } } } // destory之后,也进行一个fillPool的操作 if (destroyed && shutdown.get() == false && poolParams.minSize > 0) { PoolFiller.fillPool(this); } }
  • 16. 原理篇- 如何验证连接这个validate操作如何验证连接,由前台的参数来控制: 支持传入自定义的SQL来验证: 验证SQL java类验证: org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker 直接调用jdbc的ping database(oracle中执行的是select * from dual) 。 Validate接口的实现类如下:
  • 17. 原理篇- 连接池的关闭
  • 18. 原理篇- 连接池的关闭flush()函数清理所有的连接,主要包括: checkd out队列(已经被使用)中的连接。 空闲的队列的连接。 这里将空闲的队列的连接监听进行直接销毁,而checkd out队列的连接设置为DESTROY状态,并没有进行销毁,为什么呢? 我们在下一节的returnConnection方法中可以看以看到会对DESTROY状态的连接进行清理。 shutdown是一个很快的操作,shutdown会销毁空闲的连接,对于使用中的连接,在returnConnection地进行销毁。
  • 19. 原理篇- 设置合理的MIN值在销毁空闲的连接(IdleRemover)和无效的连接(ConnectionValidator)后,都会执行一个prefill的操作,将连接池中的连接数填充到min值。所以,对于连接池min需要合理的进行设置,如果min设置过大,JBOSS会将连接不断的进行销毁->创建->销毁->创建…(idle线程对空闲连接销毁,销毁后小于min值,然后马上又创建,新创建的连接处于空闲状态,于是又被销毁…) 设置合理的MIN值:
  • 20. 原理篇- getConnection的流程
  • 21. 原理篇- getConnection说明新建的连接不会放到连接池中,使用完返回 时放到队列尾。 在第一次getConnection时,会执行fillToMin blocking-timeout-millis 是一个获取信号量的超时时间,如果不能够获取到信号量(连接),则jboss会抛出异常“can not get connection”。 信号量一共有MAX值个,只要当前正在使用的连接数没有到达MAX值,这个信号量一定能够被获取到。 业务在使用连接的过程中,会一直占有这个信号量,在returnConnection或者发生异常时释放信号量。 - 当从连接池中获取连接失败时,是否继续去获取一个新的连接。 这个参数是为了解决SQL校验需要花费大量时间的问题,默认值为 false。(保证业务成功,不需要用sql验证来保证连接是可用的)
  • 22. 原理篇- returnConnection流程
  • 23. 原理篇- returnConnection说明  释放连接是一个线程安全的操作。   在连接return时,有可能已经是destory的状态,这时,直接进行remove即可。   释放连接需要释放信号量。 释放过程中对异常进行处理:    1.释放的连接若不属于连接监听队列(连接池),则加入到连接监听队列中(即连接池中)。    2.在释放过程中,出现任何异常,则将连接从连接池中移除,并进行强制销毁。
  • 24. 原理篇-异常连接销毁默认情况下,JBOSS不会对无效的连接进行销毁。 对异常列表中的连接进行销毁需要配置 org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
  • 25. 原理篇-异常连接销毁MYSQL异常列表:
  • 26. 原理篇-异常连接销毁ORACLE异常列表:
  • 27. 原理篇- 异常销毁扩展在数据源管理中间件中,也存在DB错误的异常列表。 中间件位于JBOSS上层,这里的异常列表有什么作用呢?位于中间件层的异常表表,其范围会比JBOSS连接池中的异常列表范围更大点。比如会额外的增加锁用户等异常的FAILOVER。用于实现某些特定的需求。
  • 28. 原理篇- 连接池的统计信息//获取可用的连接数,判断可用的信号量即可 public long getAvailableConnections() { return permits.permits(); } //获取最大可用的连接数 public int getMaxConnectionsInUseCount() { return maxUsedConnections; } //获取已经被使用的连接数,checkedout是一个hashset. //所有被使用的连接都会首先checkout到这个hashset中,如果只需要获取hashset的大小即可。 public int getConnectionInUseCount() { return checkedOut.size(); }
  • 29. 原理篇- 连接池的统计信息//获取连接数,内部执行:return created - destroyed;即所有创建的连接数-所有销毁的连接数 public int getConnectionCount() { return connectionCounter.getCount(); } //获取连接总的等待时间 public long getTotalBlockTime() { return connectionCounter.getTotalBlockTime(); } //idle timeout的连接数 public int getTimedOut() { return connectionCounter.getTimedOut(); }
  • 30. 原理篇- 连接池的统计信息 //从连接池中getconnection时,总的等待时间/总的连接创建数 public long getAverageBlockTime() { return connectionCounter.getTotalBlockTime() / getConnectionCreatedCount(); } //最大等待时间 public long getMaxWaitTime() { return connectionCounter.getMaxWaitTime(); } //创建连接的总数 public int getConnectionCreatedCount() { return connectionCounter.getCreatedCount(); } //连接销毁总数 public int getConnectionDestroyedCount() { return connectionCounter.getDestroyedCount(); }
  • 31. 原理篇- 连接池的默认参数 public static class PoolParams { public int minSize = 0; public int maxSize = 10; public int blockingTimeout = 30000; //milliseconds public long idleTimeout = 1000 * 60 * 30; //milliseconds, 30 minutes. public boolean backgroundValidation; //set to false by default public long backgroundInterval = 1000 * 60 * 10; //milliseconds, 10 minutes; public boolean prefill; public boolean useFastFail; }
  • 32. 连接风暴连接风暴 当应用启动的时候,经常会碰到各应用服务器的连接数异常飙升。危害 1. 在多个应用系统同时启动时,系统大量占用数据库连接资源,可能导致数据库连接数耗尽。 2. 数据库创建连接的能力是有限的,并且是非常耗时和消耗CPU等资源的,突然大量请求落到数据库上,极端情况下可能导致数据库异常crash。 3. 对于应用系统来说,多一个连接也就多占用一点资源。在启动的时候,连接会填充到max值,并有可能导致瞬间业务请求失败。
  • 33. 连接风暴-连接创建的测试创建连接方式距离(KM)平均每个连接的创建时间(ms)OCI1584.998THIN(MTS)1519.468THIN(DEDICATED)1520.094MYSQL1519.25THIN(DEDICATED)本地7.248OCI本地46.35MYSQL本地6.96416CORE CPU,32G内存空闲机器上进行测试:网络15公里,每次创建500个连接。
  • 34. 连接风暴
  • 35. 连接风暴
  • 36. 连接风暴扩展连接风暴类似的还有 启动时的preparedstament风暴。 在缓存的使用场景中,还有缓存KEY值失效的风暴。(单个KEY值失效,PUT时间较长,导致穿透缓存落到DB上,对DB造成大压力)。 共同点: 高并发。 串行,不够快。 事先准备或者mutex防并发。
  • 37. 应用线程池的保护连接池保护-设置合适的blocking-timeout-millis: 将blocking-timeout-millis参数设置的尽量小一点,这个参数是应用getconnection时的超时时间,只在连接数达到MAX值时才会起作用,因为连接数没到达MAX值,这个获取连接是一个很快的操作,内部仅仅是执行一个获取信号量的操作。 数据路由中间件的保护 防止因为某个库失效造成应用线程堵塞 但某个读库失效,抛出异常时,会将该库标记为无效。 无效的读库2秒钟之内只能有一条线程访问,直到该库恢复。 当只剩一个读库时,即使失效也不会阻止线程访问。
  • 38. 连接池的优化数据库的解析 PreparedStatement原理及作用 PreparedStatementCache的作用 连接池和JVM的内存 Fetchsize,PSCACHE调优 INLISTSQL的优化 其它
  • 39. ORACLE的SQL解析
  • 40. Conn.createStatementString sql = "select * from table_name where id = "; Statement stmt = conn.createStatement(); rset = stmt.executeQuery(sql+"1"); createStatement()方法: 创建一个Statement对象,用于发送SQL语句到数据库。没有使用绑定变量的SQL语句一般使用Statement来执行。如果一个相同的SQL语句被执行多次,则使用PreparedStatement是一个更好的方式。
  • 41. Conn.PreparedStatement String v_id = 'xxxxx'; String v_sql = 'select name from table_a where id = ? '; //创建con.prepareStatement,并执行SQL预编译。 stmt = con.prepareStatement(v_sql); stmt.setString(1, v_id ); //为绑定变量赋值 stmt.executeQuery(); PreparedStatement prepareStatement(String sql)方法: 创建一个PreparedStatement对象,用于发送使用绑定变量的SQL语句到数据库中。 SQL能够被预编译,并且存储到一个PreparedStatement对象中。这个对象,可以在多次执行这个SQL语句的块景中被高效的使用。 如果驱动程序支持绑定变量,方法prepareStatement将会发送一个SQL语句到数据库,以执行预编译(即数据库中的解析)。也有一些驱动不支持预编译,在这种情况下,在PreparedStatement执行之后,语句才会被发送到数据库,这对于用户没有直接的作用。 我们可以知道,只要使用了prepareStatement,就可以避免SQL的解析,主要是硬解析。因为执行计划存在于ORACLE用户进程或者ORACLE共享内存中了。
  • 42. PreparedStatementCache作用 什么是PreparedStatementCache? PreparedStatementCache即用于保存与数据库交互的prepareStatement对象。 PreparedStatementCache使用了一个本地缓存的LRU链表来减少SQL的解析和一次网络的交互,因为解析对于数据库来讲是一个很大的开销,网络的交互对响应时间会有比较大的延时。
  • 43. PreparedStatementCache初始化prepared-statement-cache-size JBOSS的配置文件中有个参数: 50 连接被创建的时候就会初始化PSCACHE的大小:( 这段代码在BaseWrapperManagedConnection函数的构造函数中,连接创建的时候,会初始化一个最小值为2,最大值为max的一个LRU双向链表)
  • 44. PreparedStatementCache结构PSCACHE对象其实是一个是一个LRU List,即它使用了一个双向链表来存储PreparedStatement的值,这个LRU链表的数据结构如下:
  • 45. PreparedStatement方法代码
  • 46. PSCACHE总结在ORACLE数据库中,使用PreparedStatement能够显著的提高系统的性能,绕过数据库的解析,前提是SQL都使用了绑定变量。 在MYSQL数据库中,因为没有绑定变量这个概念,MYSQL本身在执行所有的SQL之前,都需要进行解析,因此在MYSQL中这个值没有意义。 而PSCACHE是用于保存PS的一个LRU链表,可以减少由PS引起的一次预编译(软软解析,open cursor操作),同时减少一次网络的开销。 PreparedStatementCache不是设置越大越好,毕竟,PreparedStatementCache是会占用JVM内存的。
  • 47. ORACLE连接池和JVM内存JVM内存占用计算: JVM内存占用大小=(连接池1中的连接数*PSCache大小*平均每个PS的内存占用)+(连接池2中的连接数*PSCache大小*平均每个PS的内存占用)+(连接池3中的连接数*PSCache大小*每个PS的内存占用) 假设一个应用有3个连接池,每个连接池有10个连接,每个PreparedStatement平均大小为200K。 当PSCache为30时:内存大小=3*10*200K*30=175.78MB 当PSCache为50时:内存大小=3*10*200K*50=292.975MB 当PSCache为100时:内存大小=3*10*200K*100=585.95MB DML SQL由于不需要保存查询结果集,占用的PS大小可以忽略,一般在10KB左右。
  • 48. MYSQL连接池和JVM内存MYSQL的preparestament基本上不占用内存: MYSQL不支持客户端游标,但是客户端API通过把结果取到内存中,可以模拟游标操作。 不什么不支持客户端游标,这个是由MYSQL 客户端/服务器协议决定 的。MYSQL的这个协议是半双工的,即MYSQL只能在给定的时间,发送或接受改写,但不能同时发送和接收。(在接收完之后,才能释放锁等资源)。 这种协议使MYSQL在正常的情况下,查询的效率更高,对于大结果集的查询,MYSQL是客户端的内存占会大大的增加。这也是为什么preparestamentcache中,MYSQL基本不占用内存的原因
  • 49. 决定JVM内存的因素影响JVM内存的因素: 连接池个数:这个和应用架构有关系,不容易调整。 连接池中的连接数:由DAO处理时间及业务量决定。 PSCache大小:这个参数可以由我们来控制,和PS的大小关系很大,需要合理设置这个参数。 PS大小:由具体访问的SQL语句和fetchsize决定 。 JVM内存空间的计算最关键的问题变成了如何计算PreparedStatement的大小。 影响PreparedStatement大小的,只有fetchsize和SQL语句本身。 如何节约JVM内存?
  • 50. 如何节约JVM内存降低应用连接池的个数: 合并同一物理库上的数据源。这个降低PSCACHE的命中率。 降低连接数: 提高DAO响应时间,优化SQL,增加PSCACHE。 设置合理的FETCHSIZE: 降低 PreparedStatement的大小 。 SQL优化 提高SQL的响应时间。 ORACLE中建表时按实际需求设定字段长度。 不要写SELECT * 等,SQL中只写需要查询的字段。 INLIST SQL的优化。
  • 51. PS大小的计算表结构: Test10000: col_a varchar2(4000) , col_b varchar2(4000), col_c varchar2(2000) SQL: Select * from test10000 where col_a=:1 假设fetchsize大小为10,那PS的大小为多少呢?
  • 52. PS大小的计算fetchsize设置为10(jdbc默认值),占用空间为: select字长总长度(字节)*fetchsize*2(字符集) 连接方式理论计算结果集char数据真实大小OCI方式10000字节*10CHAR[100030]200KBTHIN方式10000字节*10CHAR[100030]200KBPS中占用空间的主要是CHAR结果集数组,在早期的JDBC版本中,使用的一个OBJECT数据来保存结果集。 Object数组中的元素又是以字段的个数来分别保存string字符串的。 CHAR数组减少了JVM内存的碎片,可以减轻GC的压力。
  • 53. PS大小与select字段的关系字段长度PS大小 (字节)PS大小/字段长度141511495269.376105.6652799141511495269.376105.66527994412479232108.62012694412479232108.62012694412479232108.62012693736404172.8108.18329763000319488106.4962851319488112.06173273000317440105.81333332391259072108.35299042282246784108.14373361576178176113.05583761576178176113.055837672584992117.230344843852224119.232876740150688126.40399与PS的大小有直接关系的是: FETCHSIZE
  • 54. 什么是FETCHSIZE? FETCHSIZE,从数据库的角度来说,就是每次从数据库中取多少条记录,即批次大小。假设有100条记录: Fetchsize=100,取1次。 Fetchsize=10,取10次。 Fetchsize=1,取100次。 Fetchsize对客户端内存的影响: 客户端需要fetchsize条记录的内存大小来保存查询的结果集。每个记录为1KB,fetchsize为10,则客户端需要1KB*10=10KB的内存大小。 Fetchsize对网络的影响: 由于数据需要多次获取,会增加网络的交互次数。(Wireshark)
  • 55. 数据库中的FETCHSIZE数据库端也有个Fetchsize,会对逻辑读的影响: 数据库角度来讲,如果100条记录在1个块中,则fetchsize=1会产生100个逻辑读。 游标中Fetchsize对应内存空间的分配: 数据库中fetchsize对应的内存是动态分配的。 应用端fetchsize对应的内存不是动态分配的。
  • 56. FETCHSIZE的设置Fetchsize可以在应用的任何一层进行设置 ORACLE JDBC驱动默认的FETCHSIZE为10。 语句级别的设置 : Ibatis, hibernate等框架上设置 。 直接使用进行设置 Preparedstatement .setFetchSize() JBOSS连接中设置: 50
  • 57. FETCHSIZE方法可以在JDBC驱动类Oracle.jdbc.driver.OracleStatment中找到这个方法, Fetchsize默认值为0,伪代码如下: void setPrefetchInternal(int paramInt){ if (paramInt < 0) { DatabaseError.throwSqlException(68, "setFetchSize"); } else if (paramInt == 0) { paramInt = this.connection.getDefaultRowPrefetch(); } if (paramInt == this.defaultRowPrefetch) return; this.defaultRowPrefetch = paramInt; if ((this.currentResultSet == null) || (this.currentResultSet.closed)) { this.rowPrefetchChanged = true; } }
  • 58. FETCHSIZE的测试ResultSet rs = null; stmt = conn.prepareStatement(sql); stmt.setFetchSize(50); stmt.setString(1, “test”); rs = stmt.executeQuery(); //打开游标,并不获取数据。 while (rs.next()){ } 查询的表一共有300条记录,测试中查询的结果集为空,执行的是全表扫描。 SQL> select count(*) from test10000;     COUNT(*) ----------        300    SQL> select * from test10000 where col_a='test';   no rows selected  
  • 59. FETCHSIZE的测试数据库连接方式PSCACHEfetchsize字段长度网络距离总记录数返回记录执行时间 (ms)ORACLEoci支持11000015KM30001.5875ORACLEoci支持51000015KM30001.5828ORACLEoci支持101000015KM30001.7781ORACLEoci支持501000015KM30002.0468ORACLEoci支持1001000015KM30002.6656ORACLEoci支持110000本地30000.1646ORACLEoci支持510000本地30000.1713ORACLEoci支持1010000本地30000.1898ORACLEoci支持5010000本地30000.3431ORACLEoci支持10010000本地30001.2609ORACLEthin支持11000015KM30001.6344ORACLEthin支持101000015KM30001.6687ORACLEthin支持1001000015KM30001.6266MYSQLjdbc支持11000015KM30001.5187MYSQLjdbc支持101000015KM30001.6093MYSQLjdbc支持1001000015KM30001.5906
  • 60. FETCHSIZE的测试数据库连接方式PSCACHEfetchsize字段长度网络距离总记录数返回记录执行时间 (ms)ORACLEoci支持110015KM300300226.9533ORACLEoci支持510015KM30030086.44667ORACLEoci支持1010015KM30030043.74667ORACLEoci支持5010015KM30030010ORACLEoci支持10010015KM3003005.2ORACLEoci支持110015KM300108.44ORACLEoci支持510015KM300102.9ORACLEoci支持1010015KM300101.56ORACLEoci支持5010015KM300101.56ORACLEoci支持1100本地30030012.773ORACLEoci支持5100本地3003005.32ORACLEoci支持10100本地3003002.9ORACLEoci支持50100本地3003000.87ORACLEoci支持100100本地3003000.65ORACLEthin支持110015KM300300447.653ORACLEthin支持1010015KM30030047.4933ORACLEthin支持5010015KM30030010.8333ORACLEthin支持10010015KM3003006.56
  • 61. FETCHSIZE-内存-性能
  • 62. FETCHSIZE-网络的影响FETCHSIZE达到一定程序后,开销会越来越大,因为数据的传输还受到网络的影响: 操作系统上控制网络的读/写 buffer net.core.rmem_default = 262144 net.core.wmem_default = 262144 数据库端控制,默认值为操作系统上的设置 : RECV_BUF_SIZE=9375000 SEND_BUF_SIZE=9375000
  • 63. 设置fetchsize的重要性FETCHSIZE直接决定了PS的大小,所以JDBC,IBATIS, hibernate框架,连接池都可以调整这个参数,可见这个值对内存的影响有多大。 XX系统中,都是查询为主,在数据库拆分,由于数据源的增加,SQL版本的增加,导致应用服务器内存被撑爆,最根本的原因是由于fetchsize设置不合理导致的。 XX系统上占用内存最大的SQL select * from (select * from XXXXX_04 where xx_no … ) where rownum<2Fetchsize字段总长真实内存占用保存结果集的数组大小50141511.426MBChar[705200] :1.41MB(14151*50)1014151285KBChar[141040] :256KB(14151*10)11415140KB左右Char[14104] :25.6KB(14151*1)如果支持在SQL级别设置FETCHSIZE为1:调整这一个SQL,单台应用可节省内存:5*8*1.38M=55.2MB (5为数据源个数8为,每个数据源中的连接数。) 设置为10,可节约内存46.4MB
  • 64. 设置合理的PSCACHE如何设置PSCache? PSCache最好能够覆盖95%的应用SQL,然后可以在95%这个值以上,再上浮5-10个。 SQL1: select * from text1; 应用调用占比重45% SQL2: select * from xxx where; 应用调用占比重25% SQL3: select * from xxx ; 应用调用占比重15% SQL4: select * from xxx; 应用调用占比重10% SQL5: select * from xxx; 应用调用占比重3% SQL6: select * from xxx; 应用调用占比重1.3% 我们统计,SQL1,SQL2,SQL3,SQL4已经占比了95%的调用,因此PSCache我们可以设置为9-14之间,仅供参考。
  • 65. prepareStatementCache的测试数据库连接方式prepareStatementCache网络距离每次执行时间(ms)ORACLEoci支持15KM1.5484ORACLEoci不支持15KM2.0015ORACLEthin支持15KM1.5937ORACLEthin不支持15KM2.9093ORACLEoci支持本地0.1625ORACLEoci不支持本地0.6631ORACLEthin支持本地0.2695ORACLEthin不支持本地0.6555ORACLEoci支持select * from dual where DUMMY=?15KM1.3249ORACLEoci支持select * from dual where DUMMY=?本地0.1432MYSQLjdbc支持15KM1.5124MYSQLjdbc不支持15KM1.5344MYSQLjdbc支持本地0.3195MYSQLjdbc不支持本地0.3792本机到机房的距离约为15KM: 15KM/(200000KM/s)=0.075ms    实际光速为300,000,000米/秒,因为光纤中的传播,有些拦损耗,所以传播速度计算为: 200,000,000米/秒 0.075ms*2=0.15ms           *2表示网络往返1次。 交换机延时:0.3ms(往返) 所以网络的交互时间在:0.3+0.15=0.45ms左右,即prepareStatement一次需要额外的0.45ms左右。
  • 66. 调整fetchsize和pscache调整PSCACHE,依照网络而定,每个SQL提高响应在0.4-0.6ms左右。 调整fetchsize,fetchsize越大,内存管理的负担越重,由前面的测试可以知道,将fetchsize从50降至10,可以提高响应0.2-0.3ms左右,测试表比较宽,可以假设为0.1-0.3ms。 综合这些参数的调整,单个SQL可提高响应0.5-0.9ms。对于一笔交易的创建,或者单个客户模型的捞取,可以提高响应多少?
  • 67. A核心库PSCACHE命中率计算A1:3000次/STotalper Secondexecute count10,243,8825,692.22parse count (hard)280.02session cursor cache hits2,555,6251,420.08parse count (total)2,805,4841,558.92parse count (failures)190.01pscache命中率47.660% 命中效=不解析次数/总执行次数: (execute count - session cursor cache hits - parse count (total))/ execute count下调fetchsize,加大PSCACHE: 减少单个DB每秒3000次解析,CPU负载可以下降。 每秒3000次网络交互,提高响应延时:3000*0.5ms。5个A库.. 减少内存占用9倍,连接数占用减少,减少gc发生次数等等A2:3000次/STotalper Secondexecute count10,239,3215,687.22parse count (hard)260.01session cursor cache hits2,513,5861,396.12parse count (total)2,772,6631,540.02parse count (failures)180.01pscache命中率48.370% 
  • 68. A核心库PSCACHE调整后A1Totalper Secondexecute count19,453,57810,807.00session cursor cache hits899,470499.68parse count (total)970,058538.89pscache命中率90.38% A2Totalper Secondexecute count19,995,41211,108.68session cursor cache hits914,265507.93parse count (total)986,248547.92pscache命中率90.49% 
  • 69. A核心库库PSCACHE调整后A系统: 调整: Fetchsize :50->5,pscache:50->150。连接数min:10->8 连接数max: 20-17   (min连接数可以继续下调2个左右) DAL响应时间:(调整前数据没有)约为1.88ms,调整后为1.08ms,提升幅度 42.56% JVM内存下降:    正常业务压力,下降150MB左右。 B系统: Fetchsize : 50->20,pscache:15->30。连接数min:10->5 连接数max: 20-12        (pscache可以继续上调,min连接数可以继续下调1个左右) DAL响应时间:   2.45ms优化至1.39ms,提升幅度达43.3%(why?) 服务平均响应时间: 5.40ms优化至3.43ms,提升幅度达36.5% JVM内存:有一定下降,理论上下降幅度20%以上,基数小,未计算。 连接数: 单个DB连接数已下调 700个左右,并且还有300个左右的连接是过度空闲的。总计可以下调1000个左右。
  • 70. A核心库PSCACHE-数据库等待事件 调整前 调整后参考:http://blogs.warwick.ac.uk/java/entry/wait_class_network/
  • 71. B核心库PSCACHE命中率计算B写库Totalper Secondexecute count1,163,375646.34session cursor cache hits318,324176.85parse count (total)383,234212.92pscache命中率39.600% PSCACHE调整效果: 设置PSCACHE从20->35,命中率从39.300%增加到83.500%。 提高DAO响应延时0.4~0.6ms。PSCACHE命中率
  • 72. C库PSCACHE命中率计算C库Totalper Secondexecute count42,757,51323,758.49session cursor cache hits9,0875.05parse count (total)25,88514.38pscache命中率99.92% C库的PSCACHE设置为180,fetchsize为50,因为表比较少,并且为窄表。
  • 73. INLIST SQL的优化什么是inlist SQL? in list的SQL就是指使用了in来进行查询,绑定变量个数不确的SQL。 如: select * from test where id in (:1,:2,:3,….:n) Inlist SQL有什么危害? 浪费应用的PSCACHE。 也浪费一点数据库的SHARE POOL 解决方案?
  • 74. INLIST优化-减少SQL版本方案1:使用Ppline Function实现一层转换。 这个函数的作用是将以逗号分隔的字符串转换成一个table,如下: zhoucang@zhoucang>select * from TABLE(str2varlist('123,456,789,012,345'));  COLUMN_VALUE ---------------------------------------------------------------------------------------------------- 123 456 789 012 345   5 rows selected. 方案2:固定in后面条件的个数。 每次传入不同的值,不足的可以使用一个不存在的值来进行补充。如: select * from test where id in(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10) 固定10个ID的查询,不足10个以-1来补充(如果ID不为负数)。
  • 75. INLIST 优化方案比较说明:应用总执行时间:秒内存读(块)返回记录数单条SQL CPU时间(us)单条SQL响应时间3个绑定变量,传3个id389375.8436575.8436520个绑定变量,传3个id3910.06394.4899594.48995100个绑定变量, 传3个id4310.059553112.8007112.8007PPLINE,3个id419.014353149.5074149.5074100个绑定变量,传10个id5331.0595510220.55765220.5576510个绑定变量,传10个id433010174.7577174.7577PPLINE,10个id5130.0379510358.46375358.46375
  • 76. INLIST 优化方案比较PPLINE方案固定绑定变量个数CPU数据库需要对字符串进行解析,增加CPU的消耗1倍左右CPU的消耗,增加幅度在20%~60%之间,具体因in的个数而定。IO对IO基本无损耗增加一个逻辑读。这一个逻辑读是由于查询-1时产生的,主要是由于索引branch节点查询引起,由于root节点一定需要查询,索引一般为3-4层,可以认为,这个逻辑读开销在1-3个左右,基本上可以认为在1个左右。响应时间数据库响应时间增加一倍。增加幅度在20%~60%,之间,具体因in的个数而定。显然,选择方案2会更加节省数据库的资源。 根据执行的频率,评估适当的多给出几个版本
  • 77. 参考链接:JBOSS连接池1-PreparedStatementCache参数的作用及原理: http://www.dbafree.net/?p=287 JBOSS连接池2-jboss连接池的启动及prefill参数配置: http://www.dbafree.net/?p=300 JBOSS连接池3-JBOSS连接池的初始化及关闭: http://www.dbafree.net/?p=342 JBOSS连接池4-从连接池中获取连接及返还连接: http://www.dbafree.net/?p=378 JBOSS连接池调优1-大规模应用集群如何防止连接风暴:http://www.dbafree.net/?p=438 JBOSS连接池调优2-合理的设置PreparedStatementCache:http://www.dbafree.net/?p=458 JBOSS连接池调优3-提高PSCACHE的命中率-inlist查询的优化: http://www.dbafree.net/?p=546 JBOSS连接池调优4-合理设置连接数的min值和max值:http://www.dbafree.net/?p=572 JBOSS连接池调优5-合理的设置fetchsiz:http://www.dbafree.net/?p=597 JBOSS连接池博客文章合集:http://www.dbafree.net/?p=655 JBOSS连接池附录:获取连接池中相关的统计信息:http://www.dbafree.net/?p=411 oracle 的sql解析过程: http://www.dbafree.net/?p=8 一个生产库的JBOSS连接池调整优化及分析: http://www.dbafree.net/?p=150
  • 78. Q&A谢谢!