Monitoring and profiling database operations-Introduction to P6spy, SQL Profiler, irontrack SQL usage

In our Java development application process, will inevitably encounter the system performance problems, especially in the enterprise application development process, will deal with the database. When we encounter database performance, the most effective is to directly track the execution of each SQL statement, the optimization of SQL statements, index optimization is often the most straightforward to achieve the most direct effect.

Now, let's start by introducing P6spy, a profiling tool, to see how it can monitor and analyze database operations without intrusion.

P6spy

P6spy is an open-source framework that can be used to intercept and modify data manipulation statements in an application. By p6spy we can intercept SQL statements, which is equivalent to a logger for SQL statements, so that we can use it for related analysis, such as performance analysis. P6spy uses log4j to record the journaling information for the JDBC call.

Since the release of P6spy release 1.3 on November 30, 2003, it has been downloaded more than 19,000 times. The P6spy supports most application servers such as WebSphere, WebLogic, JBoss, Resin, and Tomcat.

Use steps

First of all, your application should be database-based, then you need to get P6spy related files (you can find the download link in the reference resource, you can download the package directly). The following is a detailed procedure for installing and using P6spy:

1. Download the P6spy file package, or download its source file package to study;
2. Put the P6spy jar package P6spy.jar in CLASSPATH , if the Web application is placed in the YourWebApp/WEB-INF/lib/ directory;
3. Put the spy.properties CLASSPATH in the directory, if the WEB application is placed in the YourWebApp/WEB-INF/classess/ directory, note is not the lib/ directory
4. Modify the driver name for the database driver name P6spy in your application system com.p6spy.engine.spy.P6SpyDriver all other use defaults, temporarily without modification;
5. Open the configuration file Spy.properties file, locate realdriver it, and change its value to the real database driver name of your application system;
6. Running your application or WEB application, you can see in spy.log the SQL detailed execution and operation Records information that P6spy monitors, including full SQL execution parameters.

P6log and P6outage

In the P6spy release package, it contains the P6log and p6outage two modules:

1. The
2. p6log

   P6log is the JDBC statement used to intercept and record the task application. This feature is especially useful for developers to monitor the execution of SQL statements on the EJB server, allowing developers to complete the code as efficiently as possible. At the same time P6spy deployment is extremely simple, and there is no need to change any line of code, that is, the existing application is non-invasive.

   12	realdriver= (your driver)        (your actual JDBC driver name executionthreshold= integer time      (in milliseconds)

4. P6outage

   P6outage is specifically designed to detect and record long-running SQL statements, p6outage only the information that exceeds the time in the configured conditions, and minimizes the operational efficiency that could affect the database.

   12	#outagedetection=true|false    (是否记录较长时间运行的语句)#outagedetectioninterval=整数时间 (以秒为单位)

Architecture Principles

Simply put, we can assume that P6spy is an agent, it only makes a layer of the JDBC driver interception, and then forwarded out, such a design and the actual application does not have any coupling, in addition to the configuration to change the driver to P6spy interception Drive, There is no need to make any changes anywhere else in the program. This layer of interceptors has no effect on other aspects of the program, except that it may bring a slight performance degradation to the system. And the performance degradation relative to this point, in the development environment for the developer is not felt, compared to its benefits, it is completely negligible.

Figure 1. P6spy the process of intercepting and monitoring the database

Problems and Solutions

If the following message is found in the Spy.log file after your application is started, it is the driver loading problem.

1234	<你的程序的数据库驱动名称> 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

Please change the spy.properties config file deregisterdrivers=false deregisterdrivers=true to run again.

This is because some applications will load the real database driver before P6spy, causing P6spy to not be able to monitor, set deregisterdrivers to true , is explicitly the real database of the driver anti-registration off, and the use of P6spy driver.

Configuration parameters and related meanings

The following table lists the names of the configuration items in the Spy.properties configuration file, their default values, their meanings, and related considerations:

Configuration Item Name	Default Value
Module.log	Com.p6spy.engine.logging. p6logfactory
Module.outage	Com.p6spy.engine.outage. p6outagefactory
Realdriver
Realdriver2
Realdriver3
Deregisterdrivers	False
Executionthreshold
Outagedetection	False
Outagedetectioninterval
Filter	False
Include
Exclude
SqlExpression
AutoFlush	True
DateFormat
Includecategories
Excludecategories
Stringmatcher
StackTrace	False
Stacktraceclass
Reloadproperties	False
Reloadpropertiesinterval	60
Useprefix	False
Appender	Com.p6spy.engine.logging. Appender. FileLogger
LogFile	Spy.log
Append	True
Log4j.appender.STDOUT	Org.apache.log4j.ConsoleAppender
Log4j.appender.STDOUT.layout	Org.apache.log4j.PatternLayout
Log4j.appender.STDOUT. Layout. Conversionpattern	P6spy-%m%n
Log4j.logger.p6spy	Info,stdout
Realdatasource
Realdatasourceclass
Realdatasourceproperties
Jndicontextfactory
Jndicontextproviderurl
Jndicontextcustom

SQL Profiler

SQL Profiler is a p6spy engine-based quick profiling tool, provided by jahia.org, to count SQL query statements to understand where performance bottlenecks are, where to create indexes, or to take appropriate approaches to improve efficiency, and can be based on SQL Querying the statement can help you generate the appropriate index script.

This gadget can display database queries in real time, create statistical analyses on most tables and columns, and generate index scripts with an integrated SQL parser. Of course, other information is collected and displayed, such as the time of a single database request, the time of a class of requests, and the time of all requests. Therefore, you can effectively detect the performance of your data by sorting the views. This tool is useful for a large number of requests that need to be analyzed, rather than manually analyzing them one by one. When you need to know for example access to the same tables and columns but with different query values, this grouping query can be analyzed using the SQL parser built on ANTLR.

Use steps

First of all, your application system should also be database-based, then you need to get SQL Profiler related files (in the reference resources can find the download link, you can download the package directly). Detailed procedures for installing and using SQL Profiler are described below:

1. Download the file package for SQL Profiler to install;
2. Put P6spy.jar and Sqlprofiler.jar CLASSPATH in, if the Web application is placed in the YourWebApp/WEB-INF/lib/ directory;
3. Put the spy.properties CLASSPATH in the directory, if the WEB application is placed in the YourWebApp/WEB-INF/classess/ directory, attention is not the lib/ directory;
4. Modify the driver name of the database driver name P6spy in your application system com.p6spy.engine.spy.P6SpyDriver all other use default value, temporarily do not modify;
5. Open the Spy.properties file and realdriver change the value to the database driver name of your program;
6. Note to java -jar sqlprofiler.jar start the SQL Profiler first, and to successfully see the boot interface;
7. Then start your application or server, and start the normal system request processing operations;
8. This allows you to see the results and analyze them on the graphical interface of SQL Profiler.

Analysis results

After a period of time the system is running, click the Pause button to stop interception, you can get analysis results such as:

Figure 2. Analysis Results Profiler view for SQL Profiler

Next, you can switch to the loggers view, which is the information for the Lgger view:

Figure 3. Analysis results for SQL Profiler Logger view

Of course, you can also switch to the analysis view, which is the analytical result information for the analyses view:

Figure 4. Analytic view of SQL Profiler analysis results

After analysis, we can directly export the recommended index script that should be optimized by SQLProfiler directly through the Save button submitted by the file, we can see the detailed SQL script to create the index by looking at the index script, so we can make the database tuning easily.

Problems and Solutions

The last thing to note is that you need to start SQLProfiler before you start the application server, or Tomcat. This is because SQLProfiler uses the log4j socketappender by default, so it starts first. Otherwise, errors can occur because of a WEB app in your application or application server (SQLProfiler equivalent to a socket server) because of a server that is not connected to the socket, which is available through SQL Profiler Control interface at the bottom of the connection status to know if there is a program to connect up.

Figure 5. SQL Profiler is in a non-connected state

Figure 6. SQL Profiler is in a connected state

Irontrack SQL

Ironeye, an open source project focused on JDBC performance monitoring and testing, contains three tools: Ironeye Sql,ironeye cache,irontrack SQL. Ironeye SQL is used to monitor the query overhead between Java applications and database servers, diagnose problems with performance, and allow developers to spot problems before testing. Ironeye was launched on October 1, 2003 based on the Apache software License.

Ironeye SQL This lightweight Java tool provides all of the SQL statistics flowing between the database and the application and is presented in multiple charts to quickly optimize the performance of the program.

Irongrid the concept of continuous performance with respect to continuous integration, which is to pay attention to the performance problem in the project development process, rather than the traditional solution of the problem.

Irongrid continuous performance in the operation of the application on the database is represented by Irontrack SQL. Irontrack SQL can intercept the application's request to the database through the wrapper of JDBC to complete performance monitoring. The advantage of Irontrack SQL is that you don't need to modify any code or install any programs on the database side, just replace the dependent JDBC on the test.

Use steps

First of all, your application system should also be database-based, then you need to get Irontrack SQL related files (in the reference resources can find the download link, you can download the package directly). The following is a detailed procedure for installing and using Irontrack SQL:

1. Download the Irontrack SQL file package for installation;
2. Put Irontracksql.jar, P6spy.jar and Log4j-1.2.8.jar in CLASSPATH , if the Web application is placed in the YourWebApp/WEB-INF/lib/ directory;
3. Put the spy.properties CLASSPATH in the directory, if the WEB application is placed in the YourWebApp/WEB-INF/classess/ directory, attention is not the lib/ directory;
4. Modify the name of your program's database driver name to P6spy com.p6spy.engine.spy.P6SpyDriver no other changes;
5. Open the configuration file Spy.properties file, locate realdriver it, and change its value to the real database driver name of your application system;
6. Set the listening port number monitorport=2000 ;
7. Run first java -jar irontracksql.jar to start Irontrack SQL;
8. Start your application or server again;
9. You can see the results and analyze them on the Irontrack SQL graphical interface.

Connection settings

Click the "Config" button to set the hostname, port and Refresh time (in milliseconds). Depending on the difference between your server and port, the following is the example of local and 2000 ports, with a refresh time of 500 milliseconds. After setting up, OK, click "Connect" can connect the application system and monitoring and analysis, when to stop the analysis, just click "Disconnect" button can immediately stop the analysis.

In the process of analysis, we can click on the "Purge" button as needed, it can clear the current monitored content, and then re-record monitoring information, easy to start again.

Figure 7. Irontrack SQL Connection Example

Analysis results

After a period of time the system runs, we can directly get the results of the analysis and the corresponding graphic analysis examples. The relevant information is displayed as follows:

Figure 8. Irontrack SQL Analysis Results

• The Count column shows the number of calls to the SQL statement;
• The AVG Time column displays the average duration of the SQL statement execution;
• The max Time column displays the highest amount of times the SQL statement took;
• The SQL column shows the content of the SQL statement that was actually executed.

You can also set the filter criteria to display the results of the specified condition, such as: only the results of the average number of calls greater than 100 times are concerned. Click on the "Filtering" to the left of the small triangle icon, you can display the following filter settings section:

Figure 9. Irontrack SQL Settings-related filtering conditions

Once the settings are complete, click on the "Apply Filter" button to get the relevant results you need. This makes it easier to focus on the content analysis you need, making it easier and faster to locate where the problem is and then resolve it.

Summarize

By using P6spy, SQL Profiler, irontrack SQL tools, we can effectively monitor and analyze the database operation without intrusion into existing application system, and provide a convenient method to find the performance bottleneck of the system and find the performance tuning of the system.

Monitoring and profiling database operations-Introduction to P6spy, SQL Profiler, irontrack SQL usage