Doug,
Thank you for the additional information. I don't recall if we had the same issues with older versions, or if the addition of the parent fields add another layer of complexity in the searches, which is why we were seeing performance problems in our 9.1 implementation. Just to give everyone an idea of the numbers we are using. We have close to 300 companies configured in our system. We also have implemented row level access based on login id for some things. Like Doug mentioned we spent a lot of time figuring out data access and row level security for this particular organization. However, there are a lot of challenges and potential trade offs. Performance is one of them and we are constantly looking at how to improve performance. Prior to applying the settings that Shawn sent, our a search to CTM:People, which has roughly 130000 records, would come back in 70 seconds for a regular user. After the settings were done the same search came back in less than 3 seconds, so you can see a massive improvement. Brian ________________________________ From: Action Request System discussion list(ARSList) <arslist@ARSLIST.ORG> on behalf of Mueller, Doug <doug_muel...@bmc.com> Sent: Tuesday, November 22, 2016 12:50 PM To: arslist@ARSLIST.ORG Subject: Re: Performance Issues - Multitenancy ** Just some background to help here…. Implementing an effective row level security model is actually quite an involved operation. It is about security so it has to be foolproof with no holes and no exceptions to data access for people without access. It has to include many complex considerations like not allowing searching on data you don’t have access to – not just limiting the return of that data… For example, if you did not have access to a salary field but you could search for people with salary > 10,000 and < 15,000, you could find out salary even though you could not see the field. So, there is some quite involved logic and SQL to protect all this. It is involved as there is no DB level support for this level of security. OK, so this is all prelude to the discussion here. We had situations where customers had users in large numbers of groups. That means we need to check permission for all those groups when checking for row level security. That generated enormous SQL commands and that could confuse the optimizers of the databases and trigger table scans. So, customers in this situation encountered significant slowness like Brian was seeing in his environment. We found alternate ways to do the query that were much more efficient in the database for large lists of users. Our testing showed that it was equivalent performance for few groups but much better (order of magnitude in many cases) for large group lists. So, we introduced a new style of qualifications for the row level security search. You can see the difference in the commands if you turn on SQL logging. Now, what we found is that there are some conditions where the combination of number of groups users are in (generally users being in small numbers of groups) and the data volume where the older strategy actually works better. We have not found a pattern of exactly what triggers one strategy to be better vs. the other. SO, we have an option that you can set – that is detailed in the message below – where you can tell the system to use one strategy or the other. It will always be the case that a row level security controlled search will be slower than an unlimited access search as there are additional checks needed. However, if you find there are major differences, you may want to check which strategy for row level security enforcement is active and see if the other one is faster for your environment. Test a variety of users as you may find that if you have a mix of users with few groups and some in MANY groups that there is a difference and you need to choose which strategy is overall best for your organization. There is no data impact or risk or anything with the two options. They both work and protect your access and are secure. They both use the same data and neither alters any data. They simply build SQL differently to do the enforcement. So, you can try both options safely. I hope this offers some insight into this situation and what occurred and why this option exists. I am glad that it was the key to helping in your situation to be able to control which strategy you were using. Doug Mueller From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Brian Pancia Sent: Monday, November 21, 2016 8:17 AM To: arslist@ARSLIST.ORG Subject: Re: Performance Issues - Multitenancy ** Shawn, Thanks. That ended up being the fix. Brian ________________________________ From: Action Request System discussion list(ARSList) <arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>> on behalf of Shawn Scutchings <shawn.scutchi...@gov.ab.ca<mailto:shawn.scutchi...@gov.ab.ca>> Sent: Friday, November 18, 2016 3:12 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance Issues - Multitenancy ** We had a similar issue and were provided the following by BMC: The Engineer assisting us with this issue has reviewed the information provided and agrees the query we see taking to complete are running row level access. He would like you to test disabling the new RLS implementation and use the old implementation to see if the issue persists or not. 1. Please open the Centralized Configuration form in the com.bmc.arsys.server.shared section 2. Add the following parameter: Disable-New-RLS-Implementation with a value of true Disable-New-RLS-Implementation: T 3. Restart the servers in the group This change will use a LIKE clause to allow the database to search the columns directly. Once the change has been made the servers restarted, please enable API, SQL, and Filter logging and reproduce the issue. If the performance impact is seen searching the fields that have drop-down menus for non admin users after the change has been made, please run the log zipper to gather and send the log files and forward the zip file along with the name of the user who performed the search. Fixed our issue…worth a try. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of LJ LongWing Sent: Friday, November 18, 2016 1:03 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance Issues - Multitenancy ** Brian, Turn on SQL Logging and perform the same search between the two different users and compare the SQL, maybe even provide the sql here for analysis....the 'slow vs fast' queries should be fairly obvious what's causing the difference. On Fri, Nov 18, 2016 at 11:24 AM, Brian Pancia <panc...@finityit.com<mailto:panc...@finityit.com>> wrote: ** We are running into some serious performance issues with multitenancy. We're on BMC ITSM 9.1 SP1 and SQL Server 2012. If I do a search on a form like CTM:People with an account that has unrestricted access the search comes back in about 2-3 seconds for 130000 records. That same search with a user that is restricted to a certain company will come back in 70 seconds, which is a significant difference. That is the first issue. The second issue is that the database server CPU utilization will spike to 100% during the searches. During the unrestricted user test not a big deal because it is only a couple seconds and no one notices the spike. However, for the other user it brings the system to a halt for 70 seconds. If the user kills their session prior to the search complete the search will hang in the database and consume 100% of the CPU indefinitely. Any recommendations would be appreciated. We have done all the BMC recommended performance tuning on the systems. Thanks, Brian DISCLAIMER: The information contained in this e-mail and its attachments contain confidential information belonging to the sender, which is legally privileged. The information is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are notified that any disclosure, copying, distribution or action in reliance upon the contents of the information transmitted is strictly prohibited. If you have received this information in error, please delete it immediately. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. _ARSlist: "Where the Answers Are" and have been for 20 years_ DISCLAIMER: The information contained in this e-mail and its attachments contain confidential information belonging to the sender, which is legally privileged. The information is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are notified that any disclosure, copying, distribution or action in reliance upon the contents of the information transmitted is strictly prohibited. If you have received this information in error, please delete it immediately. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ DISCLAIMER: The information contained in this e-mail and its attachments contain confidential information belonging to the sender, which is legally privileged. The information is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are notified that any disclosure, copying, distribution or action in reliance upon the contents of the information transmitted is strictly prohibited. If you have received this information in error, please delete it immediately. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
