P6Spy + SQL Profiler + Spring

P6Spy

1.下载p6spy-install.zip

2.将p6spy.jar放到应用的lib目录下,将spy.properties放到WEB-INF\classes目录下

3.修改spy.properties样本的第42行，改为对应驱动，（#部分可以不管）
realdriver=net.sourceforge.jtds.jdbc.Driver
修改spy.properties样本的第54行，封闭缺省的MYSQL驱动：
# the mysql open source driver
# realdriver=org.gjt.mm.mysql.Driver
修改spy.properties样本的第168行，将spy.log放到比较好找的位置：
logfile = E:/spy.log

4.驱动程序加载先后的问题解决
　　如果spy.log里出现
　　你的程序的数据库驱动名称 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
　　请把spy.properties文件里的deregisterdrivers=false改为deregisterdrivers=true，重新运行即可。

5.在spring配置文件中配置如下:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- <property name="driverClass"><value>net.sourceforge.jtds.jdbc.Driver</value></property> -->			
	<property name="driverClass"><value>com.p6spy.engine.spy.P6SpyDriver</value></property>
	<property name="jdbcUrl"><value>${read.jdbcUrl}</value></property>
	<property name="user"><value>${read.user}</value></property>
	<property name="password"><value>${read.password}</value></property>
	<property name="minPoolSize"><value>10</value></property>
	<property name="maxPoolSize"><value>30</value></property>
	<property name="initialPoolSize"><value>10</value></property>
	<property name="maxIdleTime"><value>28000</value></property>
	<property name="idleConnectionTestPeriod"><value>28000</value></property>
</bean>   

6看日志是否有记录，完成

 

SQL Profiler

1下载sqlprofiler-0.3-bin.zip
2解压，将
spy.properties下面内容复制到先前的spy.properties替换相应地配置：
--------------------------
appender=com.p6spy.engine.logging.appender.Log4jLogger
#appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger
 
# 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 - %m%n
 
log4j.appender.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender
log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost
log4j.appender.SQLPROFILER_CLIENT.Port=4445
log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true
 
#log4j.logger.p6spy=INFO,STDOUT
log4j.logger.p6spy=DEBUG, SQLPROFILER_CLIENT
这段配置的意思是使用log4j来记录日志。
因此需要用到log4j.jar
 

3安装
1. Start the GUI
2. Start the webapp, in starts doing some JDBC requests we will ignore
3. Press the "reset" button on the GUI
4. Make a request to the webapp
5. Press the "pause" button after the request has finished executing
6. Press the "report" button to save profiling results as a CSV file
* 我们先用java -jar sqlprofiler.jar 运行 sql profiler
* 然后启动web server

4应用：
   1）可以生成SQL执行情况报表：点击“pause”按钮，即可产生profiler result；
   2）很cool的是，点击最左边的按钮，可以根据整个运行情况生成数据库索引的创建脚本，我们可以根据这些脚本生成自己的数据库索引，数据库tuning的工作是不是变得越来越简单or傻瓜了呢？呵呵。
   3）其他的包括图形分析报表，自己挖掘吧；
   4）如果用loadRunner、web stress tool等压力测试工具run一把，那么这个报表和索引创建建议就更加逼真和有效了。