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.
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;
This issue is caused by continous SQL indexing process which is invoked by internal BOXI platform search application.
Spectrum 10.x, CABI 4.1 SP3
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">