Home
The Toolkit for Online Communities
17099 Community Members, 0 members online, 2301 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Q&A : ACS3/Oracle DATA Model Question

Forum OpenACS Q&A: ACS3/Oracle DATA Model Question

Icon of envelope Request notifications

Collapse
Posted by Miguel Albuquerque on
Hi all,

So here is the picture : we have an old ACS-3 instance running wild with Oracle 8.1.7 since 2000...

We recently upgraded the hardware but the gain in performance was somehow disapointing - from a PII 550MHz with 256Mb RAM, HHD IDE to 2 x Xeon 3,4GHz 2Mb cache, 4Gb RAM, SCSI 320 load balancing but Oracle was eating 99,9% of CPU full-time!

Any help will be much appreciated.

Collapse
Posted by Dirk Gomez on
Maybe a query that generates a cartesian product?
Collapse
Posted by Sebastiano Pilla on
Try narrowing down the problem... For example, try determining if there is a specific page, or query, that is slow. Alternatively, if everything is slow, you should post some configuration details of your Oracle installation and your ACS installation: at this point it's not even clear whether the problem is in the database or in AOLServer or in whatever else.
Collapse
Posted by Miguel Albuquerque on
yes you are right, i've realized that the logins and updates in the sec_browser_properties table are very expensive...maybe because of the size of that table, more than 10 million rows.

about the ACS installation :

ns_section ns/db/drivers

        ns_param ora8 ora8.so

ns_section ns/db/pool/main

        ns_param MaxIdle 1000000000
        ns_param MaxOpen 1000000000
        ns_param Driver ora8
        ns_param Connections 5
        ns_param DataSource ""
        ns_param User service_name
        ns_param Password ******
        ns_param Verbose On
        ns_param ExtendedTableInfo On

ns_section ns/db/pool/subquery

        ns_param MaxIdle 1000000000
        ns_param MaxOpen 1000000000
        ns_param Driver ora8
        ns_param Connections 5
        ns_param DataSource ""
        ns_param User service_name
        ns_param Password ******
        ns_param Verbose On
        ns_param ExtendedTableInfo On

ns_section ns/db/pool/log

        ns_param MaxIdle 1000000000
        ns_param MaxOpen 1000000000
        ns_param Driver ora8
        ns_param Connections 5
        ns_param DataSource ""
        ns_param User service_name
        ns_param Password *******
        ns_param Verbose On
        ns_param ExtendedTableInfo On

ns_section ns/db/pools

        ns_param main main
        ns_param subquery subquery
        ns_param log log

ns_section ns/parameters

        ns_param User nsadmin
        ns_param ServerLog /opt/aolserver/log/mim_prod-error.log
        ns_param Home /opt/aolserver
        ns_param StackSize 500000
        ns_param auxconfigdir /web/mim_prod/parameters
        ns_param MaxKeepAlive 0
        ns_param HackContentType    1
        ns_param DefaultCharset    utf-8
        ns_param HttpOpenCharset    utf-8
        ns_param OutputCharset      utf-8
        ns_param URLCharset        utf-8

ns_section ns/threads

        # use more than 1 processor (Solaris)
        ns_param SystemScope no

ns_section ns/server/mim_prod

        ns_param PageRoot /web/mim_prod/www
        ns_param DirectoryFile "index.tcl, index.adp, index.html, index.htm"
        ns_param Webmaster webmaster@domain.com
        ns_param NoticeBgColor "\"#ffffff\""
        ns_param EnableTclPages On
        ns_param NotFoundResponse /global/file-not-found.html
        ns_param ServerBusyResponse /global/busy.html
        ns_param ServerInternalErrorResponse /global/error.html
# MA - Tunninig :: was MaxThreads 100
        ns_param MaxThreads 150
# MA - Tunning :: was MaxBusyThreads 50
        ns_param MaxBusyThreads 100
        ns_param MaxWait 2

ns_section ns/server/mim_prod/db

        ns_param Pools main,subquery,log
        ns_param DefaultPool main

About Oracle this is the config file:

ORACLE_OWNER="oracle"

START_ORACLE_DB="yes"
#
# Enable clients on other computers to connect to the database
#
START_ORACLE_DB_LISTENER="no"
#
# Start Oracle Intelligent Agent (if installed)
#
START_ORACLE_DB_AGENT="no"
#
# Start the Apache that comes bundled with Oracle
#
START_ORACLE_DB_APACHE="no"
#
# Should Apache be started with SSL support
#
ORACLE_APACHE_USE_SSL="no"
#
# Start Connection Manager to  allow other computers to connect to
# the database across a NAT type network
#
START_ORACLE_DB_CMANAGER="no"
#
# Start Oracle Internet Filesystem service
#
START_ORACLE_DB_IFS="no"
#
# Start Oracle Internet Directory service
#
START_ORACLE_DB_OID="no"
#
# Start Oracle Cluster Filesystem (for RAC)
#
START_ORACLE_DB_OCFS="no"
#
# Start Oracle Cluster Manager (for RAC)
#
START_ORACLE_DB_OCM="no"
#
# Command line parameters for oracm
#
ORACLE_DB_OCM_PARAMETERS=""
#
# Start Oracle GSD (for RAC, admin and config. daemon)
#
START_ORACLE_DB_GSD="no"

#
# Set the KERNEL PARAMETERS for Oracle. Requires a 2.4 kernel, in 2.2
# kernels only SHMMAX can be set during runtime via /proc.
#
# DO NOT CHANGE ANY VALUES unless you KNOW what you are doing and why!!!
#
# Have a look at the Oracle ReleaseNotes for the Oracle product you are
# using for how to set these values. If you do not set them we will
# assume some reasonable defaults for a medium Oracle 9i database
# system (that's a pretty big and busy one!).
#
# The /proc filesystem provides access to kernel parameters and statistics
# and the /proc/sys/ system allows one to change some kernel settings
# during runtime.
# If you have the kernel sources installed (package kernel-source)
# You can find more information here:
#  /usr/src/linux/Documentation/sysctl/ (directory)
#  /usr/src/linux/Documentation/filesystems/proc.txt
#  /usr/src/linux/Documentation/networking/ip-sysctl.txt
#
SET_ORACLE_KERNEL_PARAMETERS="yes"
SHMMAX=3294967296
#
# SHMMNI (default: 4096): max. number of shared segments system wide
# No change is needed for running Oracle!
#
SHMMNI=4096
#
# SHMALL (default: 8G [2097152]): max. shm system wide (pages)
# No change is needed for running Oracle!
#
SHMALL=2097152

#
# Sempahore values
# Kernel sources header file: /usr/src/linux/include/linux/sem.h
#
# SEMVMX: semaphore maximum value. Oracle recommends a value of 32767,
# which is the default in SuSE *and* the maximum value possible.
# This value cannot be changed during runtime via the /proc interface,
# but there is no need to do so anyway!
#
# SEMMSL: max. number of semaphores per id. Set to 10 plus the largest
# PROCESSES parameter of any Oracle database on the system (see init.ora).
# Max. value possible is 8000.
#
SEMMSL=1250
#
# SEMMNS: max. number of semaphores system wide. Set to the sum of the
# PROCESSES parameter for each Oracle database, adding the largest one
# twice, then add an additional 10 for each database (see init.ora).
# Max. value possible is INT_MAX (largest INTEGER value on this
# architecture, on 32-bit systems: 2147483647).
#
SEMMNS=32000
#
# SEMOPM: max. number of operations per semop call. Oracle recommends
# a value of 100. Max. value possible is 1000.
#
SEMOPM=100
#
# SEMMNI: max. number of semaphore identifiers. Oracle recommends a
# a value of (at least) 100. Max. value possible is 32768 (defined
# in include/linux/ipc.h: IPCMNI)
#
SEMMNI=256

#
# Defines the local port range that is used by TCP and UDP to
# choose the local port. The first number is the first, the
# second the last local port number. Default value depends on
# amount of memory available on the system:
# > 128Mb 32768-61000
# < 128Mb 1024-4999 or even less.
# This number defines number of active connections, which this
# system can issue simultaneously to systems not supporting
# TCP extensions (timestamps). With tcp_tw_recycle enabled
# (i.e. by default) range 1024-4999 is enough to issue up to
# 2000 connections per second to systems supporting timestamps.
#
IP_LOCAL_PORT_RANGE="32768 65000"

#
# The *_SHELL settings are for the Oracle startup script (/etc/rc.d/oracle
# and 'rcoracle') *ONLY*, it does NOT have any influence on the
# limits if you login as user 'oracle' and start Oracle from there!!!
# This sets the limits for the number of open files and processes.
# FILE_MAX_SHELL *MUST* be lower than FILE_MAX_KERNEL, obviously
#
FILE_MAX_KERNEL=131072
FILE_MAX_SHELL=65536
PROCESSES_MAX_SHELL=16384
MAX_CORE_FILE_SIZE_SHELL=unlimited

#
# By Andrea Arcangeli, SuSE:
# This decreases the swappiness of the kernel. It will tend to swap less. It
# will shrink the pagecache more, before falling back into swap. So
# increasing the mapped ratio will result in less cache and less swap.
# On a lowmemory machine reducing the cache, and the swap can decrease
# performance.
# On a database machine with plenty of ram, swapping some hundred mbyte
# instead may not be necessary, better to shrink the cache, in particular
# because having that much shm allocated tends to fool the VM. The VM
# can't know if the shm is fs cache too (the shm in Oracle is mostly cache
# for the filesystem).
# So going to 1000 is probably a good idea for high end servers with
# plenty of memory. Using "1000" make sense where you really know swapping
# is going to be not necessary during all the important workloads because
# you tune the machine in a way that it has enough ram to succeed w/o the
# need of swap. Using 1000 tells the VM to swap less.
#
VM_MAPPED_RATIO=1000

#
# Max. size of an async I/O request
#
AIO_MAX_SIZE=262144

## Path:        Productivity/Databases/Tools/Oracle
## Type:        string("/opt/oracle","/oracle","")
## Default:    "/opt/oracle"
#
# ORACLE_BASE: The base directory where Oracle puts ALL files, the
# product(s) as well the JRE, the Oracle Universal Installer (OUI),
# documentation - everything. Exceptions: /etc/oraInst.loc which tells
# the OUI where its repository is located, /etc/oratab which lists
# ORACLE_HOMEs and ORACLE_SIDs, and the three shell scripts "dbhome"
# and "[c]oraenv" which can be put anywhere (recommendation:
# put them in /usr/local/bin/, the user is prompted for their
# desired location during Oracle product installation.
# If you install multiple products they (should/usually) share this
# common base directory. Leave empty if you do not want to set this
# variable at all.
#
ORACLE_BASE=/opt/oracle

## Path:        Productivity/Databases/Tools/Oracle/DB
## Type:        yesno
## Default:    no
#
# Should Apache be started with SSL support
#
ORACLE_DB_APACHE_USE_SSL="no"

## Path:        Productivity/Databases/Tools/Oracle/DB/RAC
## Description: Settings for the cluster database (RAC)

## Path:        Productivity/Databases/Tools/Oracle/DB/RAC
## Type:        yesno
## Default:    no
#
# Start Oracle Cluster Filesystem (for RAC)
#
START_ORACLE_RAC_OCFS="no"

## Path:        Productivity/Databases/Tools/Oracle/DB/RAC
## Type:        yesno
## Default:    no
#
# Start Oracle Cluster Manager (for RAC)
#
START_ORACLE_RAC_OCM="no"

## Path:        Productivity/Databases/Tools/Oracle/DB/RAC
## Type:        string
## Default:    ""
#
# Command line parameters for oracm
#
ORACLE_RAC_OCM_PARAMETERS=""

## Path:        Productivity/Databases/Tools/Oracle/DB/RAC
## Type:        yesno
## Default:    no
#
# Start Oracle GSD (for RAC, admin and config. daemon)
#
START_ORACLE_RAC_GSD="no"

## Path:        Productivity/Databases/Tools/Oracle/System/Memory
## Type:        integer
#
# Use big pages or not?
#
SHM_USE_BIGPAGES=0

## Path:        Productivity/Databases/Tools/Oracle/System/Network
## Type:        integer
## Default:    "262144"
#
# For internal cluster communication in RAC: Maximum receive window
#
RMEM_MAX=262144

## Path:        Productivity/Databases/Tools/Oracle/System/Network
## Type:        integer
## Default:    "262144"
#
# For internal cluster communication in RAC: Default receive window
#
RMEM_DEFAULT=262144

## Path:        Productivity/Databases/Tools/Oracle/System/Network
## Type:        integer
## Default:    "262144"
#
# For internal cluster communication in RAC: Maximum send window
#
WMEM_MAX=262144

## Path:        Productivity/Databases/Tools/Oracle/System/Network
## Type:        integer
## Default:    "262144"
#
# For internal cluster communication in RAC: Default send window
#
WMEM_DEFAULT=262144

Thanks again

Collapse
Posted by Dirk Gomez on
It should be safe to remove 99.9% of the data in that table. Is the scheduler for this proc not working?

Your config files look fine. Take a look at your log files and see if something weird happens. Any queries that take forever?
Have a close look at V$SESSION_LONGOPS.

Are you using ACS3 pre- or post-bind variables?

If pre-variables, then Oracle may just be killed by too much parsing - how busy is your site? - try cursor_sharing=force to reduce parsing activity.

Collapse
Posted by Miguel Albuquerque on
V$SESSION_LONGOPS looks ok although i'am not sure what am i looking for in that table...there is no unfinished queries, and the average time seems pretty good actually.

ACS3 pre or post-variables? How do I check that? Not sure i understand what you mean.

Our site has about 1500 - 2500 hits per day.

Collapse
Posted by Dirk Gomez on
What is the version of your Oracle driver? It should be 2.6.

How are SQL queries generated, like this:

select foo from bar where bar_id=$bar_id

or like this:

select foo from bar where bar_id=:bar_id

Additionally take a look at the SQL queries that were executed on your system. v$sql is the data dictonary view you want to look into.

Collapse
Posted by Don Baccus on
Once upon a time data was never deleted from that table ... that's probably the problem.

Miguel, I suggest you empty 99.9% of that table as Dirk suggested, and check your tcl library to see if there's any proc being scheduled to empty it periodically, and the security code tcl library file to see if it attempts to delete  rows from that table when doing session management.

I think we fixed this in our latest OpenACS 3.x versions (as opposed to not fixing until we took over ACS 4.x) so you might be able to use our sources as a resource, even though they're PostgreSQL only.

Collapse
Posted by Miguel Albuquerque on
Thanks Don, you are right, there is no schedule proc for that table...

So are you suggesting to use the code from an early ACS 4.x version in order to port it to Oracle, or can I still find the latest OACS3.x code from CVS or other ?

Anyway thank you all. Just one more question : how do i check the version of the Oracle driver?

This is all i could find about it:

[17/Jun/2004:11:19:01][23909.16384][-main-] Notice: modload: loading '/opt/aolserver/bin/ora8.so'
[17/Jun/2004:11:19:01][23909.16384][-main-] Notice: ora8 driver LobBufferSize = 16384
[17/Jun/2004:11:19:01][23909.16384][-main-] Notice: ora8 driver PrefetchRows = 0
[17/Jun/2004:11:19:01][23909.16384][-main-] Notice: ora8 driver PrefetchMemory = 0
[17/Jun/2004:11:19:02][23909.16384][-main-] Notice: dbdrv: opening database 'ora8:'

Cheers

Collapse
Posted by Miguel Albuquerque on
And by the way, i've deleted 99% of the table and damn what a difference! ;-)

Thnx !