-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/71252/
-----------------------------------------------------------
Review request for ranger, Ankita Sinha, bhavik patel, Gautam Borad, Abhay
Kulkarni, Madhan Neethiraj, Mehul Parikh, Nikhil P, Nitin Galave, Ramesh Mani,
Sailaja Polavarapu, and Velmurugan Periasamy.
Bugs: RANGER-2529
https://issues.apache.org/jira/browse/RANGER-2529
Repository: ranger
Description
-------
**Problem Statement:** While working on RANGER-2499 found that during policy
delete there is a get request on x_data_hist table before insert/update
operation on the same table. if x_data_hist table is large then get call are
slow and can be seen in slow query logs. Since there is no index on obj_id and
obj_class_type columns of x_data_hist table hence select query on that table is
consuming lot of time.
**Proposed Solution:** Proposing changes in the x_data_hist table schema to add
an index on obj_id and obj_class_type columns.
Diffs
-----
security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql a6b033acc
security-admin/db/mysql/patches/042-add-index-on-xdatahist-table.sql
PRE-CREATION
security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
a4439d234
security-admin/db/oracle/patches/042-add-index-on-xdatahist-table.sql
PRE-CREATION
security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
2d5a8917c
security-admin/db/postgres/patches/042-add-index-on-xdatahist-table.sql
PRE-CREATION
security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
f2e2840e9
security-admin/db/sqlanywhere/patches/042-add-index-on-xdatahist-table.sql
PRE-CREATION
security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
be1e8a3c3
security-admin/db/sqlserver/patches/042-add-index-on-xdatahist-table.sql
PRE-CREATION
Diff: https://reviews.apache.org/r/71252/diff/1/
Testing
-------
**Observed Performance with 40000+ records on x_data_hist table**
Without patch/index query execution time: 600ms to 1000ms
With patch/index query execution time: 50ms to 100ms
Tested on mysql/oracle/postgres/sqlanywhere database in a fresh install and
upgrad case.
upgrad testing was done from ranger-0.7 to ranger master branch.
Thanks,
Pradeep Agrawal
