-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74301/#review225166
-----------------------------------------------------------
@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).
- Madhan Neethiraj
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
>
>
