<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Andrew, Petr,</p>
<p>my observations on my developer machine running midPoint (master)
and MySQL 5.7 locally are here:<br>
</p>
<p>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:</p>
<tt><font size="-1">2018-07-30 16:56:08,973 [MODEL]
[http-nio-8080-exec-9] DEBUG (PROFILING): #### Entry: 602
...model.impl.controller.ModelController->searchObjects<br>
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))<br>
2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9]
DEBUG (PROFILING): #### Entry: 604
...repo.sql.SqlRepositoryServiceImpl->searchObjects<br>
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))<br>
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 ?<br>
2018-07-30 16:56:08,973 [REPOSITORY] [http-nio-8080-exec-9]
DEBUG (PROFILING): ##### Exit: 604
...repo.sql.SqlRepositoryServiceImpl->searchObjects etime: <font
color="#cc0000"><u><b>6.079 ms</b></u></font><br>
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),...]<br>
2018-07-30 16:56:08,993 [MODEL] [http-nio-8080-exec-9] DEBUG
(PROFILING): ##### Exit: 602
...model.impl.controller.ModelController->searchObjects
etime: <font color="#cc0000"><u><b>21.325 ms</b></u></font><br>
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),...]<br>
<br>
</font></tt>The time needed to get first 20 user records was 21
milliseconds. The repository processing itself took about 6
milliseconds.<br>
<br>
The SQL command itself was:<br>
<p><tt>select <br>
ruser0_.oid as col_0_0_, <br>
ruser0_2_.fullObject as col_1_0_, <br>
ruser0_2_.stringsCount as col_2_0_, <br>
ruser0_2_.longsCount as col_3_0_, <br>
ruser0_2_.datesCount as col_4_0_, <br>
ruser0_2_.referencesCount as col_5_0_, <br>
ruser0_2_.polysCount as col_6_0_, <br>
ruser0_2_.booleansCount as col_7_0_ <br>
from <br>
m_user ruser0_ <br>
inner join m_focus ruser0_1_ on ruser0_.oid=ruser0_1_.oid <br>
inner join m_object ruser0_2_ on ruser0_.oid=ruser0_2_.oid <br>
order by <br>
ruser0_.name_orig asc <br>
limit ?</tt><tt><br>
</tt></p>
<p>So, yes, the command is the same as in your case. <br>
</p>
<p>Please, could you do the same profiling as I did? I.e. in your
config.xml file please set up:</p>
<p><tt><configuration></tt><tt><br>
</tt><tt> <midpoint></tt><tt><br>
</tt><tt> ...</tt><tt><br>
</tt><b><font color="#cc0000"><tt>
<profilingEnabled>true</profilingEnabled></tt></font></b><tt><br>
</tt><tt> </midpoint></tt><tt><br>
</tt><tt></configuration></tt></p>
<p>And restart midPoint after the change. Then in the system
configuration (GUI: System -> Profiling) set the profiling:</p>
<p><img src="cid:part1.EDEB0394.06F63FFB@evolveum.com" alt=""></p>
<p>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 <b>"...model.impl.controller.ModelController->searchObjects</b>").</p>
<p>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.<br>
</p>
<p>Best regards,<br>
</p>
<pre class="moz-signature" cols="72">Pavol Mederly
Software developer
evolveum.com
</pre>
<div class="moz-cite-prefix">On 28.07.2018 16:51, Pavol Mederly
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:28be8950-03d0-6823-ec32-49544b0eefdf@evolveum.com">Andrew,
<br>
<br>
now I see I am going to have a look at it anyhow:
<a class="moz-txt-link-freetext" href="https://jira.evolveum.com/browse/MID-4776">https://jira.evolveum.com/browse/MID-4776</a>. I will let you know.
<br>
<br>
Pavol Mederly
<br>
Software developer
<br>
evolveum.com
<br>
<br>
On 28.07.2018 10:39, Pavol Mederly wrote:
<br>
<blockquote type="cite">Hello Andrew,
<br>
<br>
how long does it take to display the user list (in GUI) in your
case?
<br>
<br>
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.)
<br>
<br>
As for the columns, please have a look at "Custom columns
configuration" in
<a class="moz-txt-link-freetext" href="https://wiki.evolveum.com/display/midPoint/Admin+GUI+Configuration">https://wiki.evolveum.com/display/midPoint/Admin+GUI+Configuration</a>.
<br>
<br>
Hope this helps,
<br>
<br>
Pavol Mederly
<br>
Software developer
<br>
evolveum.com
<br>
<br>
On 27.07.2018 18:03, Andrew Morgan wrote:
<br>
<blockquote type="cite">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:
<br>
<br>
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;
<br>
+------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
<br>
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows |
Extra |
<br>
+------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
<br>
| 1 | SIMPLE | ruser0_ | index | PRIMARY |
uc_user_name | 768 | NULL | 100839 |
Using index; Using temporary; Using filesort |
<br>
| 1 | SIMPLE | ruser0_1_ | eq_ref | PRIMARY |
PRIMARY | 110 | midpointdev.ruser0_.oid | 1 |
Using index |
<br>
| 1 | SIMPLE | ruser0_2_ | eq_ref | PRIMARY |
PRIMARY | 110 | midpointdev.ruser0_.oid | 1
| |
<br>
+------+-------------+-----------+--------+---------------+--------------+---------+-------------------------+--------+----------------------------------------------+
<br>
<br>
Without the aliases, it is a little easier to read:
<br>
<br>
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;
<br>
+------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+
<br>
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows |
Extra |
<br>
+------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+
<br>
| 1 | SIMPLE | m_user | index | PRIMARY |
uc_user_name | 768 | NULL | 100839 |
Using index; Using temporary; Using filesort |
<br>
| 1 | SIMPLE | m_focus | eq_ref | PRIMARY |
PRIMARY | 110 | midpointdev.m_user.oid | 1 |
Using index |
<br>
| 1 | SIMPLE | m_object | eq_ref | PRIMARY |
PRIMARY | 110 | midpointdev.m_user.oid | 1
| |
<br>
+------+-------------+----------+--------+---------------+--------------+---------+------------------------+--------+----------------------------------------------+
<br>
<br>
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?)
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
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?
<br>
<br>
Thanks,
<br>
<br>
Andy Morgan
<br>
Systems Administrator, Identity & Access Management
<br>
Information Services | Oregon State University
<br>
541-737-8877 | is.oregonstate.edu
<br>
_______________________________________________
<br>
midPoint mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
midPoint mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
midPoint mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
</blockquote>
<br>
</body>
</html>