Index NLEVELS and impact on performance.

[Fin]

Can anyone please explain in layman terms what impact having varying Indexes with NLEVELS = 4 will have on overall performance ? I keep reading anything above 3 will impact performance ??

I know very little about DB2 when it comes to pages, leafs, index levels etc and I have been trying to optimize some of my indexes and found a small number (but very important tables) of indexes have an NLEVEL of 4. One of the indexes in question only has 3 columns defined. Table in question has some 40m rows.

Example Index in question is clustered on an ID, DATE, OUTCOME (being an integer, date, char(1) respectively)

NLEAF = 150460
PAGE_ALLOCATION = 151322
DENSITY = 99

PCTFREE NLEAF NLEVELS FIRSTKEYCARD FIRST2KEYCARD FIRST3KEYCARD
8 1 150460 4 1810231 41222930 41222930

[Lennart Jonsson]

I'm not sure where you've read that NLEVELS above three would be
concidered bad, but I don't think a value of four is extreme in any way.
You might want to have a look at how a B-TREE index is constructed (see
for example http://en.wikipedia.org/wiki/B-tree), but I'll try to give
you some idea.

An index always start of with a root node, the root node can address a
certain number of child nodes, the children of the root node can in
their turn address a number of grandchild nodes for the root, etc. The
leaf nodes finally address data pages in the table (RID LIST).

The height (NLEVELS) of the tree can be said to be determined by two things:

a) The number of data pages in the table (SYSCAT.TABLES.CARD)
b) How well the index discriminates the data pages
(SYSCAT.INDEXES.FULLKEYCARD)

A huge table with an index with low FULLKEYCARD will have a small
NLEVELS value, but it will usually be a bad index. One of the reasons is
that every leaf page in the index will have a big rid list, so an update
of the index will affect a lot of entries in the rid list.

I don't think it will be a good strategy to try to minimize NLEVELS for
it's own sake. I would definitely look for other criteria when it comes
to optimizing indexes.

Looking at one of our productions databases (~250Gb) tells me that ~15%
of the indexes (~1000) have NLEVELS = 4 and ~3% have NLEVELS = 5.


/Lennart

[Ian]

Lennart is correct - it's not as though performance falls off a cliff if
you go from 3 levels to 4 levels in your index. But as NLEVELS
increases it becomes increasingly more expensive to traverse an index.
For OLTP applications this can be problematic.

For a single index key (say a primary key lookup) on an index with 3
levels, you'll do 3 synchronous IO operations to fetch the root page,
level 2 page and the leaf page, plus 1 synchronous IO to the table to
fetch the row.

Adding a 4th level adds an extra synchronous I/O. If you figure each
synchronous I/O takes 4-6ms to complete, you're looking at ~20ms with
NLEVELS=3 or ~25ms with NLEVELS=4 - a 25% performance decrease.

Of course, the root page and some of the other non-leaf pages may be in
the bufferpool which will help with the access times, but adding a level
still requires finding one extra non-leaf page. And with a big index
like the one you have, relying on the bufferpool to mitigate this isn't
always great idea.


This starts to get much worse if you have a query that reads lots of
keys from an index (say on an NLJOIN with the IXSCAN/FETCH on the
inner leg).

DB2 10 added a cool feature called smart index prefetching to help
minimize this. But you're still going to be better off with a flatter
index.



--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---