JBOSS连接池调优2-合理的设置PSCACHE

1.对于PSCache设置过大或过小的影响

为什么我们要合理的设置PSCache的值，这个值对于应用系统有怎样的影响呢？当然，之所以要把这个问题提出来，肯定是有原因的，在这个参数的设置问题上，我们是吃过亏的。所谓合理的设置，即这个值既不能设置太大，也不能设置太小。PSCache设置过小，可能导致PSCache命中率降低，最直观的影响，就是应用访问数据库延时增加，具体分析一下，主要有以下几方面的影响：

1. 对于没有在PSCache中的SQL,每次需要在应用程序中新建PS对象。（PS对象的大小和具体的SQL有关，一般可以认为在几K～1MB之间，具体大小需要以JVM的内存DUMP为准。后面会详细介绍。）
2. 新建PreparedStatement，需要进行一次网络的交互，这个开销非常大的。（主要由于交换机的延时及实际距离产生的代价，一般可以认为这一次网络的交互在0.3～0.5ms之间。）
3. 新建PreparedStatement,需要数据库的一次解析操作，而解析操作是非常消耗数据库资源的，一般一次解析的时间，我们可以认为在0.05ms左右。

以上就是PSCache不能命中时主要的代价。实际经验中，我们碰到过因PSCache太小的情况下，应用访问增加了0.6~0.8ms的延时，这个影响是很大的。并且，PSCache的增大，可以消除ORACLE的等待事件:”SQL*Net more data from client”，这个等待事件在某生产库上比较厉害，调整PSCache之后，由于该等待事件的下降，应用DAO的平均响应时间实际提升达到了将近1ms。关于该等待事件的详情，参考:http://blogs.warwick.ac.uk/java/entry/wait_class_network/

显然，PSCache设置太小对性能影响很大，但是设置的过大，也会产生问题，PSCache设置过大有可能耗尽应用服务器的内存，导致应用内存被撑爆。

那么如何来设置PSCache这个值呢，首先我们需要知道它占用的内存大小。

2.连接池内存耗费的计算

我们知道，一个连接池中有多个数据库连接，每一个连接都有单独的PSCache，并且，连接池中占空间最大的就是PSCache（占用95%以上的空间大小）。因此，JVM中连接池的内存开销可以大致计算如下：

JVM内存占用大小=（连接池1中的连接数*PSCache大小*平均每个PS的内存占用）+（连接池2中的连接数*PSCache大小*平均每个PS的内存占用）+（连接池3中的连接数*PSCache大小*每个PS的内存占用）

假设一个应用有3个连接池，每个连接池有10个连接，每个PreparedStatement平均大小为200K。当PSCache分别设置为30和100时，它们的内存占用情况分别如下：

当PSCache为30时：内存大小=3*10*200K*30=175.78MB
当PSCache为100时：内存大小=3*10*200K*100=585.95MB

两者PSCache相差了400多MB，和内存大小有关系的4个参数，分别是：连接池个数，连接池中的连接数，PSCache的大小,PS大小。

连接池个数：这个和应用架构有关系，如果不能省略数据库的访问或者通过接口调用来访问，这一块的开销必不可少。
连接池中的连接数：这个由业务的处理时间及业务量决定。我们只能合理的设置连接池的min值为max值。
PSCache大小：这个参数可以由我们来控制，和PS的大小关系很大，需要合理设置这个参数。
PS大小：由具体访问的SQL语句决定，这个值决定了PSCache大小的设置。因此，JVM内存空间的计算最关键的问题变成了如何计算PreparedStatement的大小。

3.如何计算PreparedStatement（PS）的大小

1. 进行连接池内存计算的关键值就是PreparedStatement的大小。我们可以对正在运行的JBOSS容器，作一个JVM内存的DUMP（方法：使用jmap来产生一个DUMP文件），然后使用Memory Analyzer (MAT)工具来查看JVM内存里面的具体内容。下面是我自已测试的一个DUMP结果，如下：

测试用例中执行了一个SQL为：

Select * from test10000 where col_a=:1

Test10000的表结构如下：

ZHOUCANG/ZHOUCANG@TEST>create table test10000
ZHOUCANG/ZHOUCANG@TEST>(col_a varchar2(4000) ,
ZHOUCANG/ZHOUCANG@TEST> col_b varchar2(4000), col_c varchar2(2000));
Table created.

3.1 使用OCI方式连接数据库

用OCI方式连接数据库，可以看到调用的是JDBC的T2CPreparedStatement方法，里面有一个CHAR[100030]的数组，占用了200KB左右的内存，这个就是用于保存数据库查询结果的char数组。这里的fetchsize设置为10（jdbc默认值）。同样使用OCI方式，我们将fetchsize设置为100，则获取到的char数据长度也增大了10倍，为CHAR[1000300],大小在2MB，如下图：

可见PreparedStatement占用内存的大小，和fetchsize有很大的关系。同样的，char数组保存了查询的结果集，所以，查询的字段的总长度决定了也PreparedStatement对象的大小。有了fetchsize和查询字段总长度，我们就可以计算出PS内存占用。如SQL：Select* from test10000，选取的字段长度为10000，内存占用为200KB，fetchsize为10。则可以得出这个比例为：

200K/10（fetchsize）/10000（字段总长）=2

为什么这个比值是2呢？

因为Java的chars需要2个字节来保存char。10000（字节）长度的字段，当fetchsize为10时，需要保存的占用的JVM内存的char数组长度为：10000字节*10=100KB，即char[100030]，而本地客户端的编码为GBK，所以，char[100030]的字符数组转化为字节数，大约会占用200KB的内存空间。

3.2 使用thin的方式连接数据库

这里使用了thin方式来连接数据库，fetchsize设置为100，保存的数据结构同样是CHAR数据，THIN方式与OCI方式没有区别，并且PreparedStatement内存占用也基本一致。唯一的区别是，thin方式调用的是JDBC的T4CPreparedStatement方法，而OCI方式调用的是T2CPreparedStatement。具体这两个方法的区别，有兴趣的同学可以研究下JDBC的驱动。

3.3关于char数组的补充

以上测试基于OJDBC10.2.3版本进行测试，在早期的JDBC驱动中，我们可以看到select返回结果集是以一个Object数组的形式来保存，如fetchsize为10，这个数组为Object[10],fetchsize为100，则数组为Object[100]。并且Object数组中的元素又是以字段的个数来分别保存string字符串的，即有多少个查询字段，就会有多少个string字符串。对于这种存储方式，我的理解是，减少了JVM内存的碎片，可以减轻GC的压力。

4 真实场景中计算PreparedStatement对象

根据select字段的长度，我们就可以计算出每个PreparedStatement在内存中的大小。当然，这里需要两个值，fetchsize(默认为10)，select字段长度的总和。有了这两个值之后，就可以精确的计算PreparedStatement的大小了。如，以下为某系统的DUMP统计结果，供参考：

字段长度	内存PS大小 (字节)	PS大小/字段长度	说明
14151	1495269.376	105.6652799	因为PS对象中除了char数组，还有其它的变量，所以当字段长度较小时（即char数据较小） “PS大小/字段长度”,这个比重就增大了。
14151	1495269.376	105.6652799
4412	479232	108.6201269
4412	479232	108.6201269
4412	479232	108.6201269
3736	404172.8	108.1832976
3000	319488	106.496
2851	319488	112.0617327
3000	317440	105.8133333
2391	259072	108.3529904
2282	246784	108.1437336
1576	178176	113.0558376
1576	178176	113.0558376
725	84992	117.2303448
438	52224	119.2328767
401	50688	126.40399

使用图表关系来查看如下，可以发现这些点可以构成一个一元二次方程，偏差在99.99%以内：

 

计算内存和字段总长的比例：

1495269.376字节/fetchsize（50）/14151=2（约等于）

实际这个比值，应该以真实环境的内存DUMP为准。知道了如何计算PreparedStatement对象的大小后，我们对于应用内存的控制可以做到心中有数了。当然，如果应用内存不吃紧，可以适当的加大这个值，以提升SQL的效率。

补充一点：如果PSCache中有in的SQL，如“select * from t where id in(:1,:2)”和“select * from t where id in(:1,:2,:3)”，这在PreparedStatementCache中是两个不同的对象，因为in中条件个数的不同，可能导致PreparedStatementCache的命中率急剧下降。这里有一种方法可以解决，详见后面第三章节的内容。

5 总结计算连接池占用JVM内存的公式及PSCache设置参考

以单个数据源为例（多个数据源累加即可），假设当前连接数为a,每个连接的PreparedStatementCache为b，并且“内存/字段总长”=c，字段平均的长度为d

连接池占用的内存大小=a*b*c*d。

最后，给出一个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占了SQL总访问量的95%，因此我们可以将PSCache设置在9-14这个区间。

以上测试及数据仅供参考，请以真实环境数据为准进行评估。

记住，一定要给应用预留足够多的内存，即使PSCache设置在平时是合理的，但是因为连接数可能会由于应用异常或者业务冲峰而陡增，按上面的计算公式，还是可能会导致JVM内存被撑爆。

 

 

 

 

 

 

附：
OCI连接数据库

package com.alipay.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/*
 *  jmap -dump:format=b,file=test.bin 4939
 */

public class Test_Tns {
	public static void main(String[] args) throws InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		String dbUrl = "java:oracle:oci:@xxx";
		Connection conn = null;
		PreparedStatement stmt;
		ResultSet rs = null;
		String sql = "";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			conn = DriverManager.getConnection(dbUrl, "zhoucang", "zhoucang");
			sql = "select * from test10000 where col_a=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "test");
			stmt.setFetchSize(10);
			System.out.println(stmt.getFetchSize());
			rs = stmt.executeQuery();
//			System.out.println(stmt.get);
			stmt.getMetaData();
			while (rs.next()) {
				//System.out.print(rs.getString("COL_A"));
			}
			System.out.println(stmt.getQueryTimeout());

			stmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

THIN连接数据库

package com.alipay.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 *  jmap -dump:format=b,file=test.bin 4939
 */

public class Test {
	public static void main(String[] args) throws InstantiationException,
			IllegalAccessException, ClassNotFoundException {

		String dbUrl = "jdbc:oracle:thin:@10.xx.xx.xx:1521:sid";
		Connection conn = null;
		PreparedStatement stmt;
		ResultSet rs = null;
		String sql = "";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			conn = DriverManager.getConnection(dbUrl, "zhoucang", "zhoucang");
			sql = "select * from test10000 where col_a=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "test");
			stmt.setFetchSize(100);
			System.out.println(stmt.getFetchSize());
			rs=stmt.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getString("COL_A"));
			}

			stmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
	}
}
}