Improve Oracle Unified Directory 11gR2 Search Performance with Index Entry Limit

Introduction

I am always looking for great tips that give big values; this one is no exception. This article is to help you understand how to tweak the index called “Index Entry Limit” to reap some dramatic ldapsearch performance improvements. I explain what this index is about, some of my own test results, how to determine the correct value, and finally how to make the index change to your OUD instance. This will be a tip you will definitely want to add to your OUD Ninja black bag.

What is Index Entry Limit?

At the time of this publishing the latest official Oracle® Fusion Middleware Administrator's Guide for Oracle Unified Directory 11g Release 2 (11.1.2) Part Number E22648-02 documentation says in section 6.3 Index Entry Limit as follows:

“The index entry limit is a configuration limit that can be used to control the maximum number of entries that is allowed to match any given index key (that is, the maximum size of an ID list). This provides a mechanism for limiting the performance impact for maintaining index keys that match a large percentage of the entries in the server. In cases where large ID lists might be required, performing an unindexed search can often be faster than one that is indexed.”

So what does this mean? Basically it is an index, which OUD uses to maintain a key between the index attribute and entries. It is a little different than the standard LDAP indexes you add for an attribute like equality, presence, substring, etc. The Index Entry Limit value controls the maximum number of entries kept in the OUD index record when it looks up data. The default value for this limit is 4000 and if you have more records returned by some ldapsearch that exceeds this value, then increasing it can reap some pretty outstanding improvements in performance. Before you jump ahead and simply raise this value to some ridiculous number, read on to see what I discovered and how to determine the right balance ---- the sweet spot.

My Test Results using the Index Entry Limit Tweak

It is important to run various tests to determine the impact of making changes to the Index Entry Limit value, so to start I want to give explain what I used as my environment. I setup an OUD 11.1.2.3.0 instance populated with 1 million user entries with some dynamic groups. Each entry had several attributes populated with data, so for my example test I leveraged the “st” attribute; used to define the US state a person lives in. Within the 1 million user population there was an average fewer than 18,000 users for each “st” or state. For example NC, or North Carolina, had the largest population of 17,499 users. This is important to know; more on this later.

For my first test case I ran a simple ldapsearch with a filter “(st=<st value>)” against 5 different states, TX, FL, RI, VA, and AK. I created a simple script that iterated the search through each state twice. I collected a summary of the time it took to complete for search, and increased the Index Entry Limit each time and so on. The following table is a summary of the results.

Index Entry Limit value	4,000 (base)	10,000	20,000	40,000	60,000
Search Time in Milliseconds	3923	3077	1401	1571	1617
Percentage Improvement	100%	127%	280%	250%	243%

Interestingly you can see at 20,000 I got the faster ldapsearch. I ran a similar test against dynamic groups that also used a filter on states. The following table is a summary of the results, and similar to the first test I found that 20,000 gave the best results.

Index Entry Limit value	4,000 (base)	10,000	20,000	40,000	60,000
Search Time in Milliseconds	5466	4876	1434	1511	1441
Percentage Improvement	100%	112%	381%	362%	379%

So I learned a couple things, 1) increasing the Index Entry Limit can have dramatic performance improvements, and 2) there is an optimal value to get the best results so simply making the value a very high number does not yield even better performance and can in fact have a negative impact.

To add more confirmation I ran large load tests against my OUD instance doing a similar adjustment of the Index Entry Limit value, and the results showed the same pattern. That is increasing the value to what I call the Sweet Spot amount led to the biggest gain.  So how do you determine this so called Sweet Spot value?

The Sweet Spot

In the previous section I determined that there is an optimal value for the Index Entry Limit to get the most performance, and in my test case I determined that value to be 20,000. However your value will most likely be completely different.

I already mentioned earlier that OUD uses the Index Entry Limit value as a measure to how many entries it keeps in its index entry record when it looks up the data. In my case the highest common denominator of the “st” attribute population was NC, North Carolina, which had a population total of 17,499. So in reality the most optimum value would have bee to set the Index Entry Limit to 17,499. However there are no guarantees that the population based on the attribute “st” will be a max of 17,499 forever. Therefore I decided to add some buffer and make the value 20,000. That said, if I were to say just make the value to 100,000 to make sure I have some buffer, the problem is that this adds unnecessary indexes that OUD must track and therefore the impact becomes more negative. So there is a balance that must be kept.

Generally Directory Services data is pretty static, but the volume can change. So even though this indexing tip can reap some pretty big rewards, you must be careful about managing the number. I would even go as far to say if you think the number of the entries the filter changes dramatically, you can either monitor this change and tune periodically or decide on a balance value and hope it is not too high.   Because when you increase the Index Entry Limit, the cost is not free, it does make OUD consume more RAM.   For example in my case, when I increased the “st” Index Entry Limit from 4000 to 20,000, the demand on RAM increased around 900MB. That is nothing major, but if you are tuning for several attributes then this can add up, so for your specific tuning I recommend running tests and monitoring the disk, RAM, CPU changes so that you do not exhaust limited resources.. The performance gains are amazing, but like a super hero, you must treat your new super powers with great respect.

Determining the Index Entry Limit

Sorry for the long previous section, but I really wanted to expand on as much as I could without creating an entire novel to make sure you understood the ramifications of playing with this really great tuning tip, but now to show you how to determine this value. There is a MOS (My Oracle Support) Doc ID 1526683.1 article that explains a way to determine the entries that are associated with this index by using a special operational attribute “debugsearchindex.   To illustrate let’s say you have a Dynamic group based on an LDAP filter “(st=NC)”. If you include the parameter “debugsearchindex” at the end of the command, an output will result that will help determine the optimum value to use.

Example LDAP Search command:

ldapsearch -h localhost –p 1389 –D cn=Directory\ Manager –w <passwd> \

     -x –LLL –b dc=example,dc=com “(st=NC)” debugsearchindex

dn: cn=debugsearch

debugsearchindex: filter=(st=NC)[INDEX:st.equality][COUNT:17499] scope=sub[LIM

IT-EXCEEDED:1000002] final=[COUNT:17499]

Depending on what you use for a LDAP filter your result will probably be different, but the key is to run an ldapsearch with the filter your application or what ever is querying OUD and include the debugsearchindex parameter.   In my example there is a final COUNT value of 17,499, so this would be the optimum Index Entry Limit value. However, before you run off and think this is all there is too it, let me expand a bit more.

You will also need to account for any variation. For example in my case I had 52 different states (“st”), which means I had to run this same command against each state; e.g. (st=FL), (st=CA), etc. and from the list of final outputs I determined the largest final COUNT was indeed from the state NC with a value of 17,499.   Since the count of users could vary over time, I decided to go with the Index Entry Limit value of 20,000 that would provide some buffer. Typically Directory Services have fairly static value, but in this case it depended on how my population would grow over time. You will have to determine what you think would be a good balance and maybe even have to make this tuning an on going administrative activity in order to get the most performance.

TIP: You can create a script to run through all the variations I mentioned in your LDAP filter to come up with the largest value. Below I created a simple example of what you could use as a script to iterate through a list of states or what ever attribute you want to evaluate. Feel free to modify this script to your specific needs. The idea is that the last output “MAX:” tells you the highest count from all the values you go through based on the LDAP filter, which would be the value you would minimally use for the Index Entry Limit.  You can also increase the Index Entry Limit value a bit as a buffer depending on what your situation is, but increasing it to much may not have much value.

/*** Example debugsearchtest_list.txt Input File with list of values ***/
TX 
FL
RI
VA
AK

/*** Example getMaxEntryLimit.sh Script ***/

#! /bin/bash
INPUT=debugsearchtest_list.txt
OLDIFS=$IFS
IFS=","
attr="st"
ldaphost="oud1.melander.us"
ldapport="1389"
ldapbind="cn=Directory Manager"
ldapcred="Oracle123"
ldapbase="cn=Users,dc=oracle,dc=com"

[ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
max=0
while read attr_val
do
     debugcount=`ldapsearch -h $ldaphost -p $ldapport \
     -D "$ldapbind" -w "$ldapcred" -x -LLL \
     -b "$ldapbase" \
     "($attr=$attr_val)" \
     debugsearchindex | \
     grep 'final=' | sed 's/\]//g' | awk -F ":" '{print $3}'`
     echo "$attr_val - $debugcount"
     array+=($debugcount)
done < $INPUT
IFS=$OLDIFS
IFS=$'\n'
echo -ne "-------------\n"
max=`echo "${array[*]}" | sort -nr | head -n1`
echo "MAX: $max"

/*** Example Command-line Output ***/
TX - 17149
FL - 17210
RI - 17120
VA - 17198
AK - 17190
-------------
MAX: 17210

How to Set the Index Entry Limit

There are three ways to set the index entry limit in OUD and that is either the command line menu driven option, the silent command line, or using the GUI in ODSM. To keep it simple I am providing the CLI to accomplish this, which works perfectly fine. All you need to change is the attribute and index entry limit value.

STEP 1 – Create the Index if it does not exist

You cannot add the Index Entry Limit index to an attribute if it is not indexed at all.  By default only a few attributes are indexed as part of the plain vanilla OUD installation. Create an index of some type; i.e. approximate, equality, extensible, ordering, presence, or substring.  The following is a command to create an index, just replace the index-type: value to what you need.  You can always modify this later if needed.

# EXAMPLE COMMAND TO CREATE AN INDEX FOR ATTRIBUTE “ST”

./dsconfig create-local-db-index \

         --element-name userRoot \

         --set index-type:equality \

         --type generic \

         --index-name st \

         --hostname localhost \

         --port 4444 \

         --trustAll \

          --bindDN cn=Directory\ Manager \

         --bindPasswordFile passwd.txt \

         --no-prompt

STEP 2 – View the existing attribute indexing

The following command will show the existing indexes for the "st" attribute, so please modify has needed.

# VIEW THE EXISTING INDEX ENTRY LIMIT

./dsconfig -n get-local-db-index-prop \

          --element-name userRoot \

          --index-name st \

          --hostname localhost \

          --port 4444 \

          --trustAll \

          --bindDN cn=Directory\ Manager \

          --bindPasswordFile passwd.txt \

          --no-prompt

STEP 3 – Set the Index Entry Limit

This command is the important one that will set the Index Entry Limit to the value you have decided to be the optimum number.

# SET INDEX ENTRY LIMIT TO 20,000

./dsconfig set-local-db-index-prop \

          --element-name userRoot \

          --index-name st \

          --set index-entry-limit:20000 \

          --hostname localhost \

          --port 4444 \

          --trustAll \

          --bindDN cn=Directory\ Manager \

          --bindPasswordFile passwd.txt \

          --no-prompt

STEP 4 – Rebuild the Index for a single attribute

The following command will rebuild the "st" attribute index; modify it to your attribute.  You can also replace "--index st" with --rebuildALL to rebuild all indexes.  IMPORTANT:  When running this command it will bring OUD offline as the indexes are rebuilt, so make sure when running this in production it be done during a scheduled off peak time and put up the appropriate SYSTEM DOWN warnings.

# REBUILD INDEX FOR SINGLE ATTRIBUTE

./rebuild-index \

          --hostname localhost \

          --baseDN dc=oracle,dc=com \

          --port 4444 \

          --trustAll \

          --bindDN cn=Directory\ Manager \

          --bindPasswordFile passwd.txt \

          --index st

Summary

To summarize setting the right value of Index Entry Limit for an attribute used in an LDAP filter can greatly improve search results for OUD, but be careful about setting this value. Setting it to high will increase memory and CPU since OUD has to manage these new record indexes. That said, used correctly this is a great tuning tip and most people that I have worked with who implemented it have been very successful in improving OUD’s performance.