P6Spy监控SQL语句及性能测试

Donald_Draper

浏览: 980934 次

最近访客更多访客>>

lyndon.lin

yyltty

pengranxiang

qinjingkai

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

P6Spy

p6spymysql log4j

P6Spy官网：http://p6spy.github.io/p6spy/2.0/configandusage.html
P6Spy下载：http://sourceforge.net/projects/p6spy/files/
P6Spy介绍：http://www.open-open.com/lib/view/open1429863625385.html
配置sqlP6Spy配置信息说明：http://blog.csdn.net/z69183787/article/details/43453581
P6Spy和sping配合显示真正的SQL：http://blog.csdn.net/rznice/article/details/7044614
P6Spy源码分析,理解跟踪SQL的工作原理：http://blog.csdn.net/sunyujia/article/details/2207027
测试实例：

package p6spy;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.p6spy.engine.spy.P6DataSource;


public class testP6Spy {
	private static Logger log = LoggerFactory.getLogger(testP6Spy.class);
	private static P6DataSource p6DSource =null;
	private static ComboPooledDataSource cpDSource =null;
	private static void initP6SpyDataSource(){
		Properties props = new Properties();
		InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("c3p0.properties");
		try {
			props.load(in);
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		finally{
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		cpDSource = new ComboPooledDataSource();
		try {
			cpDSource.setDriverClass(props.getProperty("driver"));
			log.info("=============driver:"+props.getProperty("driver"));
			cpDSource.setJdbcUrl(props.getProperty("url"));
			log.info("=============url:"+props.getProperty("url"));
			cpDSource.setUser(props.getProperty("user"));
			log.info("=============user:"+props.getProperty("user"));
			cpDSource.setPassword(props.getProperty("password"));
			log.info("=============password:"+props.getProperty("password"));
			cpDSource.setInitialPoolSize(5);
			cpDSource.setMinPoolSize(30);
			cpDSource.setMinPoolSize(5);
			/*c3p0全局的PreparedStatements缓存的大小。
			如果maxStatements与maxStatementsPerConnection均为0，则缓存不生效，
			只要有一个不为0，则语句的缓存就能生效。如果默认值: 0*/
			cpDSource.setMaxStatements(100);
			//最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0
			cpDSource.setIdleConnectionTestPeriod(60);
			/*如果为false，则获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常，
			但是数据源仍有效保留，并在下次调用getConnection()的时候继续尝试获取连接。
			如果设为true，那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。默认: false*/
			cpDSource.setBreakAfterAcquireFailure(false);
			//定义在从数据库获取新连接失败后重复尝试的次数。默认值: 30 ；小于等于0表示无限次
			cpDSource.setAcquireRetryAttempts(30);
			cpDSource.setTestConnectionOnCheckout(false);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
		p6DSource = new P6DataSource(cpDSource);
		log.info("========Init p6DSource end=============");
	}
	
	
	public static void main(String[] args){
		initP6SpyDataSource();
		testP6SpyBaseMysql();
	}
	public static void testP6SpyBaseMysql()
	{
		
	    Connection con = null;// 创建一个数据库连接
	    PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
	    ResultSet result = null;// 创建一个结果集对象
	    try
	    {
	        con = p6DSource.getConnection();
	        String iSql = "INSERT INTO user(name,age)VALUES(?,?)";
	        PreparedStatement ps =con.prepareStatement(iSql); 
	        ps.setString(1, "jack");
	        ps.setInt(2, 23);
	        ps.execute();
	        ps.setString(1, "mark");
	        ps.setInt(2, 67);
	        ps.execute();
	        String uSql = "UPDATE user SET name = ?,age=? WHERE id=?";
	        ps =  con.prepareStatement(uSql); 
	        ps.setString(1, "donald");
	        ps.setInt(2, 28);
	        ps.setInt(3, 11);
	        ps.executeUpdate();
	    }
	    catch (Exception e)
	    {
	    	log.error("============ERROR:"+e.getMessage());
	    	e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
}

测试结果：
八月 23, 2016 6:42:43 下午 com.mchange.v2.log.slf4j.Slf4jMLog$Slf4jMLogger$InfoLogger log
信息: MLog clients using slf4j logging.
八月 23, 2016 6:42:44 下午 com.mchange.v2.log.slf4j.Slf4jMLog$Slf4jMLogger$InfoLogger log
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
八月 23, 2016 6:42:44 下午 p6spy.testP6Spy initP6SpyDataSource
信息: =============driver:com.mysql.jdbc.Driver
八月 23, 2016 6:42:44 下午 p6spy.testP6Spy initP6SpyDataSource
信息: =============url:jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
八月 23, 2016 6:42:44 下午 p6spy.testP6Spy initP6SpyDataSource
信息: =============user:donald
八月 23, 2016 6:42:44 下午 p6spy.testP6Spy initP6SpyDataSource
信息: =============password:123456
八月 23, 2016 6:42:44 下午 p6spy.testP6Spy initP6SpyDataSource
信息: ========Init p6DSource end=============
八月 23, 2016 6:42:44 下午 com.mchange.v2.log.slf4j.Slf4jMLog$Slf4jMLogger$InfoLogger log
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2sigdn9i1ervxzto1pef0|64cd046, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2sigdn9i1ervxzto1pef0|64cd046, idleConnectionTestPeriod -> 60, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
p6spy - 2016-08-23 18:42:44 -0    [p6spy] INFO    - 06:42:44,822|100|1|statement|INSERT INTO user(name,age)VALUES(?,?)|INSERT INTO user(name,age)VALUES('jack',23)
p6spy - 2016-08-23 18:42:44 -3    [p6spy] INFO    - 06:42:44,826|104|2|statement|INSERT INTO user(name,age)VALUES(?,?)|INSERT INTO user(name,age)VALUES('jack',23)
p6spy - 2016-08-23 18:42:44 -22   [p6spy] INFO    - 06:42:44,845|19|1|statement|INSERT INTO user(name,age)VALUES(?,?)|INSERT INTO user(name,age)VALUES('mark',67)
p6spy - 2016-08-23 18:42:44 -22   [p6spy] INFO    - 06:42:44,845|19|2|statement|INSERT INTO user(name,age)VALUES(?,?)|INSERT INTO user(name,age)VALUES('mark',67)
p6spy - 2016-08-23 18:42:44 -23   [p6spy] INFO    - 06:42:44,846|0|1|statement|UPDATE user SET name = ?,age=? WHERE id=?|UPDATE user SET name = 'donald',age=28 WHERE id=11
p6spy - 2016-08-23 18:42:44 -23   [p6spy] INFO    - 06:42:44,846|0|2|statement|UPDATE user SET name = ?,age=? WHERE id=?|UPDATE user SET name = 'donald',age=28 WHERE id=11

c3p0.properties文件配置：

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/test?useUnicode\=true&characterEncoding\=utf8&characterSetResults\=utf8
user=donald
password=123456
minPoolSize=10
maxPoolSize=100
maxIdleTime=1800
acquireIncrement=3
maxStatements=1000
initialPoolSize=10
idleConnectionTestPeriod=60
acquireRetryAttempts=30
breakAfterAcquireFailure=true
testConnectionOnCheckout=false

spy.properties主要配置：

module.log=com.p6spy.engine.logging.P6LogFactory
realdriver=com.mysql.jdbc.Driver
deregisterdrivers=true
dateformat=hh:mm:ss,SSS
appender=com.p6spy.engine.logging.appender.Log4jLogger
#appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger
# name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log) (used for file logger only)
#logfile     = spy.log
# append to the p6spy log file. if this is set to false the
# log file is truncated every time. (file logger only)
append=true
#The following are for log4j logging only
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %d{yyyy-MM-dd HH\:mm\:ss} -%-4r [%c] %-5p %x - %m%n
log4j.appender.ROLLFILE=org.apache.log4j.DailyRollingFileAppender
log4j.appender.ROLLFILE.File=F:/spy-logs/
log4j.appender.ROLLFILE.DatePattern=yyyy-MM-dd'.log'
log4j.appender.ROLLFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.ROLLFILE.layout.ConversionPattern=p6spy - %d{yyyy-MM-dd HH\:mm\:ss} %5p %c{1}\:%L \: %m%n
#log4j.appender.CHAINSAW_CLIENT=org.apache.log4j.net.SocketAppender
#log4j.appender.CHAINSAW_CLIENT.RemoteHost=localhost
#log4j.appender.CHAINSAW_CLIENT.Port=4445
#log4j.appender.CHAINSAW_CLIENT.LocationInfo=true
log4j.logger.p6spy=INFO,STDOUT,ROLLFILE

log4j.properties配置：

log4j.rootLogger=INFO,CONSOLE,FILE
log4j.addivity.org.apache=false
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.Threshold=DEBUG
log4j.appender.CONSOLE.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} -%-4r [%c] %-5p %x - %m%n
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.Encoding=gbk
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout

log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender
log4j.appender.FILE.File=F:/logs/
log4j.appender.FILE.DatePattern=yyyy-MM-dd'.log'
log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.ConversionPattern=[FH_sys] %d{yyyy-MM-dd HH\:mm\:ss} %5p %c{1}\:%L \: %m%n
问题结局：
1.driver com.mysql.jdbc.Driver is a real driver in spy.properties,
but it has been loaded before p6spy. p6spy will not wrap these connections.
Either prevent the driver from loading, or try setting 'deregisterdrivers' to true in spy.properties
方法：
#防止realdriver在p6spy驱动之前加载
deregisterdrivers=true
2.Connections could not be acquired from the underlying database
这个问题由于数据源配置问题
方法：
1，驱动配置有误：driver=com.mysql.jdbc.Driver
2，数据库连接地址有误：url=jdbc:mysql://localhost:3306/test?3useUnicode=true&characterEncoding=utf8
3，密码或帐号有误：username=donald password=123456
4，数据库未启动或无权访问
5，项目未引入对应的驱动jar包mysql-connector-java-5.1.8.jar
6,mysql root没有远程访问的权限，需要增加权限，增加权限的步骤如下：
进入mysql数据库：
grant all privileges on *.* to 'donald'@'%' identified by '123456';
flush privileges;
如果上面没有解决，看看c3p0.properties属性配置是不是有错
属性后面不要有空格