Diagnosing Performance Problems

Prev	Chapter 6. Production Environments	Next

Did performance problems happen overnight, or did they sneak up on you? Any clue what caused the performance problems (e.g. loading 20K users into .LRN)
Is the filesystem out of space? Is the machine swapping to disk constantly?
Isolating and solving database problems.
- Without daily internal maintenance, most databases slowly degrade in performance. For PostGreSQL, see the section called “Vacuum Postgres nightly”. For Oracle, use exec dbms_stats.gather_schema_stats('SCHEMA_NAME') (Andrew Piskorski's Oracle notes).
- You can track the exact amount of time each database query on a page takes:
  1. Go to Main Site : Site-Wide Administration : Install Software
  2. Click on "Install New Application" in "Install from OpenACS Repository"
  3. Choose "ACS Developer Support">
  4. After install is complete, restart the server.
  5. Browse to Developer Support, which is automatically mounted at /ds. -
  6. Turn on Database statistics
  7. Browse directly to a slow page and click "Request Information" at the bottom of the page.
  8. This should return a list of database queries on the page, including the exact query (so it can be cut-paste into psql or oracle) and the time each query took.
    Figure 6.8. Query Analysis example
- Identify a runaway Oracle query: first, use ps aux or top to get the UNIX process ID of a runaway Oracle process.
  Log in to SQL*Plus as the admin:
```
[$OPENACS_SERVICE_NAME ~]$ svrmgrl
+              
```
- Turn on Database statistics
- Browse directly to a slow page and click "Request Information" at the bottom of the page.
- This should return a list of database queries on the page, including the exact query (so it can be cut-paste into psql or oracle) and the time each query took.
  Figure 6.8. Query Analysis example

Identify a runaway Oracle query: first, use ps aux or top to get the UNIX process ID of a runaway Oracle process.

[$OPENACS_SERVICE_NAME ~]$ svrmgrl
 
 Oracle Server Manager Release 3.1.7.0.0 - Production
 
@@ -59,7 +59,7 @@
     

       To be able to get a overview of how Oracle executes a particular query,
       install "autotrace". I usually follow the instructions here http://asktom.oracle.com/~tkyte/article1/autotrace.html.
-    
Make sure that the Oracle CBO works with adequate statistics

+    
Make sure that the Oracle CBO works with adequate statistics

     The Oracle Cost Based optimizer is a piece of software that tries to find
     the "optimal" execution plan for a given SQL statement. For that it
     estimates the costs of running a SQL query in a particular way (by default