« Return to Thread: Serious scalability issues with Oncourse at Indiana U
Oracle Parameters (was Re: Serious scalability issues with Oncourse at Indiana U)
by Drew Zhu
::
Rate this Message:
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.
>
>
>
----------------------
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.
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.
>
>
>
----------------------
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.
« Return to Thread: Serious scalability issues with Oncourse at Indiana U
| Free embeddable forum powered by Nabble | Forum Help |
