> 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). > > Andrew Luo wrote: > 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.
yes, that sounds good. These columns can be removed in next major release, 3.0 i.e., master branch. Assuming this patch makes to 2.4 release, upgrade from 2.4 to 3.0 will not require restart of Ranger - since Ranger 2.4 will not be referring to columns deleted in 3.0. - Madhan ----------------------------------------------------------- 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 > >
