[jira] [Commented] (SENTRY-228) Performance of GRANT can suffer if num tables > 10000

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

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

Arun Suresh commented on SENTRY-228:
------------------------------------

The issues is basically due to the fact that whenever a new Privilege is added to a role, JDO fires this SELECT query :  

{quote}
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` = 8
{quote}

This is an inner join that lists as the privileges for the role... unfortunately, since the number of privileges per role is large, it takes a while..

A solution would be to 
1) Either eliminate the join quay : This does not seem to be possible in JDO
2) Get JDO to inner join on the ROLE table rather than the PRIVILEGE Table : This would be much faster as generally, number of roles in a system are in the 10s which privileges are in the 10000s (worse case). Apparently JDO just requires only either one of the joins to happen to ensure its object graph is in sync. 

Looks like Option 2 is possible.. will test performance and post patch it actually improves perf..



> Performance of GRANT can suffer if num tables > 10000
> -----------------------------------------------------
>
>                 Key: SENTRY-228
>                 URL: https://issues.apache.org/jira/browse/SENTRY-228
>             Project: Sentry
>          Issue Type: Bug
>            Reporter: Arun Suresh
>            Priority: Minor
>
> It is noticed that as the number of tables / privilege objects increase, granting a new Privilege to a role takes increasingly longer time.
> This issues is not as severe as SENTRY-178 .
> Now, it takes almost ~ 0.4 secs if number of tables > 10000 (while earlier it used to be 5 secs for tables > 4000)



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