[midPoint] Users list is slow with large numbers of users
Pavol Mederly
mederly at evolveum.com
Tue Jul 31 19:46:46 CEST 2018
Andy,
yes, you need to build from midPoint sources as described here
<https://wiki.evolveum.com/display/midPoint/Building+MidPoint+From+Source+Code>.
You can clone the midPoint repository like this:
|git clone -b support-3.7 https:||//github||.com||/Evolveum/midpoint||.git|
||
Then you can build as indicated on the wiki page. I suggest building
with tests turned off, it is much faster.
Hope this helps,
Pavol Mederly
Software developer
evolveum.com
On 31.07.2018 19:19, Andrew Morgan wrote:
> Wow, thanks for the quick work. I'm not familiar with the support-3.7
> branch. Is there a way for me to get this? Will there be a release
> (3.7.3?) of this?
>
> Thanks,
>
> Andy Morgan
> Systems Administrator, Identity & Access Management
> Information Services | Oregon State University
> 541-737-8877 | is.oregonstate.edu
>
> On Tue, 31 Jul 2018, Pavol Mederly wrote:
>
>> Hello,
>>
>> the fix is implemented on support-3.7 branch. Please do not forget to
>> apply DB migration scripts.
>>
>> Best regards,
>>
>> Pavol Mederly
>> Software developer
>> evolveum.com
>>
>>
>> On 30.07.2018 19:24, Pavol Mederly wrote:
>>
>> Andrew,
>>
>> I think I got it. It seems to be a difference between 3.7 and 3.8,
>> indeed.
>>
>> The query in 3.7 is like this:
>>
>> 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
>> limit ?
>>
>> and in 3.8 it is:
>>
>> 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_.name_orig asc
>> limit 20
>>
>> Note the difference. The 3.7 version orders on a row in doubly joined
>> table, whereas the 3.8 version orders on a row in the "main" table.
>> When executing against my test database (10K rows, each having ~100
>> KB of XML data), the former takes 25 seconds, whereas the latter
>> takes 46 milliseconds.
>>
>> Having seen this I remember that our colleague Viliam really
>> implemented querying over "local" name column in 3.8, instead of the
>> topmost one. :) Maybe this was the reason. Anyway, tomorrow I will
>> try to find the least painful way how to bring this change into
>> 3.6/3.7; and will let you know.
>>
>> Best regards,
>>
>> Pavol Mederly
>> Software developer
>> evolveum.com
>>
>>
>> On 30.07.2018 17:58, Andrew Morgan wrote:
>> Pavol,
>>
>> I have attached the section of midpoint.log as requested. As you can
>> see, it takes about 20 seconds to display the results in the GUI.
>>
>> When I run the same query in the mysql client, is says, "20 rows in
>> set (13.93 sec)".
>>
>> Here is the EXPLAIN for it again:
>>
>> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
>>
>> | id | select_type | table | type | possible_keys |
>> key | key_len | ref | rows |
>> Extra |
>> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
>>
>> | 1 | SIMPLE | ruser0_ | index | PRIMARY |
>> uc_user_name | 768 | NULL | 100970 | 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
>> | |
>> +------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
>>
>>
>> Our database server is CentOS 7.5 running
>> MariaDB-server-10.2.16-1.el7.centos.x86_64. The VM has 4GB of RAM
>> and 2 CPUs (Xeon Gold 5118 CPU @ 2.30GHz). We haven't done much
>> tuning to it yet:
>>
>> [mysqld]
>> innodb_buffer_pool_size = 2G
>> max_allowed_packet = 256M
>>
>>
>> Thanks for helping us investigate!
>>
>> Andy Morgan
>> Systems Administrator, Identity & Access Management
>> Information Services | Oregon State University
>> 541-737-8877 | is.oregonstate.edu
>>
>> On Mon, 30 Jul 2018, Pavol Mederly wrote:
>>
>> Andrew, Petr,
>>
>> my observations on my developer machine running midPoint (master) and
>> MySQL 5.7 locally are here:
>>
>> I have imported 100K users (almost empty objects, only a name,
>> nothing more). After listing them via User List page the response is
>> almost immediate. Profiling says:
>>
>> 2018-07-30 16:56:08,973 [MODEL] [http-nio-8080-exec-9] DEBUG
>> (PROFILING): #### Entry: 602
>> ...model.impl.controller.ModelController->searchObjects
>> 2018-07-30 16:56:08,973 [MODEL] [http-nio-8080-exec-9] TRACE
>> (PROFILING): ###### args: (UserType, Q{null filterPAGING: O: 0,M:
>> 20,ORD: [name ASCENDING], ,,
>> [ObjectOperationOptions(/:distinct=true)], Task(id:1532962568973-0-1,
>> name:null, oid:null),
>> R(com.evolveum.midpoint.web.component.data.SelectableBeanObjectDataProvider.searchObjects
>> UNKNOWN null))
>> 2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9] DEBUG
>> (PROFILING): #### Entry: 604
>> ...repo.sql.SqlRepositoryServiceImpl->searchObjects
>> 2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9] TRACE
>> (PROFILING): ###### args: (UserType, Q{null filterPAGING: O: 0,M:
>> 20,ORD: [name ASCENDING], ,,
>> [ObjectOperationOptions(/:distinct=true)],
>> R(com.evolveum.midpoint.model.api.ModelService.searchObjects UNKNOWN
>> null))
>> 2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9] DEBUG
>> (org.hibernate.SQL): 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_.name_orig asc limit ?
>> 2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9] DEBUG
>> (PROFILING): ##### Exit: 604
>> ...repo.sql.SqlRepositoryServiceImpl->searchObjects etime: 6.079 ms
>> 2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9] TRACE
>> (PROFILING): ###### retval:
>> [user:00000000-0000-0000-0000-000000000002(administrator),user:63e9574b-6fa8-4f72-ab47-d7bc4a9b81b6(user
>> 100000),...]
>> 2018-07-30 16:56:08,993 [MODEL] [http-nio-8080-exec-9] DEBUG
>> (PROFILING): ##### Exit: 602
>> ...model.impl.controller.ModelController->searchObjects etime: 21.325 ms
>> 2018-07-30 16:56:08,993 [MODEL] [http-nio-8080-exec-9] TRACE
>> (PROFILING): ###### retval:
>> [user:00000000-0000-0000-0000-000000000002(administrator),user:63e9574b-6fa8-4f72-ab47-d7bc4a9b81b6(user
>> 100000),...]
>>
>> The time needed to get first 20 user records was 21 milliseconds. The
>> repository processing itself took about 6 milliseconds.
>>
>> The SQL command itself was:
>>
>> 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_.name_orig asc
>> limit ?
>>
>> So, yes, the command is the same as in your case.
>>
>> Please, could you do the same profiling as I did? I.e. in your
>> config.xml file please set up:
>>
>> <configuration>
>> <midpoint>
>> ...
>> <profilingEnabled>true</profilingEnabled>
>> </midpoint>
>> </configuration>
>>
>> And restart midPoint after the change. Then in the system
>> configuration (GUI: System -> Profiling) set the profiling:
>>
>> [cid:part1.EDEB0394.06F63FFB at evolveum.com]
>>
>> You can also set logging for org.hibernate.SQL to ALL (or TRACE) to
>> see SQL commands. And then please try opening the user list and send
>> us the relevant parts of midpoint.log file (i.e. text between Entry
>> and Exit of "...model.impl.controller.ModelController->searchObjects").
>>
>> 3.7.2 vs. 3.8 vs. 3.9-SNAPSHOT should make no difference in this
>> request, as far as I know. And I think that the user objects' size
>> should not perhaps make such a dramatic difference. I will have a
>> look at it more, however.
>>
>> Best regards,
>>
>> Pavol Mederly
>> Software developer
>> evolveum.com
>>
>>
>> On 28.07.2018 16:51, Pavol Mederly wrote:
>> 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<mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
>>
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com<mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
>>
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com<mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
>>
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>>
>>
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com<mailto:midPoint at lists.evolveum.com>
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>>
>>
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com<mailto: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/20180731/6721f8e6/attachment.htm>
More information about the midPoint
mailing list