[midPoint] Users list is slow with large numbers of users

[Pavol Mederly]

Hello Andrew,

how long does it take to display the user list (in GUI) in your case?

I know the query is like you say - and we know it can be optimized - but 
in reality the SQL command sent to the database should limit the results 
to first 20 records, so it should be faster than processing SQL query 
without limits. (Anyway, we can sort this out if needed.)

As for the columns, please have a look at "Custom columns configuration" 
in https://wiki.evolveum.com/display/midPoint/Admin+GUI+Configuration.

Hope this helps,

Pavol Mederly
Software developer
evolveum.com

On 27.07.2018 18:03, Andrew Morgan wrote:
> We are running midPoint v3.7.2 (we delayed upgrading to v3.8 because 
> the Google connector is broken).  We recently loaded about 110,000 
> users in midPoint.  When I click on Users > List Users, midPoint is 
> waiting for the results of this database query:
>
> MariaDB [midpointdev]> explain select ruser0_.oid as col_0_0_, 
> ruser0_2_.fullObject as col_1_0_, ruser0_2_.stringsCount as col_2_0_, 
> ruser0_2_.longsCount as col_3_0_, ruser0_2_.datesCount as col_4_0_, 
> ruser0_2_.referencesCount as col_5_0_, ruser0_2_.polysCount as 
> col_6_0_, ruser0_2_.booleansCount as col_7_0_ from m_user ruser0_ 
> inner join m_focus ruser0_1_ on ruser0_.oid=ruser0_1_.oid inner join 
> m_object ruser0_2_ on ruser0_.oid=ruser0_2_.oid order by 
> ruser0_2_.name_orig asc;
> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+ 
>
> | id   | select_type | table     | type   | possible_keys | 
> key          | key_len | ref                     | rows   | 
> Extra                                        |
> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+ 
>
> |    1 | SIMPLE      | ruser0_   | index  | PRIMARY       | 
> uc_user_name | 768     | NULL                    | 100839 | Using 
> index; Using temporary; Using filesort |
> |    1 | SIMPLE      | ruser0_1_ | eq_ref | PRIMARY       | 
> PRIMARY      | 110     | midpointdev.ruser0_.oid |      1 | Using 
> index                                  |
> |    1 | SIMPLE      | ruser0_2_ | eq_ref | PRIMARY       | 
> PRIMARY      | 110     | midpointdev.ruser0_.oid |      1 
> |                                              |
> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+ 
>
>
> Without the aliases, it is a little easier to read:
>
> MariaDB [midpointdev]> explain select m_user.oid, m_object.fullObject, 
> m_object.stringsCount, m_object.longsCount, m_object.datesCount, 
> m_object.referencesCount, m_object.polysCount, m_object.booleansCount 
> from m_user inner join m_focus on m_user.oid=m_focus.oid inner join 
> m_object on m_user.oid=m_object.oid order by m_object.name_orig asc;
> +------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+ 
>
> | id   | select_type | table    | type   | possible_keys | 
> key          | key_len | ref                    | rows   | 
> Extra                                        |
> +------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+ 
>
> |    1 | SIMPLE      | m_user   | index  | PRIMARY       | 
> uc_user_name | 768     | NULL                   | 100839 | Using 
> index; Using temporary; Using filesort |
> |    1 | SIMPLE      | m_focus  | eq_ref | PRIMARY       | 
> PRIMARY      | 110     | midpointdev.m_user.oid |      1 | Using 
> index                                  |
> |    1 | SIMPLE      | m_object | eq_ref | PRIMARY       | 
> PRIMARY      | 110     | midpointdev.m_user.oid |      1 
> |                                              |
> +------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+ 
>
>
> The result of this query is about 440MB of data.  "111690 rows in set 
> (22.84 sec)"  (BTW, join on m_focus isn't needed in this query, right?)
>
> That is a LOT of data to push around anytime the Users list is 
> loaded.  We haven't even loaded all of our users yet!  Our total user 
> population is around 350,000.
>
> Is there a way to set some defaults for the User list?  For example, 
> could I change it to not display anything until a search is 
> performed?  Ideally, it wouldn't try to fetch m_object.fullObject, at 
> least until some filter is added.
>
> On a somewhat related note, I'd like to change which columns are 
> displayed in the User list.  For example, I would remove Fullname and 
> Email and add one of our custom attributes (extension/username).  Is 
> that possible?
>
> Thanks,
>
> Andy Morgan
> Systems Administrator, Identity & Access Management
> Information Services | Oregon State University
> 541-737-8877 | is.oregonstate.edu
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint