Finding and Solving Problems

Finding and Solving Problems Hibernate affords a basic SQL monitoring capability, but for real development you are best advised to use a tool with a bit more sophistication. By definition, every interaction between your application and the database is translated through a JDBC driver. A pass-through driver is used to analyze the data. The pass-through driver does not change the data, but records all of the interaction for analysis. In this section, we will look at the pass-through JDBC driver p6spy and the use of IronTrack SQL to understand the data it generates. IronTrack SQL IronTrack SQL is an open-source Apache-licensed tool that works in conjunction with the p6spy driver monitor. Using p6spy (included with IronTrack SQL), every interaction between the application and the database is logged. IronTrack SQL, in turn, allows you to view these generated logs (either at runtime via TCP/IP or by opening generated log files). Configuring IronTrack SQL IronTrack SQL can be downloaded free from http://www.irongrid.com/. You will obtain a file with a name such as irontracksql-installer-1_0_172.jar. Once you have saved this file to your system, you can install it with the command java -jar irontracksql-installer-1_0_ 172.jar. The installer will launch, presenting a language screen, as shown in Figure 10.1. Figure 10.1. Starting IronTrack SQL Installation You can accept the defaults throughout the installation, although you may wish to specify a shorter, alternative destination path for the installation, as shown in Figure 10.2, because you will be placing libraries present in the installation in your application path. Figure 10.2. Alternative Installation Directory [View full size image] If you are using an application server, the precise installation process for IronTrack SQL varies (see http://www.irongrid.com/documentation/). To use IronTrack with a standalone application, you will need to place the following files on your class path: ironeyesql.jar p6spy.jar Next, you will need to update your Hibernate.properties to point to the p6spy driver (or whatever mechanism you are using to specify JDBC connectivity). You will observe that the line with the default driver has been commented out with a # character, not deleted. The log files generated by p6spy can become quite large (especially with full logging and stack trace tracking turned on). Therefore, you'll want to keep your standard driver class close at hand for when you wish to switch to production use. Listing 10.1 shows the Hibernate properties that should be set to make use of p6spy. Listing 10.1. Configuring p6spy Properties #hibernate.connection.driver_class=com.mysql.jdbc.Driver hibernate.connection.driver_class=com.p6spy.engine.spy. P6SpyDriver hibernate.connection.url=jdbc:mysql://localhost/hibernate hibernate.connection.username=root hibernate.connection.password= hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect hibernate.show_sql=false Finally, you will need to place a spy.properties file in your class path (typically next to your hibernate.properties). This file is used to configure the logging produced by p6spy. You should start by copying the spy.properties file included with the IronTrack SQL distribution. The most important thing is to set the spy.properties to use the correct driver, as in realdriver=com.mysql.jdbc.Driver. After changing these configuration options, simply run your application as you normally would. The default p6spy options will log every SQL statement to a log file (spy.log) in the application root directory. WHERE WAS THAT SQL GENERATED? p6spy will generate a stack trace pointing to the class that generated a SQL statement if you set stacktrace=true in the spy.properties file. This will slow your application down, because generating a stack trace is expensive, but it can be very helpful if you are working with a large, unfamiliar application and are having trouble tracking down a particular statement. Using IronTrack SQL If you are running your application in a long-lived environment (for example, in the context of an application server), you can use the IronTrack SQL graphical user interface to view your data at runtime via TCP/IP. Alternatively, you can simply load the generated spy.log file. This would be appropriate if your application runs and then terminates (as do several of the examples in this book) or, to cite another example, if you are unable to connect to the server via TCP/IP (perhaps due to a firewall installed on the server). You may have a shortcut already created that can launch IronTrack SQL. If not, you can launch IronTrack SQL from the command line with the command java jar irontracksql.jar. Once you've launched the IronTrack SQL interface, you can either connect to a running application via TCP/IP or you can import a generated log file. Figure 10.3 shows IronTrack SQL launched, with the Import… command selected. Figure 10.3. IronTrack SQL Import [View full size image] To view the generated log files, you'll need to change the Files of Type option to spy.log files, as shown in Figure 10.4. Figure 10.4. Selecting a spy.log File IronTrack allows you to sort and filter the loaded SQL statements. For example, Figure 10.5 shows the results of a run of the sample application shown in Chapter 3. As can be seen, the ALTER TABLE statements are relatively expensive, but so are our INSERT statements. Figure 10.5. Viewing SQL Statements [View full size image] Clicking the Graphing tab on the IronTrack SQL main interface allows us to see a graph of the generated SQL statements. As shown in Figure 10.6, the load on the server can be viewed at different points in time (useful for identifying certain operations that may be highly performance intensive). Figure 10.6. IronTrack SQL Import [View full size image]