Question IDXBUILD - rebuild index with some future headroom?

[Chris Hughes]

Hi all

Continuing from one of my previous posts, on reloading all of my data into new type 2 dbs I intend to do an index rebuild.

I read and am told that this tightly packs - but this is bad for performance - is there an option or way to leave some headroom on the indexes for them to expand into?

Thanks

Chris.

 

[Rob Fitzpatrick]

Yes. The degree to which index keys are packed into blocks is controlled with the -pfactor parameter to proutil idxbuild. The default value is 100 (percent), and having your index blocks completely full may cause block split operations when inserting or updating keys, which is expensive. Of course, if you had data that was read-only, like historic transaction data or a data warehouse, you could safely use -pfactor 100 without issues.

The suggested practice for indexes that will be updated is to use -pfactor 80.

 

[Chris Hughes]

As ever - thanks for your invaluable help

 

[Rob Fitzpatrick]

As you're prepping for an index rebuild, you probably want to think about minimizing downtime which means maximizing idxbuild performance. The keys to success are (a) using 10.2B07 or 11.2 and (b) optimizing the idxbuild parameters for your DB for your system. You're on 10.2B07, which is a good start. But testing and documenting your test results is also key.

You may not need to go into this depth if it's a non-production DB or if it's very small. But otherwise it helps to understand what these parameters do, how they relate to each other, and how to tune them. Most are not covered in the documentation set as they were added in SP04 and SP06, though they are documented somewhat in the release notes.

A source of information that you won't find in the PSC documentation is in the recent session on idxbuild performance at the 2013 PUG Challenge Americas conference. You can download the slide deck and two versions of the audio recording of the session at the downloads page (idxbuild is session 261). In the slide deck, be sure to look at the presenter's notes as well as the slides; both contain good foundational and tuning info.

 

[cj_brandt]

Something that just bit me with the index rebuild with the new datascanthreads option (new in the version Rob mentions above). I choose to rebuild all indexes in an area - around 1,000 indexes, but 1 of them was a word index, so my datascan was limited to a single thread...

 

[Rob Fitzpatrick]

Yeah, that sucks. There are a few rules for getting multi-threading and that violates one of them. Sounds like it would be worth moving it to its own area.

Thanks for the heads-up, I should check my schemas for those conditions as well.

For everyone's benefit, the rules are:

The data being scanned must meet all of the following criteria for a multi-threaded data scan to occur:

o The data area being scanned must be a Type II area
o No index within the data area being scanned is also being rebuilt
o No index associated with the data area being scanned is a word index
o Index rebuild must be run with the "sort" option, that is answering "y" when asked if you have enough room for sorting.