Maximize J2EE and database interoperability for performance

The right APM tool can improve the interoperability between your application's J2EE and database tiers

Things are not going well at Continental Flange. The corporate Website's performance has degraded to the point where frustrated customers are searching for other suppliers. The IT operations staff investigates and determines that a problem with the J2EE application is a major contributor to the poor response time. This puts immediate pressure on the J2EE development team to identify and resolve the problem.

After some initial investigation, the J2EE development team determines that the problem isn't in the J2EE tier, but can be traced to the database. The database team responds with arguments that the problem really is, after all, in the J2EE tier. Blamesmanship escalates, team spirit evaporates, confusion prevails, and customers and revenue continue to disappear.

This common scenario highlights a significant need: to support better interoperability between the J2EE and database tiers, IT departments need to be able to act more quickly and decisively.

The basic challenge: Naming the culprit

J2EE developers are often forced to participate in the blame game when lagging response times drive Website users away. The applications programmers develop on the middle tier must interoperate with the database, and when a performance bottleneck appears, it may show up as a problem on the J2EE tier when the database is the underlying cause. The real problem is interoperability. How do you optimally tune integration between those two tiers to maximize your application's performance? More to the point, how can you view the bottlenecks, identify the real troublemakers, and deal with them as quickly as possible?

Many APM (application performance management) tools are available to help identify and resolve performance bottlenecks. Two common approaches to finding bottlenecks in J2EE applications are:

  1. You use a dashboard with colored alerts to monitor the status of your systems. Green means all is well; yellow or red means you have a performance problem to deal with. The dashboard can also report on response times from the different components of your application.
  2. Instead of waiting for performance to deteriorate enough to trigger a dashboard alert, you take a proactive approach and try to identify excessive response times or excessive resource usage. You can do that by examining the top service requests (in terms of response time) and drill down to see what components they invoke.

Let's say you're a bank. One of your checking account customers has gone to your Website to get an account summary covering the last seven days. That customer clicks the Get Account Summary link.

Get Account Summary starts by invoking a specific URL through the Web browser. Underneath, of course, it invokes many components that interact to provide the right output. In your search for bottlenecks, you start from the top invocations (in many cases, that would be a servlet's doGet() or doPost() method) and traverse the invocation tree to look at all the components invoked to service Get Account Summary, and then the components that they invoked, and so on, down to the lowest level, which, in many cases, would be the SQL call to the database using JDBC (Java Database Connectivity).

You need to know which of these components is taking too long, but stepping through them like this is a time consuming and tiresome process, especially if you're not familiar with their individual roles. You must look at each component and ask yourself, does this take too much time? Are 10 seconds really necessary for producing an output in response to Get Account Summary? You're not even sure, because to know that, you'd really have to know the details of what's going on underneath each method or program component. The only person who really knows may be the developer of that specific component. If you suspect that the problem is with response time from the database, you may need to contact the database team to further investigate the problem.

Isolating SQL statements

Let's suppose it's taking too long to retrieve account information. Every user who asks for an account summary waits 15 seconds for a response. Is the problem on the database side? Is it in the application code? Is it in the network? It may simply be that the user's Internet connection is very slow.

In this particular case, however, you suspect that a data retrieval issue is to blame. One approach to finding the cause is to ask the APM tool to display all the SQL statements issued by the application, sorted by response time, so you can see if any SQL statement is taking too long for the wrong reason (some SQL statements simply take a long time—such as a request for a list of all the transactions performed by the account for an entire year).

You now look at the top SQL statement on the list. It goes to the database and its response time contribution is one second. This is not really bad; if this is the worst-performing SQL statement, the problem may not be with the SQL statements at all. So the next questions are: Is this something that originates in the application tier? Who is invoking this statement? How many times? If an application invokes a SQL statement many times more than you expect, you may reasonably suspect that the application is at fault.

The APM tool comes to your rescue. You simply click on the SQL statement and a link takes you to its entire invocation tree, beginning with the request from the customer for an account summary and ending with the SQL statement that went to the database. Now you have two more important pieces of information: you know for certain you're looking at the specific request for the service request you were investigating, and you can see each component's individual contribution to response time.

You now look at the description of the component that invoked the SQL statement, and you see that its response time is 9 seconds. It's clearly a major contributor to the 15-second wait time frustrating your customers. The list displayed by the APM tool also shows that this component invoked the SQL statement 7 times. This tells you that most of the 9 seconds consumed by this component were spent invoking the SQL statement. Not only is the component performing too many calculations, it's also invoking the SQL statement many times. There could be a good reason for this, but it's beyond the ability of any performance management tool to tell you so. The important thing is that you have pinpointed the program component that must be investigated. Good APM tools can also provide clear suggestions for dealing with the problem.

Is it really a database problem?

Let's look at our example from another viewpoint. Let's say that instead of invoking the SQL statement too many times, the component in question only invoked it once, but that invocation consumed most of the 15 seconds. Now the question is, why is this single statement taking so long to execute? The problem is not in the code, so it probably lies on the database side.

What you need now is a performance management tool that enables you to move your investigation of a specific service request—Get Account Summary—to the database tier. Going back to your list of SQL statements, you click on a drill-down link associated with the SQL statement you're interested in, which takes you from the J2EE side to the database side. You're now looking at your SQL statement in the context of an Oracle database or another database product. The tool may help you pinpoint the problem on the database side and may also offer expert tuning advice that database administrators (DBAs) can use to improve database performance. It may be that the disk on which the table space is located has a bad response time, and a move to another disk is indicated. It may be that an index is missing, and you can speed data retrieval by creating a new index. Perhaps there are too many threads running in parallel on the database, and you need to do some partitioning to alleviate concurrency problems.

There are other possibilities. Data retrieval may be taking too long because there's a long wait to get a database connection. The code is fine, and the database is running well, but the wait time may be telling you that the database connection pool is simply not large enough to handle peak or even normal traffic. You can query the application server to learn how many connections have been defined, compare that number with the typical number of concurrent requests, and quickly determine whether more connections are needed.

Leveraging interoperability

Your performance management tool should do more than just identify contributors to poor response time on the J2EE tier. It should allow you to look at the interaction between the J2EE tier and the adjacent database tier and provide the means for analyzing performance problems on both tiers. The use of an integrated APM product by J2EE developers and DBAs is necessary for an efficient handling of performance problems. It also allows the J2EE and database groups to "speak a common language." Most APM solutions target a single tier within the architecture and offer diagnostics of that tier alone. Using this approach, time is wasted, blame-storming results, and, more often than not, problems are not solved. Today's complex architectures and sophisticated technologies mean that the interoperation of one tier with another often results in performance problems that are impossible to find with this approach. Leading APM products are available today that allow you to drill down in context from the J2EE tier into the database tier and ensure the timely resolution of performance issues, regardless of what tier they originated from.

Conclusion

Improved interoperability between the J2EE and database tiers offers significant benefits: faster end-user response time, increased customer loyalty, improved morale, and even a better bottom line. Superior tools available today bridge the gap between the J2EE tier and the database tier, automate the search for bottlenecks, pinpoint the causes, and provide expert advice for solving the problems. Every J2EE development team should seriously consider the value these integrated APM tools can add to their output.

Robert Maness is vice president of product management and marketing at Veritas Software.

Learn more about this topic

Related: