[
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)
