Index: openacs-4/packages/acs-core-docs/www/maint-performance.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/maint-performance.adp,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-core-docs/www/maint-performance.adp 8 Nov 2017 09:42:11 -0000 1.3 +++ openacs-4/packages/acs-core-docs/www/maint-performance.adp 25 Apr 2018 08:38:28 -0000 1.4 @@ -3,11 +3,11 @@ Diagnosing Performance Problems -
+ leftLink="uptime" leftLabel="Prev" + title="Chapter 6. Production +Environments" + rightLink="database-management" rightLabel="Next"> +

Diagnosing Performance Problems

  • Did performance problems happen overnight, or did they sneak up @@ -16,9 +16,8 @@ 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 +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 @@ -32,15 +31,14 @@

      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

      Query Analysis example
      +

      Figure 6.8. Query Analysis +example

      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
+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
 
@@ -50,39 +48,29 @@
 With the Partitioning option
 JServer Release 8.1.7.3.0 - Production
 
-SVRMGR> connect internal              
-Password:
-
    See all of the running queries, and match the UNIX PID:
    -select p.spid  -- The UNIX PID
+SVRMGR> connect internal             
+Password:
    See all of the running queries, and match the UNIX PID:
    select p.spid  -- The UNIX PID
        ,s.sid  ,s.serial#
        ,p.username  as os_user
        ,s.username  ,s.status
        ,p.terminal  ,p.program
   from v$session s  ,v$process p
  where p.addr = s.paddr
- order by s.username ,p.spid ,s.sid ,s.serial# ;
-
    See the SQL behind the oracle processes:
    -select s.username
+ order by s.username ,p.spid ,s.sid ,s.serial# ;
    See the SQL behind the oracle processes:
    select s.username
        ,s.sid  ,s.serial#
        ,sql.sql_text
   from v$session s, v$sqltext sql
  where sql.address    = s.sql_address
    and sql.hash_value = s.sql_hash_value
  --and upper(s.username) like 'USERNAME%'
- order by s.username ,s.sid ,s.serial# ,sql.piece ;
-
    To kill a troubled process:
    -alter system kill session 'SID,SERIAL#';  --substitute values for SID and SERIAL#
-
    (See Andrew
+ order by s.username ,s.sid ,s.serial# ,sql.piece ;

    To kill a troubled process:

    +alter system kill session 'SID,SERIAL#';  --substitute values for SID and SERIAL#

    (See Andrew Piskorski's Oracle notes)

  • Identify a runaway Postgres query. First, logging must be enabled in the database. This imposes a performance penalty and should not be done in normal operation.

    Edit the file postgresql.conf - its location depends on -the PostGreSQL installation - and change

    -#stats_command_string = false
-

    to

    -stats_command_string = true
-

    Next, connect to postgres (psql +the PostGreSQL installation - and change

    #stats_command_string = false

    to

    stats_command_string = true

    Next, connect to postgres (psql service0 ) and select * from @@ -101,8 +89,7 @@ 64344418 | openacs.org | 14311 | 101 | nsadmin | <IDLE> 64344418 | openacs.org | 14549 | 101 | nsadmin | <IDLE> (8 rows) -openacs.org=> - +openacs.org=>

@@ -124,7 +111,7 @@ 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 +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 @@ -136,9 +123,9 @@

- \ No newline at end of file + leftLink="uptime" leftLabel="Prev" leftTitle="External uptime validation" + rightLink="database-management" rightLabel="Next" rightTitle="Chapter 7. Database +Management" + homeLink="index" homeLabel="Home" + upLink="maintenance-web" upLabel="Up"> + \ No newline at end of file