For about 3-4 months now and intermittently our server was slow to respond to queries both CMC and BI Launchpad and we often had to stop SIA , clean tomcat cache and restart to bring it back to life. We are on Windows server 2016 , 64Gb RAM and BI 4.2 Sp5. Both CPU and RAM resources, disk and network were all fine. Eventually we engaged various different IT teams to assist us as Administrators of the environment to find the cause. And our DBA’s came up trumps and found it. If you have a lot of customers and we do, over 15k , 4k of which have been using BI and approx.150 different users per day each day. DO NOT use Events and User Notification Events , it will query a particular table which stores the event notification in a BLOB column for each user that exists in any groups you subscribe to the User Notification Event. We have 3 primary user groups, Writers, Guests and Consumers totally , 4800 plus users. each time a person logs onto BI Launchpad the table is queried fetching 4800 rows for each user notification that is active, we had 3, so any time a person logs on the table is queried fetching all those rows with a blob column type bringing our environment to a grinding halt. The table in question is CMS_RELATIONS7 and column is DATA which is a BLOB column type. We also found that when a user goes back to BI Launchpad homepage the table is queried again. We had at one point 32,800 row selects from this table. I have now deleted all User Notification Events and advised my colleagues not to use it, instead I have written my own message system in which message will appear on BI Launchpad logon page.
Macroman (BOB member since 2002-11-13)