Re: Oracle Parameters (was Re: Serious scalability issues with Oncourse at Indiana U)

> Hi Lance,
>
> We are using automatic shared memory management in Oracle. Base on  
> your settings and our ours, I think are the keys are as the follow.
> 1. Your shared_pool is too large.  SAKAI application codes does not  
> need such a large shared pool. 1 gig can be a good start point  
> (unless you have other applications in the same database).
> 2. You can set the db_cache_size much greater. We have a total SGA  
> of 6560M and 5872M is used in buffer cache (Oracle assigned it).
> 3. 255M of PGA is enough based on our settings.
> 4. If you can set the sga_max_size = 20480M (or even higher as your  
> from), try to use AMM and set the sga_target to at least 18gigs.
>
> The following are our parameter settings:
> sga_max_size =6560M
> sga_target=6560M
> pga_aggregate_target=256M
>
> The following are automatically generated by Oracle based our target:
> Shared Pool     624M
> Buffer Cache     5872M
> Large Pool     16M
> Java Pool     32M
> Other     16M
>
> Luke has created a site to put the parameter as a reference:
>
> http://confluence.sakaiproject.org/confluence/display/ENC/Oracle 
> +Admini
>
> All the parameters can be seen below.
>
> Thanks,
> Drew Zhu
> Oracle DBA
> ITCS, University of Michigan
>
> SAKAI.__db_cache_size=6190792704
>>> SAKAI.__java_pool_size=33554432
>>> SAKAI.__large_pool_size=16777216
>>> SAKAI.__shared_pool_size=620756992
>>> SAKAI.__streams_pool_size=0
>>> *.archive_lag_target=600
>>> *.audit_file_dest='/u01/app/oracle/admin/SAKAI/adump'
>>> *.background_dump_dest='/u01/app/oracle/admin/SAKAI/bdump'
>>> *.compatible='10.2.0.1.0'
>>> *.control_files='/u02/oradata/SAKAI/control01.ctl','/u02/oradata/
>>> SAKAI/contro
>>> l02.ctl','/u02/oradata/SAKAI/control03.ctl'
>>> *.core_dump_dest='/u01/app/oracle/admin/SAKAI/cdump'
>>> *.cursor_sharing='FORCE'
>>> *.db_block_size=16384
>>> *.db_create_online_log_dest_2=''
>>> *.db_domain=''
>>> *.db_file_multiblock_read_count=64
>>> *.db_flashback_retention_target=500
>>> *.db_name='SAKAI'
>>> *.db_recovery_file_dest='/u02/flash_recovery_area/SAKAI'
>>> *.db_recovery_file_dest_size=103079215104
>>> *.db_unique_name='SAKAI_primary'
>>> *.dg_broker_start=TRUE
>>> *.dispatchers='(PROTOCOL=TCP) (SERVICE=SAKAIXDB)'
>>> *.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105
>>> .235.199)(PORT=11587)))(CONNECT_DATA=
>>> (SERVICE_NAME=SAKAI_primary_XPT)(INSTANC
>>> E_NAME=SAKAI)(SERVER=dedicated)))'
>>> *.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105
>>> .235.96)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_XPT)
>>> (SERVER=dedic
>>> ated)))','(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
>>> (HOST=121.105.235
>>> .95)(PORT=11587)))(CONNECT_DATA=(SERVICE_NAME=SAKAI_DELPHI_XPT)
>>> (INSTANCE_NAME
>>> =SAKAI)(SERVER=dedicated)))'
>>> *.filesystemio_options='SETALL'
>>> *.job_queue_processes=100
>>> *.local_listener='SAKAI_primary'
>>> *.log_archive_config='dg_config=(SAKAI,SAKAI_standby1)'
>>> *.log_archive_dest_1='location="/u02/oradata/arch/
>>> SAKAI"','valid_for=(ONLINE_
>>> LOGFILE,ALL_ROLES)
>>> DB_UNIQUE_NAME=SAKAI_primary'
>>> *.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=
>>> (ADDRESS=(PROTOCOL=
>>> TCP)(HOST=standby1.os.umich.edu)(PORT=11587)))(CONNECT_DATA=
>>> (SERVICE_NAME=SAK
>>> AI_standby1_XPT)(INSTANCE_NAME=SAKAI)(SERVER=dedicated)))"','
>>> ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
>>> reopen=300
>>>  db_unique_name="SAKAI_standby1" register net_timeout=180
>>> valid_for=(online_logfile,primary_role)'
>>> *.log_archive_dest_3=''
>>> *.log_archive_dest_state_1='ENABLE'
>>> *.log_archive_dest_state_2='ENABLE'
>>> *.log_archive_dest_state_3='ENABLE'
>>> *.log_archive_format='%t_%s_%r.dbf','%t_%s_%r.arc'
>>> *.log_archive_max_processes=2
>>> *.log_archive_min_succeed_dest=1
>>> *.open_cursors=300
>>> *.pga_aggregate_target=268435456
>>> *.processes=1000
>>> *.remote_login_passwordfile='EXCLUSIVE'
>>> *.sga_max_size=6871947674
>>> *.sga_target=6871947674
>>> *.standby_archive_dest='/u01/app/oracle/admin/SAKAI/arch'
>>> *.standby_file_management='AUTO'
>>> *.undo_management='AUTO'
>>> *.undo_retention=18000
>>> *.undo_tablespace='UNDOTBS1'
>>> *.user_dump_dest='/u01/app/oracle/admin/SAKAI/udump'
>
>
>
>
> Lance Speelmon wrote:
>> Adi,
>>
>> Would you mind sharing your Oracle memory settings?  We are  
>> currently running with:
>> db_cache_size = 4096M (from 5120M)
>> shared_pool_size = 3072M (from 4096M)
>> java_pool_size = 250M (no change)
>> large_pool_size= 2048M (from 4096M)
>> sga_max_size = 20480M (from 24576M)
>>
>> Thanks, L
>>
>>
>> Lance Speelmon  +1 (317) 278-9053
>> Manager Online Development / Sakai Release Manager
>>
>>
>> On Sep 6, 2007, at 3:21 PM, R.P. Aditya wrote:
>>
>>> Do you have minIdle and maxIdle set? and does maxIdle =  
>>> maxActive? That will
>>> ensure you don't create new db connections and will help you  
>>> scale much
>>> better.
>>>
>>> We have 8 appservers and use:
>>>
>>>   minIdle@...=1
>>>   maxIdle@...=14
>>>   initialSize@...=15
>>>   maxActive@...=14
>>>
>>> with 400 requests per second peak, I'm don't see why you would
>>> need 2400 db pool connections -- maybe 400 * 2 for safety, but  
>>> you are just
>>> eating PGA unnecessarily with all those connections, and that  
>>> memory could be
>>> used for SGA instead (we reduced our PGA from 512m to 256m and  
>>> haven't seen
>>> problems).
>>>
>>> Adi
>>>
>>> On Thu, Sep 06, 2007 at 03:13:39PM -0400, Lance Speelmon wrote:
>>> > Adi,
>>> >
>>> > Let me see if I can outline the changes:
>>> >
>>> > 1) DBCP settings we have been running for 2+ years:
>>> > minSize=10
>>> > initialSize=10
>>> > maxSize=50
>>> >
>>> > 2) When we started seeing DBCP having problems establishing new
>>> > database connections, we switched to:
>>> > minSize=50
>>> > initialSize=50
>>> > maxSize=50
>>> > * These settings served us pretty well until we saw the 2x load
>>> > increase the first week of classes.
>>> >
>>> > 3) Once the load really hit we tried:
>>> > minSize=150
>>> > initialSize=150
>>> > maxSize=150
>>> > * We were still seeing errors with creating new database  
>>> connections
>>> > and DBCP deadlocks.
>>> >
>>> > 4) Our current settings after switching to c3p0:
>>> > minSize=150
>>> > initialSize=150
>>> > maxSize=150
>>> > * We still saw connection errors, but c3p0 was able to cope  
>>> without
>>> > any deadlocking.
>>> >
>>> > 5) Now that we think we have resolved our Oracle connection  
>>> issues,
>>> > we are considering moving to the following settings for c3p0:
>>> > minSize=10
>>> > initialSize=10
>>> > maxSize=150
>>> > * The change that we think resolved the Oracle connection  
>>> issues were
>>> > increasing number of dispatchers, and disabling automatic memory
>>> > management.
>>> >
>>> > Thanks, L
>>> >
>>> >
>>> >
>>> >
>>> > Lance Speelmon  +1 (317) 278-9053
>>> > Manager Online Development / Sakai Release Manager
>>> >
>>> >
>>> > On Sep 6, 2007, at 10:13 AM, R.P. Aditya wrote:
>>> >
>>> > >On Wed, Sep 05, 2007 at 04:26:34PM -0400, Lance Speelmon wrote:
>>> > >> The first sign of trouble came from the fact that DBCP was  
>>> having
>>> > >> trouble obtaining and maintaining connections to Oracle.  
>>> DBCP has a
>>> > >> nasty bug that can be triggered in these kinds of conditions  
>>> that
>>> > >> results in a deadlock situation.  To resolve the DBCP bugs, we
>>> > >> switched to the c3p0 connection pool.  C3p0 behaves much  
>>> more stably
>>> > >> and predictably under heavy load and can recover better from
>>> > >> connection issues with Oracle.  This is a drop-in  
>>> replacement for
>>> > >> DBCP and I am going to recommend that Sakai switch to this
>>> > >connection
>>> > >> pool as a default in the 2.5 release.
>>> > >
>>> > >what were the dbcp parameters you were using per appserver and  
>>> what
>>> > >are they
>>> > >under c3p0?
>>> > >
>>> > >Any reason why did increasing the connection pool so drastically
>>> > >helped?
>>> > >
>>> > >Thanks,
>>> > >Adi
>>> > >
>>> >
>>> > [see attachment: "message0.html", size: 4702 bytes]
>>> >
>>> > [see attachment: "smime.p7s", size: 2417 bytes]
>>> >
>>> >
>>> > Attachments:
>>> >
>>> > message0.html
>>> > https://collab.sakaiproject.org/access/content/attachment/ 
>>> 66508027-ebfb-4f38-8041-bae67cfeeff3/message0.html
>>> >
>>> > smime.p7s
>>> > https://collab.sakaiproject.org/access/content/attachment/ 
>>> 6570e313-5eda-400e-80ae-1221d28f2725/smime.p7s
>>> >
>>> > ----------------------
>>> > This automatic notification message was sent by Sakai Collab
>>> > (https://collab.sakaiproject.org/portal) from the WG:  
>>> Production site.
>>> > You can modify how you receive notifications at My Workspace >  
>>> Preferences.
>>> >
>>>
>>
>> [see attachment: "message0.html", size: 7940 bytes]
>>
>> [see attachment: "smime.p7s", size: 2417 bytes]
>>
>>
>> Attachments:
>>
>> message0.html
>> https://collab.sakaiproject.org/access/content/attachment/ 
>> c6aeb8b6-9ddc-42d6-80e7-3010a11a4b3e/message0.html
>>
>> smime.p7s
>> https://collab.sakaiproject.org/access/content/attachment/ac63cb76- 
>> f528-4360-0038-1757b33db2ff/smime.p7s
>>
>> ----------------------
>> This automatic notification message was sent by Sakai Collab  
>> (https://collab.sakaiproject.org/portal) from the DG: Development  
>> (a.k.a. sakai-dev) site.
>> You can modify how you receive notifications at My Workspace >  
>> Preferences.
>>
>>
>>
>