Frequent CMS (DB2) locks causing slow response and search index freezes

[Former Member]

Hello Colleagues,

We have BI 4.0 SP5 installed and we are using DB2 as our CMS database. We encounter frequent CMS locks and this is causing platform search index to fail. So we stopped indexing, and we are trying to understand why this behavior occurs.

We have already referred to the following, but it does not seem pertinent for our situation

1640918 - Getting frequent deadlocks in CMS DB pointing to MS
SQL server in BI 4.0

1787321 - database Deadlock warnings in event viewer due to

indexing of search service.

All APS settings are fine tuned to reflect best practices. DSL Bridge @ 6GB,  Webi APS @ 6 GB, 4 WebI processing servers on each node.

The question now is how do I prevent database locks in the future.  We are unable to run platform search indexing as it causes database locks

Can you please advise us as to how we can fix this problem ?

[former_member202789]

Schedule Crawling at non-load time or schedule it at off hours.

Reduce the level of Indexing.

You may also try and install SP6 on you test server and check whether SP6 helps improving the performance.

Regards,

Animesh Kumar

[former_member190781]

Hi Narender,

Have to tried suggestions mentioned in KBA 1640934

Regards,

Sohel

1640934

[Former Member]

Note: 1640934. Already tried but to no avail.

[former_member190781]

Do you know which service or process is responsible for the deadlock, your DBA can help you to find it out. Is it really PlatformSearchService or something else.

How often you see the deadlocks?

Regards,

Sohel

[former_member190781]

BI 4.0 is installed on which OS? is it a clustered environment, please describe the architecture of your environment, do you see any complaints in Event Viewer or /var/log/messages or in CMS logs?

Regards,

Sohel

[Former Member]

We have a clustered environment with three nodes with 32 GB each, the OS is Windows Server 2008 R2 SP1. Platform search is on a dedicated node.There are five WebI processing servers on Box 1 & Box 2. The individual services under each APS is configured as per best practices. The APS splits are as follows:

Box 1

DSL Bridge  - 6 GB

LCM  - 1 GB

MDAS -3 GB

Publishing -2 GB

WebI - 8 GB

Other - 1 GB

Custom 1 GB

Box 2

DSL Bridge  - 6 GB

LCM  - 1 GB

MDAS -3 GB

Publishing -2 GB

WebI - 8 GB

Other - 1 GB

Custom 1 GB

Box 3

Platform Search  - 6 GB

Data Services & Information Steward 4 GB.

Here is the lock details from the database, we are unable to tie it down to a specific PID. Any inputs are much appreciated.

[former_member190781]

Hi Narender,

I am unable to review the attached image, could you re-upload it.

Regards,

Sohel

[Former Member]

Here are the details from the image.

PARENTID    CHILDID     ISMEMBER    ORDINAL     RELATIONSHIPID    VERSION

1892917            3356              1         null                 2932                         0

I am unable to locate a corresponding PID for the CMS lock. No processes under 3356, 2932 or 1892917

[former_member190781]

Your DBA could help you to findout the process ID and the SQL which caused the deadlock.

Regards,

Sohel

[former_member182521]

Hello Narender,

The value which you are displaying are no where related to processIds instead they are available with in your CMS database table named CMS_RELATIONS.

CMS_RELATIONS6 for BO 3.x

CMS_RELATIONS7 for BO 4.x

Your are facing issues with this object whose SI_ID is 3356 and its parent id is 1892917. You may required to use Query builder to analyse bit more on these two repository objects.

Regards,

Mani