WEBI_User SQL query process slows down report generation

book

Article ID: 5909

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

You don't have any WEBI (ad-hoc) reports running but there is following WEBI_User SQL query process in MySQL reporting database that causes MySQL performance issue. 

User: WEBI_User
Host: <hostname>:53959
db: srmdbapi
Command: Query
State: Copying to tmp table on disk
Info: SELECT DISTINCT ( v_dim_secure_interface_model_nofx.model_name ) FROM v_dim_secure_interface_mod

This SQL query is locking tables, so when you run reports from BI Launch Pad it just hangs, the progress bar is just circling around.

You can login to OneClick + SRM (Spectrum Report Manager) machine, launch a bash shell, login to MySQL (under $SPECROOT/mysql/bin directory run './mysql -uroot -proot' and run the following on MySQL prompt to see if this WEBI_User process exists.

show full processlist;

Cause

This issue is caused by continous SQL indexing process which is invoked by internal BOXI platform search application.

Environment

Spectrum 10.x, CABI 4.1 SP3

Resolution

We should configure BOXI platform search application so that it is run by schedule instead of running continuously.

Follow the steps below to resolve this issue:

1. Login to CABI machine and access Central Management Console and login as Administrator

2. Select "Servers" from the top left pull down and select "Servers List". Make sure all the Adaptive Processing Server servers (you may have multiple servers) are in "Running" and "Enabled"status.

3. Select "Applications" from the top left pull down and right-click on Platform Search Application item and select "Properties". The "Properties"window will appear.

4. Look the "Indexing Status" on top. If it is shown as running click on [Stop Indexing] button and wait until the status changes to "Stopped".

5. Under "Crawling Frequency" box, select "Scheduled crawling". Click [Save & Close] button at the bottom to save the configuration.

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKGzAAO" alt="PlatformSearchProperties.png" width="1133" height="364">

6. Select "Folders" from the top left pull down and select All Folders -> Platform Search Scheduling folder on the left. Right-click Platform Search Scheduling Object and select "Schedule". Select Schedule -> Recurrence and configure in such a way that it runs once or twice in a week. Select appropriate day(s) when CABI is least accessed by users. And then click [Schedule] button at the bottom to save the configuration.

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKH0AAO" alt="PlatformSearchSchedule.png" width="1262" height="336">

7. Again right-click Platform Search Scheduling Object and select "Platform Search Schedule Duration". Set the duration to 240 minutes and click the [Save & Close] button at the bottom.

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKH1AAO" alt="PlatformSearchScheduleDuration.png" width="1265" height="199">

 

 

Attachments

1558707885566000005909_sktwi1f5rjvs16qv2.png get_app
1558707883644000005909_sktwi1f5rjvs16qv1.png get_app
1558707881504000005909_sktwi1f5rjvs16qv0.png get_app