> On Feb. 8, 2023, 4:54 p.m., Madhan Neethiraj wrote: > > @Andrew - thank you for this patch to improve performance of policy > > changes. Changes in the patch look good. > > > > I have few suggestions: > > > > 1. Column named guid in ref tables is not mapped to any field in respective > > Java entity class. It might help to remove this unused column. > > 2. DB patch to drop constriants look good. However, removing columns from > > tables would require following sequence of steps - to avoid earlier version > > Ranger running into errors while referring to these columns: > > - stop Ranger server instances > > - apply the DB patch to remove columns > > - update Ranger admin server to newer version containing this patch > > - start Ranger server instances > > > > This will result in Ranger being not available for the duration of above > > steps. This will not be acceptable in deployments having Ranger in high > > availablity configuration. > > > > I suggest not to remove columns that are used by current version of Ranger. > > Note that these columns can be removed from scripts that create Ranger > > schema in a new deployment (scripts under directory named optimized).
I see. What I could recommend here is that we can not remove the columns during the upgrade, and revisit this a few months/years later when we're comfortable. - Andrew ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/74301/#review225166 ----------------------------------------------------------- On Feb. 8, 2023, 4:09 p.m., Andrew Luo wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/74301/ > ----------------------------------------------------------- > > (Updated Feb. 8, 2023, 4:09 p.m.) > > > Review request for ranger and Pradeep Agrawal. > > > Bugs: RANGER-2713 > https://issues.apache.org/jira/browse/RANGER-2713 > > > Repository: ranger > > > Description > ------- > > XXPolicyRef objects have fields such as create time, update time, added by > user ID, updated by user ID, but there fields are entirely useless since they > are all copied from the XXPolicy object. In addition, while improving > performance for creation of policies with large numbers of users, we > discovered that a lot of time was being spent in JPA converting these Date > objects especially. After removing these fields we saw a significant > performance improvement (a secondary benefit is less database space usage). > > We previously tried this commit and it caused a few issues (some typos on SQL > Server and SQL Anywhere, as well as upgrade scenarios - however this has been > fixed). > > > Diffs > ----- > > security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql > 9a79fe8ad > > security-admin/db/mysql/patches/061-drop-audit-columns-from-policy-ref-tables.sql > PRE-CREATION > security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql > fd6cec9a7 > > security-admin/db/oracle/patches/061-drop-audit-columns-from-policy-ref-tables.sql > PRE-CREATION > security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql > 4d5a8cedf > > security-admin/db/postgres/patches/061-drop-audit-columns-from-policy-ref-tables.sql > PRE-CREATION > > security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql > 3ed2a5b9c > > security-admin/db/sqlanywhere/patches/061-drop-audit-columns-from-policy-ref-tables.sql > PRE-CREATION > security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql > ca8f7da1f > > security-admin/db/sqlserver/patches/061-drop-audit-columns-from-policy-ref-tables.sql > PRE-CREATION > security-admin/src/main/java/org/apache/ranger/biz/PolicyRefUpdater.java > 6cc3509d8 > > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefAccessType.java > 6af8f99f4 > > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefCondition.java > 4f4409d6a > > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefDataMaskType.java > cb926740e > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefGroup.java > 32a1b9f24 > > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefResource.java > 115064621 > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefRole.java > 7aee502e0 > security-admin/src/main/java/org/apache/ranger/entity/XXPolicyRefUser.java > 8dfb92833 > > security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java > 85e457efa > security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java > 691ab52b3 > > > Diff: https://reviews.apache.org/r/74301/diff/2/ > > > Testing > ------- > > Fresh install and upgrade tested on all 5 databases (tested service and > policy creation) > > Also tested user deletion on MySQL: > > 1. Created new user > 2. Logged in as new user, created a policy > 3. Logged back in as original user, delete user that created policy > 4. Verified no errors, policy is still present/correct > > Also searched the source code and JPA queries xml for other references to > these fields > > > Thanks, > > Andrew Luo > >
