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

[Pavol Mederly]

Andrew,

now I see I am going to have a look at it anyhow: 
https://jira.evolveum.com/browse/MID-4776. I will let you know.

Pavol Mederly
Software developer
evolveum.com

On 28.07.2018 10:39, Pavol Mederly wrote:
> 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
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint