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

Collapse
4: Re: ACS3/Oracle DATA Model Question (response to 3)
Posted by Miguel Albuquerque on 06/16/04 12:37 PM
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 mailto: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
5: Re: ACS3/Oracle DATA Model Question (response to 4)
Posted by Dirk Gomez on 06/16/04 12:53 PM
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
6: Re: ACS3/Oracle DATA Model Question (response to 5)
Posted by Miguel Albuquerque on 06/16/04 03:45 PM
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
7: Re: ACS3/Oracle DATA Model Question (response to 6)
Posted by Dirk Gomez on 06/16/04 04:14 PM
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.