[Date Prev][Date Next] [Chronological] [Thread] [Top]

Re: back-sql performance problem workaround

Brad Midgley wrote:

Hi

I think I found a fix for this. The access control was extremely locked down, but that seems to cause trouble with back-sql. The original problem:

When I do a straightforward search on openldap using back-sql, a select is run that pulls every entry from ldap_entries. That's 40,000 entries for this database. It means getting a user's *own* entries after authenticating can take another 5-15 seconds! I can understand other kinds of searches taking a long time, but if we already know the name of the object it should be quick.

bmidgley@jabbed:~$ ldapsearch -H ldap://gambit.uen.org -D uid=bmidgley,dc=my,dc=uen,dc=org -x -W -d 256 -z 10 "(uid=bmidgley)"
request 1 done
# extended LDIF
#
# LDAPv3
# base <> with scope sub
# filter: (uid=bmidgley)
# requesting: ALL
#

# bmidgley, my.uen.org
dn: uid=bmidgley,dc=my,dc=uen,dc=org
objectClass: inetOrgPerson
objectClass: posixAccount
cn: Brad Midgley
ou: Alpine School District
ou: Utah Education Network
sn: Midgley
uid: bmidgley
mail: bmidgley@uen.org
gidNumber: 74032
givenName: Brad
uidNumber: 74032
homeDirectory: /home/bmidgley
employeeNumber: 74032
request 2 done

# search result
search: 2
result: 4 Size limit exceeded

# numResponses: 2
# numEntries: 1
bmidgley@jabbed:~$

The complete log looks like: http://www.xmission.com/~bmidgley/openldap/slapd-selectall.log

The bottom line is it looks like this is being executed:

SELECT DISTINCT ldap_entries.id,utahlink..educator.teacher_id,('posixAccount') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,utahlink..educator WHERE utahlink..educator.teacher_id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND upper(ldap_entries.dn) LIKE "%DC=MY,DC=UEN,DC=ORG" and 1=1
Every entry in our database matches the dn test.

Solution: changing access from

access to *
        by dn="cn=Manager,dc=my,dc=uen,dc=org" write
        by self read
        by * none

to

access to *
        by dn="cn=Manager,dc=my,dc=uen,dc=org" write
        by self read
        by * read

Caused the select to change to:

SELECT DISTINCT ldap_entries.id,utahlink..educator.teacher_id,('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,utahlink..educator WHERE utahlink..educator.teacher_id=ldap_entries.keyval AND ldap_entries.oc_map_id=convert(integer,?) AND upper(ldap_entries.dn) LIKE ? AND (upper(username)='BMIDGLEY')

Which allows the db to produce only one record instead of all of them.

There should be something else, since in back-sql's search (or search related)
code there isn't any single interaction with ACLs; it all occurs in the frontend.

I just checked my own installation (from HEAD, but should be fine), with data
from sql-test000, and I don't see any difference in authenticated queries with
or without the "access" directive you have indicated. In fact, the two different
selects you indicate respectively seem to refer to a "(objectClass=*)"

> Constructed query: SELECT DISTINCT ldap_entries.id,persons.id,text('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,persons WHERE persons.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND upper(ldap_entries.dn) LIKE ? AND 1=1
> id: '1'
> (sub)dn: "%DC=EXAMPLE,DC=COM"

and to a "(uid=exact)"

> Constructed query: SELECT DISTINCT ldap_entries.id,persons.id,text('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,persons WHERE persons.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND upper(ldap_entries.dn) LIKE ? AND (upper(persons.surname)='KOVALEV')
> id: '1'
> (sub)dn: "%DC=EXAMPLE,DC=COM"

search. Unfortunately, in your log you seem to produce only the second case,
so no comparison is possible. I wonder if you could produce the entire slapd.conf.

p.




   SysNet - via Dossi,8 27100 Pavia Tel: +390382573859 Fax: +390382476497