[jira] [Commented] (SENTRY-178) Poor performance for Sentry Policy Service as #of privileges is scaled up

["Arun Suresh (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SENTRY-178?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14002819#comment-14002819 ] 

Arun Suresh commented on SENTRY-178:
------------------------------------

Testing on MySQL ... I was able to replicate the Issue..
Basically, as Lenni specified, time to grant NEW Privileges to an EXISTING Role grows as the number of privileges (Tables) increase.

Findings from analyzing the MySQL query log :

* Assume we a role 'adminrole0'
* Assume the 'adminrole0' is already granted 'ALL' Privilege on Tables : 'testTable2' .. thru.. 'testTable5'.
* Following are the bunch of queries fired on MySQL for a  request to grant privilege on 'testTable6' to role 'adminrole0' :

{quote}
- SELECT @@session.tx_isolation
- SELECT 'org.apache.sentry.provider.db.service.model.MSentryRole' AS NUCLEUS_TYPE,`A0`.`CREATE_TIME`,`A0`.`GRANTOR_PRINCIPAL`,`A0`.`ROLE_NAME`,`A0`.`ROLE_ID` FROM `SENTRY_ROLE` `A0` WHERE `A0`.`ROLE_NAME` = 'adminrole0'

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryPrivilege' AS NUCLEUS_TYPE,`A0`.`URI`,`A0`.`ACTION`,`A0`.`CREATE_TIME`,`A0`.`DB_NAME`,`A0`.`GRANTOR_PRINCIPAL`,`A0`.`PRIVILEGE_NAME`,`A0`.`PRIVILEGE_SCOPE`,`A0`.`SERVER_NAME`,`A0`.`TABLE_NAME`,`A0`.`DB_PRIVILEGE_ID` FROM `SENTRY_DB_PRIVILEGE` `A0` WHERE `A0`.`PRIVILEGE_NAME` = 'testServer+testDb+testTable6+ALL'

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryPrivilege' AS NUCLEUS_TYPE,`A1`.`URI`,`A1`.`ACTION`,`A1`.`CREATE_TIME`,`A1`.`DB_NAME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`PRIVILEGE_NAME`,`A1`.`PRIVILEGE_SCOPE`,`A1`.`SERVER_NAME`,`A1`.`TABLE_NAME`,`A1`.`DB_PRIVILEGE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` `A0` INNER JOIN `SENTRY_DB_PRIVILEGE` `A1` ON `A0`.`DB_PRIVILEGE_ID` = `A1`.`DB_PRIVILEGE_ID` WHERE `A0`.`ROLE_ID` = 1

- INSERT INTO `SENTRY_DB_PRIVILEGE` (`DB_PRIVILEGE_ID`,`PRIVILEGE_SCOPE`,`GRANTOR_PRINCIPAL`,`SERVER_NAME`,`ACTION`,`PRIVILEGE_NAME`,`TABLE_NAME`,`URI`,`CREATE_TIME`,`DB_NAME`) VALUES (5,'TABLE','admin','testServer','ALL','testServer+testDb+testTable6+ALL','testTable6',null,1400559736983,'testDb')

- SELECT `DB_PRIVILEGE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` THIS WHERE THIS.`DB_PRIVILEGE_ID`=5 AND THIS.`ROLE_ID`=1

- INSERT INTO `SENTRY_ROLE_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`,`ROLE_ID`) VALUES (5,1)

- SELECT `ROLE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` THIS WHERE THIS.`ROLE_ID`=1 AND THIS.`DB_PRIVILEGE_ID`=5

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryGroup' AS NUCLEUS_TYPE,`A1`.`CREATE_TIME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`GROUP_NAME`,`A1`.`GROUP_ID` FROM `SENTRY_ROLE_GROUP_MAP` `A0` INNER JOIN `SENTRY_GROUP` `A1` ON `A0`.`GROUP_ID` = `A1`.`GROUP_ID` WHERE `A0`.`ROLE_ID` = 1

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryRole' AS NUCLEUS_TYPE,`A1`.`CREATE_TIME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`ROLE_NAME`,`A1`.`ROLE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` `A0` INNER JOIN `SENTRY_ROLE` `A1` ON `A0`.`ROLE_ID` = `A1`.`ROLE_ID` WHERE `A0`.`DB_PRIVILEGE_ID` = 3

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryRole' AS NUCLEUS_TYPE,`A1`.`CREATE_TIME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`ROLE_NAME`,`A1`.`ROLE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` `A0` INNER JOIN `SENTRY_ROLE` `A1` ON `A0`.`ROLE_ID` = `A1`.`ROLE_ID` WHERE `A0`.`DB_PRIVILEGE_ID` = 4

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryRole' AS NUCLEUS_TYPE,`A1`.`CREATE_TIME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`ROLE_NAME`,`A1`.`ROLE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` `A0` INNER JOIN `SENTRY_ROLE` `A1` ON `A0`.`ROLE_ID` = `A1`.`ROLE_ID` WHERE `A0`.`DB_PRIVILEGE_ID` = 1

- SELECT 'org.apache.sentry.provider.db.service.model.MSentryRole' AS NUCLEUS_TYPE,`A1`.`CREATE_TIME`,`A1`.`GRANTOR_PRINCIPAL`,`A1`.`ROLE_NAME`,`A1`.`ROLE_ID` FROM `SENTRY_ROLE_DB_PRIVILEGE_MAP` `A0` INNER JOIN `SENTRY_ROLE` `A1` ON `A0`.`ROLE_ID` = `A1`.`ROLE_ID` WHERE `A0`.`DB_PRIVILEGE_ID` = 2

- commit
{quote}

The interesting thing to note are the last 4 queries.. DN has sent 4 select queries for all the existing privileges associated to the role..

Thus if there there exists already n Privileges associated to a role, a request to associated n + 1 th Privilege would result in at-least n select queries being fired...


> Poor performance for Sentry Policy Service as #of privileges is scaled up
> -------------------------------------------------------------------------
>
>                 Key: SENTRY-178
>                 URL: https://issues.apache.org/jira/browse/SENTRY-178
>             Project: Sentry
>          Issue Type: Bug
>    Affects Versions: 1.3.0
>            Reporter: Lenni Kuff
>            Priority: Critical
>
> I have observed that as the number of role privileges is scaled up, the performance of the Sentry Service (time it takes to execute a grant/revoke RPC) gets increasingly worse.
> The following is how long it takes to execute an RPC to grant/revoke a privilege from a role:
> {code}
> # of Role Privileges (each on different tables)
> 100 privileges ~2 RPCs/sec
> 1000 privileges ~1.5 RPCs/sec
> 2000 privileges - ~.5 RPCs/sec
> 4000 privileges - ~.2 RPCs/sec
> Configuration:
> - Sentry Policy Service -> Postgres Backend DB
> {code}
> This means the time to actually execute one grant/revoke RPC using a policy that is securing 4000 tables is >5s.
> I tried scaling up the number of clients, but that doesn't appear to improve the throughput since there is a lot of locking that is happening inside the Sentry Policy Service.



--
This message was sent by Atlassian JIRA
(v6.2#6252)