UserDaoImp.getAdminRolesFilter slow query

[Nicola Scendoni]

Hi guys,

Working on 0_15_X release I found a very slow query (with 2,5 Milion of users) on UserDaoImp.getAdminRolesFilter.
Can you tell me what the query inside this method has to do? Do you have any suggestion about how to improve the query?
I paste the method in org.syncope.core.persistence.dao.impl.UserDaoImp class below:

    private String getAdminRolesFilter(final Set<Long> adminRoles) {
        final StringBuilder adminRolesFilter = new StringBuilder();
        if (adminRoles == null || adminRoles.isEmpty()) {
            adminRolesFilter.append("SELECT syncopeUser_id AS user_id ").
                    append("FROM Membership");
        } else {
            adminRolesFilter.append("SELECT syncopeUser_id AS user_id ").
                    append("FROM Membership M1 ").
                    append("WHERE syncopeRole_id IN (");
            adminRolesFilter.append("SELECT syncopeRole_id ").
                    append("FROM Membership M2 ").
                    append("WHERE M2.syncopeUser_id=M1.syncopeUser_id ").
                    append("AND syncopeRole_id NOT IN (");
            adminRolesFilter.append(
                    "SELECT id AS syncopeRole_id FROM SyncopeRole");
            boolean firstRole = true;
            for (Long adminRoleId : adminRoles) {
                if (firstRole) {
                    adminRolesFilter.append(" WHERE");
                    firstRole = false;
                } else {
                    adminRolesFilter.append(" OR");
                }

                adminRolesFilter.append(" id=").append(adminRoleId);
            }
            adminRolesFilter.append("))");
        }

        return adminRolesFilter.toString();
    }

[Francesco Chicchiriccò]

On 18/05/2011 18:03, Nicola Scendoni wrote:
> Hi guys,
>
> Working on 0_15_X release I found a very slow query (with 2,5 Milion
> of users) on UserDaoImp.getAdminRolesFilter.

0_15_X? This has to be a typo: probably, the release you are referring
to should be 0.4.3, out of the 0_4_X branch.

> Can you tell me what the query inside this method has to do? Do you
> have any suggestion about how to improve the query?
> I paste the method in org.syncope.core.persistence.dao.impl.UserDaoImp
> class below:

The method you are referring to is in the UserSearchDAOImpl class (see
[1]) and its logic hasn't changed at all up to the current trunk [2].

The invocation of such method is part of the Authorization process in
Syncope: let me try to outline it before going to your specific question.

The foundations are built by the Entitlements which are basically
strings describing the right to perform an operation. Defaults
entitlements are included at the end of [3] and always loaded in Syncope
database.

Entitlements can only be assigned to roles: this is the basis of a
role-based authorization mechanism.

Two kind of entitlements are managed: normal entitlements and "role
operational" entitlements. The former are related to the general
operations that can be performed (like TASK_DELETE or CONNECTOR_UPDATE)
while the latter are specifically bound to each and every role defined
(like ROLE_10 or ROLE_23).

Why do we need this distinction? Because Syncope implements a delegated
role-based authorization model (as per issue #85 [4] - take a look there
for some additional detail) so that an user can manage other users and
this can be specified with a very fine-grained mechanism.
As a result, user A can create users under role 5 but not under role 7,
user B can update users under role 6 and 8, user C can update role 8.
This would imply, for example, that A needs to own USER_CREATE and
ROLE_5 entitlements (but not ROLE_7).

Of course there is a special admin user, granted by all the entitlements
defined in the system, thus capable of performing any available operation.

All that above applies to user search as well: what would happen, in
fact, if search results would include one or more users for which the
calling user is not entitled for reading?
This is exactly the place in which the method outlined above comes into
the game.

As soon as possible, a more detailed description of such process must be
added to the documentation: I've opened issue #113 [5] for this.

Cheers.

[1]
http://syncope.googlecode.com/svn/tags/syncope-0.4.3/core/src/main/java/org/syncope/core/persistence/dao/impl/UserSearchDAOImpl.java
[2]
http://syncope.googlecode.com/svn/trunk/core/src/main/java/org/syncope/core/persistence/dao/impl/UserSearchDAOImpl.java
[3]
http://syncope.googlecode.com/svn/trunk/core/src/main/resources/content.xml
[4] http://code.google.com/p/syncope/issues/detail?id=85
[5] http://code.google.com/p/syncope/issues/detail?id=113

--
Francesco Chicchiricc�

"Computer Science is no more about computers than astronomy
is about telescopes." (E. W. Dijkstra)

[Nicola Scendoni]

Il giorno 19 maggio 2011 09:49, Francesco Chicchiriccò <chicch...@gmail.com> ha scritto:

On 18/05/2011 18:03, Nicola Scendoni wrote:

Hi guys,

Working on 0_15_X release I found a very slow query (with 2,5 Milion of users) on UserDaoImp.getAdminRolesFilter.

0_15_X? This has to be a typo: probably, the release you are referring to should be 0.4.3, out of the 0_4_X branch.

Of course I meant 0_4_X, sorry for my mistake...
 

Can you tell me what the query inside this method has to do? Do you have any suggestion about how to improve the query?
I paste the method in org.syncope.core.persistence.dao.impl.UserDaoImp class below:

The method you are referring to is in the UserSearchDAOImpl class (see [1]) and its logic hasn't changed at all up to the current trunk [2].

The invocation of such method is part of the Authorization process in Syncope: let me try to outline it before going to your specific question.

The foundations are built by the Entitlements which are basically strings describing the right to perform an operation. Defaults entitlements are included at the end of [3] and always loaded in Syncope database.

Entitlements can only be assigned to roles: this is the basis of a role-based authorization mechanism.

Two kind of entitlements are managed: normal entitlements and "role operational" entitlements. The former are related to the general operations that can be performed (like TASK_DELETE or CONNECTOR_UPDATE) while the latter are specifically bound to each and every role defined (like ROLE_10 or ROLE_23).

Why do we need this distinction? Because Syncope implements a delegated role-based authorization model (as per issue #85 [4] - take a look there for some additional detail) so that an user can manage other users and this can be specified with a very fine-grained mechanism.
As a result, user A can create users under role 5 but not under role 7, user B can update users under role 6 and 8, user C can update role 8. This would imply, for example, that A needs to own USER_CREATE and ROLE_5 entitlements (but not ROLE_7).

Of course there is a special admin user, granted by all the entitlements defined in the system, thus capable of performing any available operation.

All that above applies to user search as well: what would happen, in fact, if search results would include one or more users for which the calling user is not entitled for reading?
This is exactly the place in which the method outlined above comes into the game.

Thanks, now the authorization mechanism it's more clear for me. I think we need to improuve the performance of this query as well, or define some index, since with a huge number of users the query not terminate (at least not terminate afer 24 hours). If you agree I'll open an issue. Do you have any idea about how to improuve that?

Regards

Nicola
 

As soon as possible, a more detailed description of such process must be added to the documentation: I've opened issue #113 [5] for this.

Cheers.

[1] http://syncope.googlecode.com/svn/tags/syncope-0.4.3/core/src/main/java/org/syncope/core/persistence/dao/impl/UserSearchDAOImpl.java
[2] http://syncope.googlecode.com/svn/trunk/core/src/main/java/org/syncope/core/persistence/dao/impl/UserSearchDAOImpl.java
[3] http://syncope.googlecode.com/svn/trunk/core/src/main/resources/content.xml
[4] http://code.google.com/p/syncope/issues/detail?id=85
[5] http://code.google.com/p/syncope/issues/detail?id=113

--
Francesco Chicchiriccò

[Fabio Martelli]

Il giorno 19/mag/2011, alle ore 10.29, Nicola Scendoni ha scritto:

Il giorno 19 maggio 2011 09:49, Francesco Chicchiriccò <chicch...@gmail.com> ha scritto:

On 18/05/2011 18:03, Nicola Scendoni wrote:

Hi guys,

Working on 0_15_X release I found a very slow query (with 2,5 Milion of users) on UserDaoImp.getAdminRolesFilter.

0_15_X? This has to be a typo: probably, the release you are referring to should be 0.4.3, out of the 0_4_X branch.

Of course I meant 0_4_X, sorry for my mistake...
 

Can you tell me what the query inside this method has to do? Do you have any suggestion about how to improve the query?
I paste the method in org.syncope.core.persistence.dao.impl.UserDaoImp class below:

The method you are referring to is in the UserSearchDAOImpl class (see [1]) and its logic hasn't changed at all up to the current trunk [2].

The invocation of such method is part of the Authorization process in Syncope: let me try to outline it before going to your specific question.

The foundations are built by the Entitlements which are basically strings describing the right to perform an operation. Defaults entitlements are included at the end of [3] and always loaded in Syncope database.

Entitlements can only be assigned to roles: this is the basis of a role-based authorization mechanism.

Two kind of entitlements are managed: normal entitlements and "role operational" entitlements. The former are related to the general operations that can be performed (like TASK_DELETE or CONNECTOR_UPDATE) while the latter are specifically bound to each and every role defined (like ROLE_10 or ROLE_23).

Why do we need this distinction? Because Syncope implements a delegated role-based authorization model (as per issue #85 [4] - take a look there for some additional detail) so that an user can manage other users and this can be specified with a very fine-grained mechanism.
As a result, user A can create users under role 5 but not under role 7, user B can update users under role 6 and 8, user C can update role 8. This would imply, for example, that A needs to own USER_CREATE and ROLE_5 entitlements (but not ROLE_7).

Of course there is a special admin user, granted by all the entitlements defined in the system, thus capable of performing any available operation.

All that above applies to user search as well: what would happen, in fact, if search results would include one or more users for which the calling user is not entitled for reading?
This is exactly the place in which the method outlined above comes into the game.

Thanks, now the authorization mechanism it's more clear for me. I think we need to improuve the performance of this query as well, or define some index, since with a huge number of users the query not terminate (at least not terminate afer 24 hours). If you agree I'll open an issue. Do you have any idea about how to improuve that?

Hi Nicola,

can you provide the query that you are going to execute.

The method getAdminRolesFilter is private: how did you do to say that the problem is into this method?

Please, let us have some additional information.

Regards,

F.

[Nicola Scendoni]

2011/5/19 Fabio Martelli <fabio.m...@gmail.com>

Il giorno 19/mag/2011, alle ore 10.29, Nicola Scendoni ha scritto:

Il giorno 19 maggio 2011 09:49, Francesco Chicchiriccò <chicch...@gmail.com> ha scritto:

On 18/05/2011 18:03, Nicola Scendoni wrote:

Hi guys,

Working on 0_15_X release I found a very slow query (with 2,5 Milion of users) on UserDaoImp.getAdminRolesFilter.

0_15_X? This has to be a typo: probably, the release you are referring to should be 0.4.3, out of the 0_4_X branch.

Of course I meant 0_4_X, sorry for my mistake...
 

Can you tell me what the query inside this method has to do? Do you have any suggestion about how to improve the query?
I paste the method in org.syncope.core.persistence.dao.impl.UserDaoImp class below:

The method you are referring to is in the UserSearchDAOImpl class (see [1]) and its logic hasn't changed at all up to the current trunk [2].

The invocation of such method is part of the Authorization process in Syncope: let me try to outline it before going to your specific question.

The foundations are built by the Entitlements which are basically strings describing the right to perform an operation. Defaults entitlements are included at the end of [3] and always loaded in Syncope database.

Entitlements can only be assigned to roles: this is the basis of a role-based authorization mechanism.

Two kind of entitlements are managed: normal entitlements and "role operational" entitlements. The former are related to the general operations that can be performed (like TASK_DELETE or CONNECTOR_UPDATE) while the latter are specifically bound to each and every role defined (like ROLE_10 or ROLE_23).

Why do we need this distinction? Because Syncope implements a delegated role-based authorization model (as per issue #85 [4] - take a look there for some additional detail) so that an user can manage other users and this can be specified with a very fine-grained mechanism.
As a result, user A can create users under role 5 but not under role 7, user B can update users under role 6 and 8, user C can update role 8. This would imply, for example, that A needs to own USER_CREATE and ROLE_5 entitlements (but not ROLE_7).

Of course there is a special admin user, granted by all the entitlements defined in the system, thus capable of performing any available operation.

All that above applies to user search as well: what would happen, in fact, if search results would include one or more users for which the calling user is not entitled for reading?
This is exactly the place in which the method outlined above comes into the game.

Thanks, now the authorization mechanism it's more clear for me. I think we need to improuve the performance of this query as well, or define some index, since with a huge number of users the query not terminate (at least not terminate afer 24 hours). If you agree I'll open an issue. Do you have any idea about how to improuve that?

Hi Nicola,

can you provide the query that you are going to execute.

The method getAdminRolesFilter is private: how did you do to say that the problem is into this method?

Hi Fabio,

our DBA noticed that there are quite a lot of dead queres like:

select * from ( SELECT COUNT(user_id) FROM (SELECT u.user_id FROM (SELECT DISTINCT user_id FROM user_search_membership WHERE role_id=:1) u WHERE user_id NOT IN (SELECT syncopeUser_id AS user_id FROM Membership M1 WHERE syncopeRole_id IN (SELECT syncopeRole_id FROM Membership M2 WHERE M2.syncopeUser_id=M1.syncopeUser_id AND syncopeRole_id NOT IN (SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=34 OR id=35 OR id=32 OR id=33 OR id=6 OR id=500 OR id=11 OR id=13 OR id=17 OR id=16 OR id=19 OR id=18 OR id=21 OR id=20 OR id=23 OR id=22 OR id=25 OR id=24 OR id=27 OR id=26 OR id=29 OR id=28 OR id=30)))) count_user_id ) where rownum <= :2

We reproduced this quey following the steps on Syncope Console:
1. Go on User Tab
2. Search an user by membership.

Analyzing the syncope-core code we found UserDaoImp.getAdminRolesFilter
Do you think our analisys is correct?

Thanks

Nicola
 

[Fabio Martelli]

Hi Nicola,

unfortunately the search feature is a little bit limited: with all that amount of data (2,5M of users) stored on the central repository some types of queries are hard to be processed in time.  

The problem is that certain types of queries are based on subqueries concerning result sets too much big. These kind of queries are, more or less, equivalent to search for all the users.

I'm sure that your problem is a sub-query. Probably the following

SELECT DISTINCT user_id FROM user_search_membership WHERE role_id=:1

I suppose that the role that you provided is associated to a great amount of users.

This is an obvious limitation that hasn't a solution yet.

Please, consider that Syncope has no problem to manage the amount of data that you indicated.

Btw the searches that can be performed by the console or via rest request are strongly dependent on the size of the returned result set of the principal query or subqueries.

I hope I was clear. Let me know if you need more clarification about.

Regards,

F.