<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>PS: and yes, it is quite probable there will be 3.7.3 release.
But it is not clear exactly when it will be. <br>
</p>
<pre class="moz-signature" cols="72">Pavol Mederly
Software developer
evolveum.com
</pre>
<div class="moz-cite-prefix">On 31.07.2018 19:46, Pavol Mederly
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:a022ab97-eed6-0a90-226b-b5bf2f63ebd6@evolveum.com">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<p>Andy,</p>
<p>yes, you need to build from midPoint sources as described <a
moz-do-not-send="true"
href="https://wiki.evolveum.com/display/midPoint/Building+MidPoint+From+Source+Code">here</a>.
You can clone the midPoint repository like this:</p>
<div class="container" title="Hint: double-click to select code">
<div class="line number1 index0 alt2">
<div class="container" title="Hint: double-click to select
code">
<div class="line number1 index0 alt2"><code class="bash
plain">git clone -b support-3.7 https:</code><code
class="bash plain">//github</code><code class="bash
plain">.com</code><code class="bash plain">/Evolveum/midpoint</code><code
class="bash plain">.git</code></div>
</div>
<code class="bash plain"></code></div>
</div>
<p>Then you can build as indicated on the wiki page. I suggest
building with tests turned off, it is much faster.<br>
</p>
<p>Hope this helps,<br>
</p>
<pre class="moz-signature" cols="72">Pavol Mederly
Software developer
evolveum.com
</pre>
<div class="moz-cite-prefix">On 31.07.2018 19:19, Andrew Morgan
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:alpine.DEB.2.11.1807311018530.17038@shell.onid.oregonstate.edu">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? <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>
On Tue, 31 Jul 2018, Pavol Mederly wrote: <br>
<br>
<blockquote type="cite">Hello, <br>
<br>
the fix is implemented on support-3.7 branch. Please do not
forget to apply DB migration scripts. <br>
<br>
Best regards, <br>
<br>
Pavol Mederly <br>
Software developer <br>
evolveum.com <br>
<br>
<br>
On 30.07.2018 19:24, Pavol Mederly wrote: <br>
<br>
Andrew, <br>
<br>
I think I got it. It seems to be a difference between 3.7 and
3.8, indeed. <br>
<br>
The query in 3.7 is like this: <br>
<br>
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_2_.name_orig asc <br>
limit ? <br>
<br>
and in 3.8 it is: <br>
<br>
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 20 <br>
<br>
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. <br>
<br>
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. <br>
<br>
Best regards, <br>
<br>
Pavol Mederly <br>
Software developer <br>
evolveum.com <br>
<br>
<br>
On 30.07.2018 17:58, Andrew Morgan wrote: <br>
Pavol, <br>
<br>
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. <br>
<br>
When I run the same query in the mysql client, is says, "20
rows in set (13.93 sec)". <br>
<br>
Here is the EXPLAIN for it again: <br>
<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 | 100970 |
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>
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: <br>
<br>
[mysqld] <br>
innodb_buffer_pool_size = 2G <br>
max_allowed_packet = 256M <br>
<br>
<br>
Thanks for helping us investigate! <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>
On Mon, 30 Jul 2018, Pavol Mederly wrote: <br>
<br>
Andrew, Petr, <br>
<br>
my observations on my developer machine running midPoint
(master) and MySQL 5.7 locally are here: <br>
<br>
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: <br>
<br>
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:
6.079 ms <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: 21.325 ms <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>
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>
<br>
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 ? <br>
<br>
So, yes, the command is the same as in your case. <br>
<br>
Please, could you do the same profiling as I did? I.e. in your
config.xml file please set up: <br>
<br>
<configuration> <br>
<midpoint> <br>
... <br>
<profilingEnabled>true</profilingEnabled> <br>
</midpoint> <br>
</configuration> <br>
<br>
And restart midPoint after the change. Then in the system
configuration (GUI: System -> Profiling) set the profiling:
<br>
<br>
[<a class="moz-txt-link-freetext"
href="cid:part1.EDEB0394.06F63FFB@evolveum.com"
moz-do-not-send="true">cid:part1.EDEB0394.06F63FFB@evolveum.com</a>]
<br>
<br>
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").
<br>
<br>
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>
<br>
Best regards, <br>
<br>
Pavol Mederly <br>
Software developer <br>
evolveum.com <br>
<br>
<br>
On 28.07.2018 16:51, Pavol Mederly wrote: <br>
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"
moz-do-not-send="true">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>
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"
moz-do-not-send="true">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>
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"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a>
<br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
<br>
_______________________________________________ <br>
midPoint mailing list <br>
<a class="moz-txt-link-abbreviated"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a>
<br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
<br>
_______________________________________________ <br>
midPoint mailing list <br>
<a class="moz-txt-link-abbreviated"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a>
<br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________ <br>
midPoint mailing list <br>
<a class="moz-txt-link-abbreviated"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a>
<br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________ <br>
midPoint mailing list <br>
<a class="moz-txt-link-abbreviated"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><a
class="moz-txt-link-rfc2396E"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true"><mailto:midPoint@lists.evolveum.com></a>
<br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
<br>
<br>
<br>
</blockquote>
_______________________________________________ <br>
midPoint mailing list <br>
<a class="moz-txt-link-abbreviated"
href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a> <br>
<a class="moz-txt-link-freetext"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
moz-do-not-send="true">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
<br>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
midPoint mailing list
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
</body>
</html>