Diagnosing Performance Problems

Prev	Chapter 6. Production Environments	Next

Did performance problems happen overnight, or did they sneak up on +Diagnosing Performance Problems
Prev Chapter 6. Production Environments Next
Diagnosing Performance Problems
- 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 file system 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:
  Go to Main Site : Site-Wide Administration : Install Software
  Click on "Install New Application" in "Install from OpenACS Repository"
  Choose "ACS Developer Support">
  After install is complete, restart the server.
  Browse to Developer Support, which is automatically mounted at /ds. -
  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.
  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.
  Log in to SQL*Plus as the admin:
```
[$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
```

Diagnosing Performance Problems

Diagnosing Performance Problems

Make sure, that the Oracle CBO works with adequate statistics

Make sure, that the Oracle CBO works with adequate statistics