Bug 48945 - search filter uid=*foo* is very slow in certain circumstances
search filter uid=*foo* is very slow in certain circumstances
Status: REOPENED
Product: UCS
Classification: Unclassified
Component: UMC - Domain management (Generic)
UCS 5.0
Other Linux
: P5 normal (vote)
: ---
Assigned To: UMC maintainers
UMC maintainers
:
: 53065 (view as bug list)
Depends on:
Blocks: 55412
  Show dependency treegraph
 
Reported: 2019-03-11 10:38 CET by Florian Best
Modified: 2022-11-10 15:51 CET (History)
5 users (show)

See Also:
What kind of report is it?: Development Internal
What type of bug is this?: ---
Who will be affected by this bug?: ---
How will those affected feel about the bug?: ---
User Pain:
Enterprise Customer affected?: Yes
School Customer affected?: Yes
ISV affected?:
Waiting Support:
Flags outvoted (downgraded) after PO Review:
Ticket number:
Bug group (optional): UCS Performance, Usability
Max CVSS v3 score:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Florian Best univentionstaff 2019-03-11 10:38:21 CET
During the UCS 4.4 product tests we experienced massive performance problems for a simple search in any UMC UDM module. UMC automatically creates search filters like *foo* if you search for "foo".

Administrator doesn't have problems for this, but e.g. users which are User Password Admins.

# time univention-ldapsearch -LLL -D uid=foo,cn=users,dc=mydomain,dc=intranet -w univention  '(&(objectClass=posixAccount)(objectClass=shadowAccount)(objectClass=sambaSamAccount)(objectClass=krb5Principal)(objectClass=krb5KDCEntry)(!(uidNumber=0))(!(uid=*$))(!(univentionObjectFlag=functional))(&(!(univentionObjectFlag=hidden))(|(uid=*foo*)(description=*foo*)(givenName=*foo*)(sn=*foo*)(employeeNumber=*foo*)(mailPrimaryAddress=*foo*))))' dn

dn: uid=foo,cn=users,dc=mydomain,dc=intranet
dn: uid=foo2,cn=users,dc=mydomain,dc=intranet
dn: uid=foo3,cn=users,dc=mydomain,dc=intranet
dn: uid=foo4,cn=users,dc=mydomain,dc=intranet
dn: uid=foo5,cn=users,dc=mydomain,dc=intranet
real    2m41,527s
user    0m0,196s
sys     0m0,024s

A search for uid=*foo or a search for uid=foo* is very fast.
Comment 1 Florian Best univentionstaff 2022-08-26 14:13:46 CEST
The problem could also be missing indicies.
E.g. searching in users/user by default searches in multiple properties: also in `employeeNumber` with a search filter like (|(employeeNumber=foo)(employeeNumber=*foo*)). But employeeNumber is only indexed for subsearches, not equality or presence.

management/univention-ldap/scripts/ldap_setup_index:
260 »   'eq': UDM_PROP_ATTRS | set((
…
313 »   )),
314 »   'pres': UDM_PROP_ATTRS | set((
…
350 »   )), 
351 »   'sub': set((
…
359 »   »   'employeeNumber',
…
379 »   )),

At least this happened in a larger customer environment.
Comment 2 Florian Best univentionstaff 2022-08-26 14:17:36 CEST
git:f3acbe31b9c9248672c8f65f651731a100340f7f (Add attributes to be searched in by default) Bug #24341
→ since then employeeNumber is searched by default

When removing employeeNumber from the defaultsearch performance improved greatly.
Comment 4 Florian Best univentionstaff 2022-09-01 12:30:39 CEST
Workaround for comment 2 (works only in UCS > 5):
ucr set directory/manager/web/modules/users/user/properties/employeeNumber/include_in_default_search=false
Comment 5 Daniel Tröder univentionstaff 2022-11-10 09:51:02 CET
This means a performance win for almost no effort at all. We should absolutely use this opportunity!
IMHO we should not add the attribute to the index (there are already a lot of attr in there, making the writes slower), but remove the attribute from the search.
My guess is, that there are few customers that use "employeeNumber" in their searches in the UMC. If we change the default, and they experience a performance loss, they can enable the UCRV to include it in the search.
Can this be discussed with PM?
Comment 6 Florian Best univentionstaff 2022-11-10 12:19:07 CET
(In reply to Daniel Tröder from comment #5)
> Can this be discussed with PM?
Dirk approved.

From the URL field:
https://stackoverflow.com/questions/9564120/using-wildcards-in-ldap-search-filters-queries

MR:
https://git.knut.univention.de/univention/ucs/-/merge_requests/563
Comment 7 Florian Best univentionstaff 2022-11-10 12:21:01 CET
*** Bug 53065 has been marked as a duplicate of this bug. ***
Comment 8 Florian Best univentionstaff 2022-11-10 14:29:56 CET
to reproduce this:
(for german background information see Bug #18685)
> apt install univention-admingrp-user-passwordreset
Set up an environment with a few thousand users.
Create a user with permissions:
> udm users/user create --position "cn=users,$(ucr get ldap/base)" --set username=mypasswordadmin --set lastname=foo --set password=univention --append groups="cn=User Password Admins,cn=groups,$(ucr get ldap/base)"
> tail -f /var/log/syslog | grep 'not indexed' &
> time curl -X POST -H 'Content-Type: application/json' -d '{"options": {"objectProperty": "None", "objectPropertyValue": "foo"}, "flavor": "users/user"}' http://mypasswordadmin:univention@localhost/univention/command/udm/query
> time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))(|(givenName=*foo*)(givenName=foo))(|(sn=*foo*)(sn=foo))(|(description=*foo*)(description=foo))(|(mailPrimaryAddress=*foo*)(mailPrimaryAddress=foo))))'

Just removing "employeeNumber" from the search is an enhancement and also fixed the performance problem in one customer environment but this is not enough on my VM with 50.024 users:

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))(|(givenName=*foo*)(givenName=foo))(|(sn=*foo*)(sn=foo))(|(description=*foo*)(description=foo))(|(mailPrimaryAddress=*foo*)(mailPrimaryAddress=foo))))'

real    4m2,086s
user    0m0,987s
sys     0m0,118s
# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))(|(givenName=*foo*)(givenName=foo))(|(sn=*foo*)(sn=foo))(|(description=*foo*)(description=foo))(|(employeeNumber=*foo*)(employeeNumber=foo))(|(mailPrimaryAddress=*foo*)(mailPrimaryAddress=foo))))'

real    4m52,653s
user    0m1,019s
sys     0m0,072s

→ 50 seconds faster

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))(|(givenName=*foo*)(givenName=foo))(|(sn=*foo*)(sn=foo))(|(description=*foo*)(description=foo))(|(mailPrimaryAddress=*foo*)(mailPrimaryAddress=foo)))))' 1.1 > /dev/null

real    2m10,822s
user    0m1,039s
sys     0m0,110s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))(|(givenName=*foo*)(givenName=foo))(|(sn=*foo*)(sn=foo))(|(description=*foo*)(description=foo))(|(employeeNumber=*foo*)(employeeNumber=foo))(|(mailPrimaryAddress=*foo*)(mailPrimaryAddress=foo)))))' 1.1 > /dev/null 

real    2m23,636s
user    0m1,013s
sys     0m0,110s

→ 12 seconds faster

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=foo))(|(givenName=foo))(|(sn=foo))(|(description=foo))(|(mailPrimaryAddress=foo))))'

real    0m1,124s
user    0m1,040s
sys     0m0,076s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=*foo)(uid=foo))(|(givenName=*foo)(givenName=foo))(|(sn=*foo)(sn=foo))(|(description=*foo)(description=foo))(|(mailPrimaryAddress=*foo)(mailPrimaryAddress=foo))))'

real    0m1,096s
user    0m1,016s
sys     0m0,079s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=foo*)(uid=foo))(|(givenName=foo*)(givenName=foo))(|(sn=foo*)(sn=foo))(|(description=foo*)(description=foo))(|(mailPrimaryAddress=foo*)(mailPrimaryAddress=foo))))'

real    0m1,084s
user    0m0,955s
sys     0m0,123s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=foo*)(uid=foo)(uid=*foo))(|(givenName=foo*)(givenName=foo)(givenName=*foo))(|(sn=foo*)(sn=foo)(sn=*foo))(|(description=foo*)(description=foo)(description=*foo))(|(mailPrimaryAddress=foo*)(mailPrimaryAddress=foo)(mailPrimaryAddress=*foo))))'

real    0m1,161s
user    0m1,029s
sys     0m0,124s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(!(objectFlag=hidden))(|(|(uid=*foo*)(uid=foo))))'

real    0m48,132s
user    0m0,987s
sys     0m0,086s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=foo))' 1.1 > /dev/null 

real    0m1,160s
user    0m1,058s
sys     0m0,090s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=foo*))' 1.1 > /dev/null 

real    0m1,195s
user    0m1,065s
sys     0m0,122s
# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=*foo))' 1.1 > /dev/null 

real    0m1,144s
user    0m1,064s
sys     0m0,075s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjec
tFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ])))))' 1.1 > /dev/null
real    0m56,312s
user    0m2,537s
sys     0m1,195s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=*foo*))' 1.1 > /dev/null

real    0m53,159s
user    0m1,051s
sys     0m0,060s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(objectClass=person)(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functio
nal)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=*foo*))' 1.1 > /dev/null

real    1m3,061s
user    0m1,025s
sys     0m0,112s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(objectClass=person)(uid=*foo*))' 1.1 > /dev/null

real    1m1,643s
user    0m1,038s
sys     0m0,082s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=*foo*)(objectClass=person))' 1.1 > /dev/null

real    0m53,342s
user    0m0,986s
sys     0m0,147s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(&(!(objectFlag=hidden))(|(|(uid=foo*)(uid=foo)(uid=*foo))(|(givenName=foo*)(givenName=foo)(givenName=*foo))(|(sn=foo*)(sn=foo)(sn=*foo))(|(description=foo*)(description=foo)(description=*foo))(|(mailPrimaryAddress=foo*)(mailPrimaryAddress=foo)(mailPrimaryAddress=*foo)))))' 1.1 > /dev/null 

real    0m1,097s
user    0m0,984s
sys     0m0,105s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=f*o*o))' 1.1 > /dev/null 

real    0m52,093s
user    0m1,009s
sys     0m0,121s

# time univention-ldapsearch -D "uid=mypasswordadmin,cn=users,$(ucr get ldap/base)" -w univention -LLL '(&(univentionObjectType=users/user)(!(|(univentionObjectFlag=temporary)(univentionObjectFlag=functional)(univentionObjectFlag=hidden)))(!(&(shadowExpire=1)(krb5KDCFlags:1.2.840.113556.1.4.803:=128)(|(sambaAcctFlags=[ud ])(sambaAcctFlags=[uld ]))))(uid=f*oo))' 1.1 > /dev/null 

real    0m1,189s
user    0m1,028s
sys     0m0,112s

# free -m
              total        used        free      shared  buff/cache   available
Mem:          32169        6063       13956          28       12149       25640
Swap:           979           0         979

So the problem are the two wildcards in the filter.
UMC adds them automatically to make substring searches.
This can be prevented via setting `ucr set 'directory/manager/web/auto_substring_search=false'`.
Comment 9 Florian Best univentionstaff 2022-11-10 15:51:21 CET
moved the employeeNumber issue into Bug #55412.