You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ranger.apache.org by Pradeep Agrawal <pr...@gmail.com> on 2019/08/08 11:05:53 UTC
Review Request 71252: RANGER-2529: Create index on obj_id and
obj_class_type column of x_data_hist table
-----------------------------------------------------------
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
Re: Review Request 71252: RANGER-2529: Create index on obj_id and
obj_class_type column of x_data_hist table
Posted by Pradeep Agrawal <pr...@gmail.com>.
> On Aug. 8, 2019, 7:32 p.m., Velmurugan Periasamy wrote:
> > security-admin/db/mysql/patches/042-add-index-on-xdatahist-table.sql
> > Lines 24 (patched)
> > <https://reviews.apache.org/r/71252/diff/1/?file=2160257#file2160257line24>
> >
> > During upgrade, index creation might take a long time depending upon data in x_data_hist table. Would be better to document and also provide SQL to create the index separately, so that this can be done prior to the upgrade.
Sure I will add the details on the JIRA.
- Pradeep
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/71252/#review217129
-----------------------------------------------------------
On Aug. 8, 2019, 11:05 a.m., Pradeep Agrawal wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/71252/
> -----------------------------------------------------------
>
> (Updated Aug. 8, 2019, 11:05 a.m.)
>
>
> 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
>
>
Re: Review Request 71252: RANGER-2529: Create index on obj_id and
obj_class_type column of x_data_hist table
Posted by Velmurugan Periasamy <vp...@hortonworks.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/71252/#review217129
-----------------------------------------------------------
security-admin/db/mysql/patches/042-add-index-on-xdatahist-table.sql
Lines 24 (patched)
<https://reviews.apache.org/r/71252/#comment304393>
During upgrade, index creation might take a long time depending upon data in x_data_hist table. Would be better to document and also provide SQL to create the index separately, so that this can be done prior to the upgrade.
- Velmurugan Periasamy
On Aug. 8, 2019, 11:05 a.m., Pradeep Agrawal wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/71252/
> -----------------------------------------------------------
>
> (Updated Aug. 8, 2019, 11:05 a.m.)
>
>
> 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
>
>
Re: Review Request 71252: RANGER-2529: Create index on obj_id and
obj_class_type column of x_data_hist table
Posted by Velmurugan Periasamy <vp...@hortonworks.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/71252/#review217130
-----------------------------------------------------------
Ship it!
Ship It!
- Velmurugan Periasamy
On Aug. 8, 2019, 11:05 a.m., Pradeep Agrawal wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/71252/
> -----------------------------------------------------------
>
> (Updated Aug. 8, 2019, 11:05 a.m.)
>
>
> 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
>
>