[midPoint] Users list is slow with large numbers of users

[Andrew Morgan]

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