<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Andrew,</p>
<p>I think I got it. It seems to be a difference between 3.7 and
3.8, indeed.</p>
<p>The query in 3.7 is like this:</p>
<p><tt>select </tt><tt><br>
</tt><tt> ruser0_.oid as col_0_0_, </tt><tt><br>
</tt><tt> ruser0_2_.fullObject as col_1_0_, </tt><tt><br>
</tt><tt> ruser0_2_.stringsCount as col_2_0_, </tt><tt><br>
</tt><tt> ruser0_2_.longsCount as col_3_0_, </tt><tt><br>
</tt><tt> ruser0_2_.datesCount as col_4_0_, </tt><tt><br>
</tt><tt> ruser0_2_.referencesCount as col_5_0_, </tt><tt><br>
</tt><tt> ruser0_2_.polysCount as col_6_0_, </tt><tt><br>
</tt><tt> ruser0_2_.booleansCount as col_7_0_ </tt><tt><br>
</tt><tt>from </tt><tt><br>
</tt><tt> m_user ruser0_ </tt><tt><br>
</tt><tt> inner join m_focus ruser0_1_ on
ruser0_.oid=ruser0_1_.oid </tt><tt><br>
</tt><tt> inner join m_object ruser0_2_ on
ruser0_.oid=ruser0_2_.oid </tt><tt><br>
</tt><tt><font color="#cc0000"><b>order by </b><b><br>
</b><b> ruser0_2_.name_orig asc </b><b><br>
</b></font></tt><tt>limit ?</tt><tt><br>
</tt></p>
<p>and in 3.8 it is:</p>
<p><tt>select</tt><tt><br>
</tt><tt> ruser0_.oid as col_0_0_,</tt><tt><br>
</tt><tt> ruser0_2_.fullObject as col_1_0_,</tt><tt><br>
</tt><tt> ruser0_2_.stringsCount as col_2_0_,</tt><tt><br>
</tt><tt> ruser0_2_.longsCount as col_3_0_,</tt><tt><br>
</tt><tt> ruser0_2_.datesCount as col_4_0_,</tt><tt><br>
</tt><tt> ruser0_2_.referencesCount as col_5_0_,</tt><tt><br>
</tt><tt> ruser0_2_.polysCount as col_6_0_,</tt><tt><br>
</tt><tt> ruser0_2_.booleansCount as col_7_0_</tt><tt><br>
</tt><tt>from</tt><tt><br>
</tt><tt> m_user ruser0_</tt><tt><br>
</tt><tt> inner join m_focus ruser0_1_ on
ruser0_.oid=ruser0_1_.oid</tt><tt><br>
</tt><tt> inner join m_object ruser0_2_ on
ruser0_.oid=ruser0_2_.oid</tt><tt><br>
</tt><font color="#cc0000"><b><tt>order by</tt></b><b><tt><br>
</tt></b><b><tt> ruser0_.name_orig asc</tt></b><b><tt><br>
</tt></b></font><tt>limit 20</tt><br>
</p>
<p>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.</p>
<p>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>
</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 30.07.2018 17:58, Andrew Morgan
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:alpine.DEB.2.11.1807300849551.17038@shell.onid.oregonstate.edu">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>
<blockquote type="cite">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">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">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">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">midPoint@lists.evolveum.com</a><a class="moz-txt-link-rfc2396E" href="mailto:midPoint@lists.evolveum.com"><mailto: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>
<br>
_______________________________________________
<br>
midPoint mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><a class="moz-txt-link-rfc2396E" href="mailto:midPoint@lists.evolveum.com"><mailto: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>
<br>
_______________________________________________
<br>
midPoint mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><a class="moz-txt-link-rfc2396E" href="mailto:midPoint@lists.evolveum.com"><mailto: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>
<br>
<br>
</blockquote>
<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>