serarch optimization question
We have the sun one directory server 5.2.
On eof our searches takes almost 8 minutes, which Im trying to improve.
We have about 1 million records in teh server, and the search fetches 25000 of them.
When this search is started, the CPU on teh server goes up to 45%.
Also in the errors log, I can see the line 'search is not indexed'
But all teh attributes involved are indexed.
ldapsearch -h host -p 390 -D "cn=Directory Manager" -w xxx
-b ou=nasclients,o=bst,dc=bellsouth,dc=com '(bstdgl=*)' bstdgl
I even tried VLV indexing ( Browsing index ) without any result.
Is there a way to find out what is not indexed.
Also in general which are the attributes that should be indexed.
1. All teh ones appearing in the filter ?
2. Or only the ones appearing in teh basename ?
Thanks
--sony
# 1
By the way for the above query, the results are in the form,
bstdlg=XXX,circuitid=YYY,ou=nasclients,o=bst,dc=bellsouth,dc=com
We have each equality, presence, substring turned on for attributes bstdlg and circuitid.
I thought this is all the indexing that is required.
--sony
# 2
What is your allidsthreshold set to?If it's the default (4000), a search that returns 25,000 results will be unindexed.Look here (near the bottom of the page) for info about the allidsthreshold: http://docs.sun.com/source/816-6697-10/indexing.html
# 3
That was something I did not know.
However that did not make any difference.
Also is it required to reinitialize the data after changing indexes ?
I thought just reindexing is all that is required.
Is it possible that teh data will become "defragmented" over time ?
which will be fixed if data is reinitialized ( db2ldif , ldif2db )
Thanks
--sony
# 4
This is the exact error message I see in the error file when teh search is launched."[28/Apr/2007:02:28:34 -0400] - WARNING<20805> - Backend Database - conn=12 op=1 msgId=2 - search is not indexed"--sony
# 5
> Also is it required to reinitialize the data after changing indexes ?
No,
But it is required when changing the allidsthreshold.
Did you see this at http://docs.sun.com/source/816-6697-10/indexing.html :
Change the all IDs threshold as follows. Note that service is interrupted on the Directory Server instance undergoing the change.
1. Stop the Directory Server instance in question.
2. Export all directory databases to LDIF.
Refer to the Sun ONE Directory Server Administration Guide for details.
3. Carefully adjust the value of the nsslapd-allidsthreshold attribute in ServerRoot/slapd-ServerID/config/dse.ldif.
4. Reinitialize all directory databases from LDIF.
Refer to the Sun ONE Directory Server Administration Guide for details.
...
# 6
Forgot to answer this:
> Also in general which are the attributes that should be indexed.
> 1. All teh ones appearing in the filter ?
> 2. Or only the ones appearing in teh basename ?
It's just those in the filter, there's no benefit to indexing attributes in the basename. However, they often may be indexed anyways. 'cn', for example, is indexed by default, and is a common DN component attribute.
# 7
Thank you very much. That solved the problem.
I wrote a script to fix allidsthreshold.
Posting it here, since somebody might find it useful
Thanks again
--sony
#!/bin/ksh
#########################################################3
#Usage : fixAllIdsThreshold.ksh <pwd> [ssl pwd]
#-Make sure that teh script is execd from the instance directory
#Eg : - cd /opt/sun/mps/master/slapd-birmingham
#/my/scripts/dir/fixAllIdsThreshold.ksh mypasswd mysslpwd
#
#-Running this script will result in the ldap instance being down for approx 3 hours
#( or less depending on the number of entries )
#-If the LDAP instance has SSL enabled, this script requires a second argumento
#to be used as the PIN for Internal Token
#-Ldap instance should be running when this script is run
#-Ldap instance will be restarted if teh script completes execution successfully.
#
#Author: Sony Antony
#
#########################################################3
PASSWD=$1
#In case you have SSL
PIN_4_SSL_TOKEN=$2
HOSTNAME=$(hostname)
PORT=390
USER_NAME="cn=Directory Manager"
NEW_ALLIDSTHRESHOLD_VALUE=4000
LDIF_STORE_DIR=/tmp
LDIF_STORE_FILE=${LDIF_STORE_DIR}/userRoot.$$.ldif
../shared/bin/ldapmodify -h ${HOSTNAME} -p ${PORT} -D "${USER_NAME}" -w ${PASSWD} <<END_HERE
dn: cn=config,cn=ldbm database,cn=plugins,cn=config
changetype: modify
replace: nsslapd-allidsthreshold
nsslapd-allidsthreshold: ${NEW_ALLIDSTHRESHOLD_VALUE}
END_HERE
./stop-slapd
./db2ldif -n userRoot -U -a ${LDIF_STORE_FILE}
./ldif2db -n userRoot -i ${LDIF_STORE_FILE}
./start-slapd><<END_HERE
${PIN_4_SSL_TOKEN}
END_HERE
rm ${LDIF_STORE_FILE}
# 8
Oops sorry I posted the script with the value 4000.
Here is teh one with the value being set as 75000
--sony
#!/bin/ksh
#########################################################3
# Usage : fixAllIdsThreshold.ksh <pwd> [ssl pwd]
# -Make sure that teh script is execd from the instance directory
# Eg : - cd /opt/sun/mps/master/slapd-birmingham
# /my/scripts/dir/fixAllIdsThreshold.ksh mypasswd mysslpwd
#
# -Running this script will result in the ldap instance being down for approx 3 hours
# ( or less depending on the number of entries )
# -If the LDAP instance has SSL enabled, this script requires a second argumento
# to be used as the PIN for Internal Token
# -Ldap instance should be running when this script is run
# -Ldap instance will be restarted if teh script completes execution successfully.
#
# Author: Sony Antony
#
#########################################################3
PASSWD=$1
#In case you have SSL
PIN_4_SSL_TOKEN=$2
HOSTNAME=$(hostname)
PORT=390
USER_NAME="cn=Directory Manager"
NEW_ALLIDSTHRESHOLD_VALUE=75000
LDIF_STORE_DIR=/tmp
LDIF_STORE_FILE=${LDIF_STORE_DIR}/userRoot.$$.ldif
../shared/bin/ldapmodify -h ${HOSTNAME} -p ${PORT} -D "${USER_NAME}" -w ${PASSWD} <<END_HERE
dn: cn=config,cn=ldbm database,cn=plugins,cn=config
changetype: modify
replace: nsslapd-allidsthreshold
nsslapd-allidsthreshold: ${NEW_ALLIDSTHRESHOLD_VALUE}
END_HERE
./stop-slapd
./db2ldif -n userRoot -U -a ${LDIF_STORE_FILE}
./ldif2db -n userRoot -i ${LDIF_STORE_FILE}
./start-slapd><<END_HERE
${PIN_4_SSL_TOKEN}
END_HERE
rm ${LDIF_STORE_FILE}
