[midPoint] Users list is slow with large numbers of users
Petr Gašparík - AMI Praha a.s.
petr.gasparik at ami.cz
Sun Jul 29 00:57:27 CEST 2018
Yes, that's outr case, I can copy what Andrew says.
Thank you for looking at that, Pavol!
Most nerving thing is that it is slow even on "Browse repo" level, where I
would expect to list everything fast (because there is no extra decoration)
have a great day! Petr
--
s pozdravem
Petr Gašparík
solution architect
gsm: [+420] 603 523 860
e-mail: petr.gasparik at ami.cz
AMI Praha a.s.
Pláničkova 11
162 00 Praha 6
tel.: [+420] 274 783 239
web: www.ami.cz
[image: AMI Praha a.s.]
Textem tohoto e-mailu podepisující neslibuje uzavřít ani neuzavírá za
společnost AMI Praha a.s.
jakoukoliv smlouvu. Každá smlouva, pokud bude uzavřena, musí mít výhradně
písemnou formu.
2018-07-28 16:51 GMT+02:00 Pavol Mederly <mederly at evolveum.com>:
> 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
>>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20180729/625f244c/attachment.htm>
More information about the midPoint
mailing list