Answered Index Rebuild Performance

[gareth.roberts]

Hello,

As part of an upgrade to a new version of our product we have to apply a DF (with various new table, fields, indexes) to a database. We add the indexes inactive and then run an index rebuild to rebuild just the inactive indexes.

We are in the process of testing this process for one of our biggest clients whose database is just over 200GB. We applied the df with no problem and then ran the index rebuild, this takes just under 2 hours and reports that it is complete. However the index rebuild utility has not return back to the command prompt and the _dbutil process still has over 20 GB of memory committed to it. This is gradually reducing over time and we assume once this has all gone, and presumably committed to disk, the process will truly finish. This has been like this for nearly 5 hours now and has reduced to 12 GB.

The index rebuild was run with the following command:

proutil <dbname> -C idexbuild -TB 31 -TM 32 -SG 64 -B 20000

Is this expected behavior?

Is there anything we can do to speed this up?

OS is Windows Server 2008 R2 and OpenEdge version is 10.2B07 64 bit.

Thanks

Gareth

 

[Rob Fitzpatrick]

There are still some unknowns here like what kind of storage you have available (direct attach or SAN, RAID level, number of spindles available, etc.), available RAM, available cores, storage area design (Type I versus Type II), and additional workload on the box and storage. These are all relevant for index rebuild performance in 10.2B07.

Read Rich Banville's presentation on index rebuild changes in 10.2B06 and later:
http://pugchallenge.org/2012PPT/NEPUG_Performance.pptx

Pay attention to what he says about multi-threading. If you go overboard you can run out of file handles and crash (I'm not sure about Windows limits on that though). Also read the OE release notes for SP07. They contain index rebuild parameter descriptions that are not in the documentation set. Note for example that the max for -TB is now 64, not 31. Also learn about -TF (this is huge), -TMB, -threadnum, -mergethreads, and -datascanthreads. And if you want to get advanced and you know that you have only indexes to be rebuilt in a given area, you can optimize by truncating that area before the rebuild to skip the initial index block scan phase. It's mentioned in the slide deck linked above.

From a cursory glance I'd say you can definitely do better than you are now, judging by your params. Also make sure you use -rusage to capture your resource utilization, so you can see the effect that your parameter value changes have.

Preparation, testing, logging and analysis (repeated several times) should yield some significant performance improvements. Also, if you are resource-constrained you should experiment with multiple partial rebuilds rather than a single full one. I've had good success with that approach on a box with low RAM.

 

[gareth.roberts]

Thanks for your reply Rob, I will certainly experiment with these parameters and see what benefits I can get.

The bit that confused me though was the gap between the index rebuild saying it was complete (this took 2 hours) and the session actually ending (nearly 7 hours later). This extract from the database log file shows what I mean:

[2013/04/05@11:48:20.958+0100] P-420        T-10228 I          : (11465) 171 indexes were rebuilt.
[2013/04/05@11:48:20.959+0100] P-420        T-10228 I          : (-----) Index rebuild complete.  0 error(s) encountered.  Elapsed time: 7179.237
[2013/04/05@18:43:39.394+0100] P-420        T-10228 I          : (334)  Idxbuild session end.

 

[gareth.roberts]

Forgot to answer your questions about the box it is a dedicated database server with 44 GB RAM, 3.4 GHz Quad Xeon Processor, the databases are on direct attached storage consisting of 8 x 146GB 15k SAS disks in a RAID 10 and the database is all Type II storage.

 

[Cringer]

Great post, Rob - I was going to link up the exact same presentation, but you had already done it

 

[Rob Fitzpatrick]

Thanks for your reply Rob, I will certainly experiment with these parameters and see what benefits I can get.

The bit that confused me though was the gap between the index rebuild saying it was complete (this took 2 hours) and the session actually ending (nearly 7 hours later). This extract from the database log file shows what I mean:

[2013/04/05@11:48:20.958+0100] P-420        T-10228 I          : (11465) 171 indexes were rebuilt.
[2013/04/05@11:48:20.959+0100] P-420        T-10228 I          : (-----) Index rebuild complete.  0 error(s) encountered.  Elapsed time: 7179.237
[2013/04/05@18:43:39.394+0100] P-420        T-10228 I          : (334)  Idxbuild session end.

Over the weekend I rebuilt DBs for a client. The largest DB was smaller than yours (about 35 GB), but similar hardware; 15K SAS in RAID 10, 16-core box with 64 GB of RAM (on Red Hat 6.1). The time gap between messages 11465 and 334 was 603 ms. I don't deal with any 200 GB DBs but I've never seen any noticeable time delay between those two messages.

If I were you I'd open a case with TS to find out what is supposed to happen in the time between those two messages being written to the log. Also, when you do another test and are waiting in that gap between (11465) and (334), use Process Explorer to see what file handles _dbutil.exe has and what file I/O it's doing, if any.

Also, search for all instances of (11480) and (11483) in the log to see how much temp file I/O you did. You should have a pair of those messages per area. Ideally, it should be zero for all of them. You may want to reduce your -B as well. Rich B recommends to keep it small, about 1024.

 

[gareth.roberts]

Thanks Rob. I logged a call with Technical Support on Friday and they have confirmed this morning that they think this could be a bug.

 

[cj_brandt]

If you have AI enabled (and you should), you can keep AI enabled by activating the indexes with idxactivate instead of idxbuild. This comes in very handy if you are keeping a standby copy of the database in a separate physical location which is kept up to date by applying AI logs. If idxbuild is used, then AI must be disabled and the database has to be copied to the other location - which could take awhile for a 200gb db.

idxbuild (when properly configured ) is faster than idxactivate for large tables.

 

[gareth.roberts]

Thanks for the advise but we are upgrading OpenEdge version so have to rebuild the AI anyway.

 

[LynnReas]

I also have seen this behavior in 10.2B SP7. In 10.2B SP5 once the message of indexes rebuild complete it would return to command prompt.
10.2B SP7 that return to command prompt is hours. It also shows in the Event Log.
The same occurs with dbanalys and tabanalys as well. Seems items that run with _dbutil.exe exhibit this oddity.

Lynn

 

[Rob Fitzpatrick]

I also have seen this behavior in 10.2B SP7. In 10.2B SP5 once the message of indexes rebuild complete it would return to command prompt.
10.2B SP7 that return to command prompt is hours. It also shows in the Event Log.
The same occurs with dbanalys and tabanalys as well. Seems items that run with _dbutil.exe exhibit this oddity.

Lynn

Hi Lynn,

I don't see this issue in 10.2B07 on Red Hat or CentOS. My idxbuilds and analyses return to the command prompt as soon as they're done, and the process doesn't hang around longer than it should. Is it possible this is a Windows-specific issue in SP07? I haven't tested it in Server 2008 R2.

 

[gareth.roberts]

Thanks Lynn I will give SP5 ago and see if it is any better.

 

[Chris Hughes]

Love resurrecting old threads

Just wanted to say me too

Win 2008 R2, 10.2B SP7 64bit, massive hang on rebuild

20GB of temp file space used

My command line

proutil mydb -C idxbuild ALL -TB 31 -TM 32 -B 1000 -SG 64 -pfactor 80

I'm gonna read the above link and suggestions - hopefully can avoid this for the live run!

 

[Cringer]

Can you get SP08 installed before you go? AFAIK there are some tweaks in that SP.

 

[TheMadDBA]

Most of the idxbuild improvements are in 10.2B07. Make sure you test and take advantage of those.

If you are getting a 20GB temp file then you are probably trying to build too many indexes at once (unless you have one table with 10-20GB of indexes). Storage areas matter since the temporary files are created/applied/removed for each area instead for the entire database.

If you have a proper storage layout the idxbuild times will be much faster. If not fix that first. If for some reason you can't fix the storage area layout, try rebuilding indexes in chunks.

 

[Rob Fitzpatrick]

Love resurrecting old threads

Just wanted to say me too

Win 2008 R2, 10.2B SP7 64bit, massive hang on rebuild

20GB of temp file space used

My command line

proutil mydb -C idxbuild ALL -TB 31 -TM 32 -B 1000 -SG 64 -pfactor 80

I'm gonna read the above link and suggestions - hopefully can avoid this for the live run!

You can avoid the delay issue on 64-bit Windows Server by installing SP08. It adds a new parameter -freeTF n. On Windows it defaults to 0, so you don't have to set it explicitly. It is documented in the SP08 release notes.

PSC00256208 : PROUTIL IDXBUILD has -freeTF parameter for merge buffer memory
management
================================================================================
Adding -freeTF <n> to the PROUTIL IDXBUILD command line specifies whether to
free the merge buffer memory explicitly or
leave the free to the operating system at the completion of IDXBUILD.

To improve performance, the merge buffers in the -TF pool are pointer swapped
rather than using strcpy(), which results in the list of
allocated memory chunks being in sort order rather than allocated order. When
the buffers in the -TF pool are freed in an order other
than they are allocated, there can be severe performance problems on Windows
64-bit. By setting -freeTF 0 on Windows
64-bit, the allocated memory is freed by the operating system when IDXBUILD
completes.

Values for -freeTF <n>:

0 -- Do not explicitly free merge buffer memory. The default for Windows
64-bit.
1 -- Explicitly free merge buffer memory. The default for all Unix platforms
and Windows 32-bit.

Chris, if you are on 10.2B SP07 or later you can probably do much better than the params you have shown above. You aren't taking much advantage of the new functionality added in SP06. So unless your rebuilds don't need to be any faster than they are, you should optimize.

 

[Chris Hughes]

I will be type 2'ing as part of the upgrade.

Finally finished

[2014/12/19@16:09:05.052+0000] P-6104       T-6888  I          : (-----) Index rebuild complete.  0 error(s) encountered.  Elapsed time: 4535.806
[2014/12/19@17:47:31.030+0000] P-6104       T-6888  I          : (334)   Idxbuild session end.

Thanks