Univention Bugzilla – Bug 48945
search filter uid=*foo* is very slow in certain circumstances
Last modified: 2022-11-10 15:51: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.
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.
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.
Workaround for comment 2 (works only in UCS > 5): ucr set directory/manager/web/modules/users/user/properties/employeeNumber/include_in_default_search=false
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?
(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
*** Bug 53065 has been marked as a duplicate of this bug. ***
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'`.
moved the employeeNumber issue into Bug #55412.