P6SPY Configuration...

Common Property File Settings

먼저 spy.properties 의 간단한 샘플을 첨부하겠다.

설정파일 열기

#################################################################
# P6Spy Options File #
# See documentation for detailed instructions #
#################################################################

#################################################################
# MODULES #
# #
# Modules provide the P6Spy functionality. If a module, such #
# as module_log is commented out, that functionality will not #
# be available. If it is not commented out (if it is active), #
# the functionality will be active. #
# #
# Values set in Modules cannot be reloaded using the #
# reloadproperties variable. Once they are loaded, they remain #
# in memory until the application is restarted. #
# #
#################################################################

module.log=com.p6spy.engine.logging.P6LogFactory
#module.outage=com.p6spy.engine.outage.P6OutageFactory

#################################################################
# REALDRIVER(s) #
# #
# In your application server configuration file you replace the #
# "real driver" name with com.p6spy.engine.P6SpyDriver. This is #
# where you put the name of your real driver P6Spy can find and #
# register your real driver to do the database work. #
# #
# If your application uses several drivers specify them in #
# realdriver2, realdriver3. See the documentation for more #
# details. #
# #
# Values set in REALDRIVER(s) cannot be reloaded using the #
# reloadproperties variable. Once they are loaded, they remain #
# in memory until the application is restarted. #
# #
#################################################################

# oracle driver
# realdriver=oracle.jdbc.driver.OracleDriver

# mysql Connector/J driver
realdriver=com.mysql.jdbc.Driver

# informix driver
# realdriver=com.informix.jdbc.IfxDriver

# ibm db2 driver
# realdriver=COM.ibm.db2.jdbc.net.DB2Driver

# the mysql open source driver
# realdriver=org.gjt.mm.mysql.Driver

#specifies another driver to use
realdriver2=
#specifies a third driver to use
realdriver3=

#the DriverManager class sequentially tries every driver that is
#registered to find the right driver. In some instances, it's possible to
#load up the realdriver before the p6spy driver, in which case your connections
#will not get wrapped as the realdriver will "steal" the connection before
#p6spy sees it. Set the following property to "true" to cause p6spy to
#explicitily deregister the realdrivers
deregisterdrivers=false

################################################################
# P6LOG SPECIFIC PROPERTIES #
################################################################
# no properties currently available

################################################################
# EXECUTION THRESHOLD PROPERTIES #
################################################################
# This feature applies to the standard logging of P6Spy. #
# While the standard logging logs out every statement #
# regardless of its execution time, this feature puts a time #
# condition on that logging. Only statements that have taken #
# longer than the time specified (in milliseconds) will be #
# logged. This way it is possible to see only statements that #
# have exceeded some high water mark. #
# This time is reloadable. #
#
# executionthreshold=integer time (milliseconds)
#
executionthreshold=

################################################################
# P6OUTAGE SPECIFIC PROPERTIES #
################################################################
# Outage Detection
#
# This feature detects long-running statements that may be indicative of
# a database outage problem. If this feature is turned on, it will log any
# statement that surpasses the configurable time boundary during its execution.
# When this feature is enabled, no other statements are logged except the long
# running statements. The interval property is the boundary time set in seconds.
# For example, if this is set to 2, then any statement requiring at least 2
# seconds will be logged. Note that the same statement will continue to be logged
# for as long as it executes. So if the interval is set to 2, and the query takes
# 11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals).
#
# outagedetection=true|false
# outagedetectioninterval=integer time (seconds)
#
outagedetection=false
outagedetectioninterval=

################################################################
# COMMON PROPERTIES #
################################################################

# comma separated list of tables to include
include =
# comma separated list of tables to exclude
exclude =

# sql expression to evaluate if using regex
sqlexpression =

# filter what is logged
filter=false

# turn on tracing
trace = true
autoflush = true

# sets the date format using Java's SimpleDateFormat routine
dateformat=

#list of categories to explicitly include
includecategories=

#list of categories to exclude: error, info, batch, debug, statement,
#commit, rollback and result are valid values
#excludecategories=
excludecategories=info,debug,result,batch

#allows you to use a regex engine or your own matching engine to determine
#which statements to log
#
#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher
#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher
stringmatcher=

# prints a stack trace for every statement logged
stacktrace=false
# if stacktrace=true, specifies the stack trace to print
stacktraceclass=

# determines if property file should be reloaded
reloadproperties=false
# determines how often should be reloaded in seconds
reloadpropertiesinterval=60

#if=true then url must be prefixed with p6spy:
useprefix=false

#specifies the appender to use for logging
#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 - %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

#################################################################
# DataSource replacement #
# #
# Replace the real DataSource class in your application server #
# configuration with the name com.p6spy.engine.spy.P6DataSource,#
# then add the JNDI name and class name of the real #
# DataSource here #
# #
# Values set in this item cannot be reloaded using the #
# reloadproperties variable. Once it is loaded, it remains #
# in memory until the application is restarted. #
# #
#################################################################
realdatasource=/RealMySqlDS
realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

#################################################################
# DataSource properties #
# #
# If you are using the DataSource support to intercept calls #
# to a DataSource that requires properties for proper setup, #
# define those properties here. Use name value pairs, separate #
# the name and value with a semicolon, and separate the #
# pairs with commas. #
# #
# The example shown here is for mysql #
# #
#################################################################
realdatasourceproperties=port;3306,serverName;myhost,databaseName;jbossdb,foo;bar

#################################################################
# JNDI DataSource lookup #
# #
# If you are using the DataSource support outside of an app #
# server, you will probably need to define the JNDI Context #
# environment. #
# #
# If the P6Spy code will be executing inside an app server then #
# do not use these properties, and the DataSource lookup will #
# use the naming context defined by the app server. #
# #
# The two standard elements of the naming environment are #
# jndicontextfactory and jndicontextproviderurl. If you need #
# additional elements, use the jndicontextcustom property. #
# You can define multiple properties in jndicontextcustom, #
# in name value pairs. Separate the name and value with a #
# semicolon, and separate the pairs with commas. #
# #
# The example shown here is for a standalone program running on #
# a machine that is also running JBoss, so the JDNI context #
# is configured for JBoss (3.0.4). #
# #
#################################################################
#jndicontextfactory=org.jnp.interfaces.NamingContextFactory
#jndicontextproviderurl=localhost:1099
#jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.nameing:org.jnp.interfaces

#jndicontextfactory=com.ibm.websphere.naming.WsnInitialContextFactory
#jndicontextproviderurl=iiop://localhost:900

module
module.xxx는 시스템 시작시 자동으로 로딩되는 특수한 모듈로서 매 모듈마다 여러가지 기능들을 수행하고 있다. 이러한 모듈의 주석문 체크를 해제하면 시스템 시작시 자동으로 메모리에 적재가 되여지며 그 생존주기는 시스템이 종료되기 전까지이다.
P6SPY에서는 아래와 같은 두가지 모듈을 지원하고 있다.

module.log=com.p6spy.engine.logging.P6LogSpyDriver
module.outage=com.p6spy.engine.outage.P6OutageSpyDriver

module.log는 로깅할때 꼭 필요한 모듈이다.
module.outage는 로깅정보를 출력할때 꼭 필요한 모듈이다.

realdriver
실제 p6spy는 WAS에서 들어오는 모든 database 요청들을 가로채고 결과를 log파일로 찍는다. 사실 이 과정에서 사용자는 코드에 전혀 손 댈 필요가 없으며 단순히 p6spy설정파일에 realdriver를 설정해주고 WAS에 wrapped 데이타베이스 driver만 설정해주면 끝난다.

filter, include, exclude
P6Spy는 사용자들로 하여금 특정된 테이블, 특정된 statement 타입들에 대하여 모니터링할수 있게 한다. 간단히 filter=true 라는 세팅만으로 log에 찍힐 모든 내용들을 필터링할수 있다.
Include는 콤마로 분할된 표현식 리스트를 받아 들이는데 실제 로그파일에는 include 리스트에 포함되여있는 문자열들만 log파일에 찍게 된다.

가령 모든 select문을 출력하되 테이블에대한 정열문을 제외하고 싶다면 아래와 같이 세팅하면 된다.

filter = true
# comma separated list of tables to include
include = select
# comma separated list of tables to exclude
exclude = orders

stringmatcher (Custom Filtering)
P6SPY를 이용하여 좀 더 융통적인 로깅정보를 획득하고 싶다면 include,exclude 대신 정규표현식을 사용할수 있다. P6SPY에서 지원하는 RegExp는 두가지가 있는데 하나는 GNU RegExp이고 다른 하나는 Jakarta RegExp이다.

# Use GNU Regex Matching for Filtering
stringmatcher =com.p6spy.engine.common.GnuRegexMatcher
# Use Apache Jakarta Regex Matching for Filtering
stringmatcher =com.p6spy.engine.common.JakartaRegexMatcher

appender

Appenders는 사용자로 하여금 출력될 로그가 어디에 저장될건지 또한 어떤식으로 저장될건지를 설정해주는 작용을 한다. 기존에 p6spy버젼은 3가지 방식의 appender을 지원하는데 그것들로는 log4j, stout, csv text파일 등이다.

CSV 파일 포맷으로 로깅을 하고 싶을때 아주 간단히 appender=com.p6spy.engine.logging.appender.FileLogger 앞의 코멘트 기호를 삭제해주면 된다.

#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

Using StdOut—Uncomment the StdoutLogger as follows and all output will be sent to stdout in a CSV format:
#appender=com.p6spy.engine.logging.appender.Log4jLogger
appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger

Using Log4J—To output to log4j, make sure log4j is in your path, uncomment the log4j appender, and specify the desired log4j settings:
appender=com.p6spy.engine.logging.appender.Log4jLogger
#appender=com.p6spy.engine.logging.appender.StdoutLogger
# appender=com.p6spy.engine.logging.appender.FileLogger

#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.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

If for some reason log4j cannot be initialized, the logging will go to a file called log4jaux.log.

p6spy-install.zip

sqlprofiler-0.3-bin.zip