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

> I am going to let our DBAs respond directly, but the last report I saw
> this afternoon showed we only have 304M free in shared_pool.  L
>
>
> Lance Speelmon  +1 (317) 278-9053
> Manager Online Development / Sakai Release Manager
>
>
> On Sep 6, 2007, at 4:34 PM, Drew Zhu wrote:
>
>> 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.
>>>
>>>
>>>
>>
>
> [see attachment: "smime.p7s", size: 2417 bytes]
>
>
> Attachments:
>
> smime.p7s
> https://collab.sakaiproject.org/access/content/attachment/e6b3f23d-20af-4791-0091-0d0d07d629f6/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.
>
>
>