[midPoint] Users list is slow with large numbers of users
Petr Gašparík - AMI Praha a.s.
petr.gasparik at ami.cz
Mon Aug 6 12:48:09 CEST 2018
I am eager to know what time you get, Andrew. Please let us informed :)
--
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-31 19:49 GMT+02:00 Pavol Mederly <mederly at evolveum.com>:
> PS: and yes, it is quite probable there will be 3.7.3 release. But it is
> not clear exactly when it will be.
>
> Pavol Mederly
> Software developerevolveum.com
>
> On 31.07.2018 19:46, Pavol Mederly wrote:
>
> 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 developerevolveum.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>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <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>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <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>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <midPoint at lists.evolveum.com><mailto:midPoint at lists.evolveum.com>
> <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>
> <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>
> <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 listmidPoint at lists.evolveum.comhttp://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/20180806/5c2fc6c1/attachment.htm>
More information about the midPoint
mailing list