Index: openacs-4/packages/acs-core-docs/www/xml/install-guide/maintenance-performance.xml =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/xml/install-guide/Attic/maintenance-performance.xml,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-core-docs/www/xml/install-guide/maintenance-performance.xml 23 Jan 2004 15:10:31 -0000 1.1 @@ -0,0 +1,104 @@ + + +%myvars; +]> + + 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 . For Oracle, "gather schema stats" (more info?). + + + 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. +
+ 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: + [service0 ~]$ svrmgrl + +Oracle Server Manager Release 3.1.7.0.0 - Production + +Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. + +Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production +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 + ,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 + ,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 Piskorski's Oracle notes) + + + + + ($Id: maintenance-performance.xml,v 1.1 2004/01/23 15:10:31 joela Exp $) + +