To locate performance problems caused by database access, you need to understand the queries issued against the database. You can instruct Dynamo to log all database activity by setting the loggingSQLInfo or loggingSQLDebug properties to true on each of your data sources. Usually there is only one data source: /atg/dynamo/service/jdbc/JTDataSource, by default. However, if you’ve created others, you’ll need to turn loggingSQLInfo on for those too. The database activity is logged to <ATG2006.5dir>/home/logs/info.log or <ATG2006.5dir>/home/logs/debug.log.
To generate test information in your logs:
Create a test script using the Recording Servlet or other methods. The script should simulate typical user behaviors, making sure to exercise those components that relate to database access. See Using the Recording Servlet in the Monitoring Site Performance chapter for information about how to use the Recording Servlet to create a test script.
Turn on
loggingSQLInfofor each of your database Connection Pools. If you want to see the parameter values in your SQL queries, as well as the SQL strings, also setloggingSQLDebugtotruefor each of your database Connection Pools.Run the test script using URLHammer, with one request per thread. See Using URLHammer in the Performance Diagnostics chapter for information about how to use the URLHammer utility.
After you have generated a log of SQL activity, the next step is to read through the queries. For best results, read the log messages from stdout, because there, the info and debug messages will be interleaved. If you read the debug log, the messages will be hard to interpret without the associated info log messages. If you are familiar with SQL, this will be easier, but there are some checks you can make without fully understanding each query. Focus on the queries that hit large tables and any queries that have several tables in their WHERE clause. These queries must not perform table scans. See Avoiding Table Scans.
You can also take an empirical approach to measuring database performance. Instead of analyzing each query, run each of them and time how long they take to execute. This is probably best done by culling the SQL out of the Dynamo logs and writing scripts to run the SQL. Examine the performance of these scripts and identify any queries that seem to take a long time to run. If you find some queries take much longer than others to run, you can focus your efforts on those queries.
