1 / 28

Troubleshooting database performance issues with Documentum Content Server

Troubleshooting database performance issues with Documentum Content Server. Vipul Kapadia Subject Matter Expert (SME) Worldwide Technical Support EMC – Information Intelligence Group. Troubleshooting database performance issues with Documentum Content Server. Dial-in numbers:

rebekah-mullins
Download Presentation

Troubleshooting database performance issues with Documentum Content Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Troubleshooting database performance issues with Documentum Content Server Vipul Kapadia Subject Matter Expert (SME) Worldwide Technical Support EMC – Information Intelligence Group

  2. Troubleshooting database performance issues with Documentum Content Server • Dial-in numbers: • U.S. Dial-in numbers(toll free) 888-643-3084 (toll): 857-207-4204 • Passcode: 34856332 • Country-specific dial-in numbers: http://www.emcconferencing.com/globalaccess/index.asp?bid=302 • Click the handouts icon to download the presentation. • Separate Q&A session after the webinar • The webinar is being recorded • Follow us at: http://community.emc.com/blogs/iigsupportwebinars

  3. Agenda • RDBMS • RDBMS Bottlenecks • Oracle Best Practices • Microsoft SQL Server Best Practices • Microsoft SQL Server Missing Indexes • Troubleshooting SR #1 • Troubleshooting SR #2 • Troubleshooting SR #3

  4. RDBMS • Why RDBMS is important? • Are Performance issues related to database?

  5. RDBMS Bottlenecks • Common bottlenecks include: • CPU • Memory for caches and query execution • Disk I/O • Concurrency and locking • MOST bottlenecks are due to poorly executing queries, but sometimes you need a little more…

  6. Bottlenecks • CPU • Logical I/O • Hard parse • Sorts or Filters • Memory • Cache Hit Ratio • Physical I/O • Recompilation and Query Plan generation (high CPU cost) • Small Caches

  7. Oracle Best Practices • Parameters • optimizer_mode = ALL_ROWS (Oracle Default) • optimizer_index_cost_adj = 100 (Oracle Default) • optimizer_index_caching = 0 (Oracle Default) • cursor_sharing = FORCE • 10g • sga_target = Set as large as possible, up to a max of 65% of available physical memory or use formula • Total Physical Memory * 80% * 80% • pga_aggregate_target = Set as large as possible, up to a max of 15% of available physical memory or use formula • Total Physical memory * 80% * 20%

  8. Oracle Best Practices Cont. • 11g • MEMORY_TARGET • Set to total amount of memory you want to allocate to Oracle, up to 80% of the available memory • Additional Parameters for Documentum • processes = 2 * total number of concurrent sessions in server.ini for all repositories • If you have 1 Repository with 2 Content Server each has concurrent session set to 100 • Total max session can be 100+100 = 200 • Processes needs to be set at 2*200 = 400 • sessions = processes * 1.1 + 5 • In the above example to support 200 concurrent Documentum session we need to set • session = 400*1.1 + 5 = 445 • * This assumes maximum load condition and concurrent sessions please verify active Documentum sessions at peak load • Disclosure: These recommendations are based on internal testing, customer environment can vary, please actively involve your DBA before implementing the same.

  9. Microsoft SQL Server Best Practices • Recommended Server Settings for SQL Server Server Properties -> Advanced • Max Degree of Parallelism = 1

  10. Microsoft SQL Server Best Practices • Recommended Database Settings Database Properties -> Options • Parameterization = Forced • Auto Create Statistics = False • Auto Update Statistics = True(*) • Auto Update Statistics Asynchronously = True(*) (*) Set to False and manually update statistics if excessive recompilation is observed on the server

  11. Microsoft SQL Server Best Practices • READ_COMMITTED_SNAPSHOT setting for SQL Server databases • When using existing database when setting up repository or doing upgrade please make sure the database value "READ_COMMITTED_SNAPSHOT" is set to be "ON" • Use below SQL to validate SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'DM_<repository_name>_docbase’ GO Verify the value "is_read_commited_snapshot_on=1" • If not set; shutdown Documentum repository make sure all user session are disconnected and use below SQL to set it on ALTER DATABASE DM_<repository_name>_docbase SET READ_COMMITTED_SNAPSHOT ON

  12. Microsoft SQL Server Missing Indexes • Please use below SQL to get the recommendation from SQL server based on load and usage as to which index creation might help SELECT det.database_id, det.object_id, DB_NAME(det.database_id) as [database_name], OBJECT_NAME(det.object_id, database_id) AS [object_name], grp.user_seeks, grp.user_scans, det.equality_columns, det.inequality_columns, det.included_columns, det.statement FROM sys.dm_db_missing_index_detailsdet INNER JOIN sys.dm_db_missing_index_groups link ON det.index_handle = link.index_handle LEFT OUTER JOIN sys.dm_db_missing_index_group_stats AS grp ON link.index_group_handle = grp.group_handle WHERE det.database_id >= 5 ORDER BY [database_name], [object_name] GO

  13. How to create indexes • Indexes can be created directly in the RDBMS, or can be created from within Documentum • Indexes created in the RDBMS will not be “monitored” by Documentum • Indexes created on repeating valued attributes will affect query translation in different ways if they are created in the RDBMS vs. in Documentum • SQL • Oracle: • create index myindex on dmr_content_r(i_parked_state,r_object_id)tablespace DCTM_INDEX; • MS-SQL: • create index myindex on dmr_content_r(i_parked_state,r_object_id) • go • Documentum API: • apply,c,NULL,MAKE_INDEX,TYPE_NAME,S,dmr_content,ATTRIBUTE,S,i_parked_state,USE_ID_COL,B,T,ID_IN_FRONT,B,F • Documentum DQL: • EXECUTE make_index WITH type_name= 'dmr_content', attribute= 'i_parked_state',use_id_col=true

  14. How to create indexes cont. • DQL Query • select r_object_id, object_name from dm_document • where any keywords='repeating1' • No dmi_index on keywords: • select all dm_document.r_object_id, dm_document.object_name • from dm_document_spdm_document where ( • exists (select r_object_id from dm_sysobject_r where dm_document.r_object_id = r_object_id and keywords='repeating1')) • and (dm_document.i_has_folder = 1 and dm_document.i_is_deleted = 0) • With dmi_index on keywords: • select all dm_document.r_object_id, dm_document.object_name • from dm_document_spdm_document where (dm_document.r_object_id • in (select r_object_id from dm_sysobject_r where keywords='repeating1')) • and (dm_document.i_has_folder = 1 and dm_document.i_is_deleted = 0)

  15. Troubleshooting SR #1 Description of the issue: • Customer running into Oracle error with max processes ORA-20 - Maximum Processes Exceeded Steps taken: • Worked with DBA and verified the MAX resource utilization use following SQL as DBA • SQL>select * from v$resource_limit where resource_name in ('sessions', 'processes'); • Verified concurrent Documentum sessions in use in the server.ini and limit the same to customer requirement as • Server.ini under [SERVER_STARTUP] section • # This controls the number of concurrent users that can • # be connected to the server at any given time. • concurrent_sessions = 100

  16. Troubleshooting SR #1 Cont. Solution: • Calculated max concurrent session and set the limit in server.ini based on usage • Set the oracle initialization parameters with respect to same as : • processes = 2 * total number of concurrent_session in server.ini • sessions = processes * 1.1 + 5

  17. Troubleshooting SR #2 Description of the issue: • Customer with SQL Server back end started seeing poor performance with folder navigation and folder display over time Steps taken: • Captured the dfc trace from client application for poor performance see SN : http://solutions.emc.com/emcsolutionview.asp?id=esg91393 • {Application}/WEB-INF/classes/dfc.properties file

  18. Troubleshooting SR #2 • Reviewed query timings and responses in the trace and located long running DQL query • Using DA/IDQL captured SQL for the issue DQL • Option 1: Use DA DQL Editor • – enable “Show the SQL” checkbox • – Text area has a size limitation so the query may be cut off

  19. Troubleshooting SR #2 Cont • Option 2: Using IAPI on content server • – Login using IAPI and use same user ID to reproduce issue ( Avoid using install owner user to reproduce issue if issue is related to end user performance) • – Turn on SQL trace for user session by: • API> trace,c,1,,SQL_TRACE • ... • OK • Please note the trace turn on flag is 1 (One) and extra comma is needed • – Execute issue query as ex: • API> ?,c, select 1,upper(object_name),r_object_id,object_name,r_object_type …..

  20. Troubleshooting SR #2 Cont • – SQL trace will be captured under the user session log which can be found on content server under folder: $DOCUMENTUM/dba/log/<DOCBASE_ID>/<USER_NAME> • – Turn off SQL trace for user session by: • API> trace,c,0,,SQL_TRACE • ... • OK • Please note the trace turn off flag is 0 (Zero) and extra comma is needed • – Obtain the issue SQL and worked with DBA to analyze SQL performance directly at database layer using SQL Studio

  21. Troubleshooting SR #2 Cont • – Executed SQL and noted execution time, found the issue SQL is using multiple indexes however when looking at index properties and fragmentation details found indexes are fragmented

  22. Troubleshooting SR #2 Cont • Solution: • – Used SQL Management studio and selected Index Rebuild option from the pop-up menu for each of the indexes – Re-executed the SQL query in question and observed significant improvement – Reviewed Documentum Job “dm_UpdateStats” using DA and found the Job has been running every week – Looking at Job properties on Method tab the flag -dbreindex was set to READ

  23. Troubleshooting SR #2 Cont • Solution Cont: • – Reviewed the Job report and found recommendation in the report on changing flag to -dbreindex FIX and re-run to rebuild indexes on key tables • UpdateStats Report For DocBase <ABC> As Of 3/13/2012 20:31:12 • -dbreindex READ. The tables listed below are fragmented. • Change to -dbreindex FIX and re-run if you want to reindex these tables… • – Changed the Job Properties and updated Method tab to enable • -dbreindex READ to -dbreindex FIX – Re-ran the “dm_UpdateStats” job and reviewed/validated the job report – Validated system performance and issue was resolved.

  24. Troubleshooting SR #3 Description of the issue: • Poor Taskspace performance with opening Inbox items/Tasklist when processes have many process variables and use more than one SDTs Steps taken: • Captured the dfc trace from client application for poor performance see SN : http://solutions.emc.com/emcsolutionview.asp?id=esg91393 • Reviewed trace and found query as below repeating multiple times over and over • SELECT r_object_id, sd_element_name, sd_element_type FROM dm_process (ALL) where any sd_element_name=‘CUSTOMER_SDT' • This issue is due to SDT/attribute not found in cache forcing caching framework to query over and over • This new caching framework was introduced in 6.6

  25. Troubleshooting SR #3 Cont. Solution: • Increase default configuration of the number of cache element in memory when using many process variables/many SDT • Locate the file bpm-cache-ext.jar on the deployed Taskspace application on application server under {taskspace}/WEB-INF/lib/bpm-cache-ext.jar • Extract the Jar file and edit config/ehcacheExt.xml file • Change the following value from 100 to 1000 From : • <cache name="BPMPVNameToPVInfoCache" maxElementsInMemory="100“ eternal="false" overflowToDisk="false" timeToIdleSeconds="3600" timeToLiveSeconds="3600" memoryStoreEvictionPolicy="LRU"> • </cache> • To : • <cache name="BPMPVNameToPVInfoCache" maxElementsInMemory="1000" eternal="false" overflowToDisk="false" timeToIdleSeconds="3600" timeToLiveSeconds="3600" memoryStoreEvictionPolicy="LRU"> • </cache>

  26. Troubleshooting SR #3 Cont. Solution Cont: • Rebuild the Jar file bpm-cache-ext.jar with edited xml file • Deploy the new jar file after taking backup of existing jar under {taskspace}/WEB-INF/lib/bpm-cache-ext.jar • Redeploy the new application and restart application server • Try to reproduce the issue and review DFC trace, the repeating query should not reappear.

  27. Resources Posted to Powerlink Support Solutions: Enabling Tracing via dfc.properties http://solutions.emc.com/emcsolutionview.asp?id=esg91393 TaskSpace may face performance problems querying tasklists showing S.DT Data http://solutions.emc.com/emcsolutionview.asp?id=esg121408 Performance issue with Tasklist http://solutions.emc.com/emcsolutionview.asp?id=esg127988 SQL Server 2005 equivalent for CURSOR_SHARING in ORACLE DB http://solutions.emc.com/emcsolutionview.asp?id=esg90971 Performance is very slow when user belong to many groups http://solutions.emc.com/emcsolutionview.asp?id=esg123903 History tab is very slow http://solutions.emc.com/emcsolutionview.asp?id=esg117754

  28. Question And Answers Vipul Kapadia Subject Matter Expert (SME)

More Related