Speed up Index Rebuild

Posted by martinz on 02-Mar-2020 09:57

Hi all,

We're cleaning up a database for one of our customers. It's a production database, and off course downtime should be as short as possible.

As it's a large database for us (around 1.5 TB), and some of the tables can be completely removed, and we want to reclaim unused diskspace, we decided to dump and load the database. We can do that per area.

After dumping and loading, we need to do an idxbuild. This takes 8 hours for an area with about 163 GB of data. This area only contains tables, indexes are in a separate area. The command used is proutil _db_ -C idxbuild area _indexarea_ -TB 64 -TM 32 -T _tmpdir_ -thread 1 -datascanthreads 2 -mergethreads 2 -B 5000 -i

The machine used is a linux machine with 4 cpu's and 32GB of memory. We have one volume for the database, and some other volumes for the tmpdir. All are on a SAN with SSD storage (I know, local storage would be much better, but we can't get that right now). The database is a OpenEdge 11.5.1 database. 

Scan for the index area takes about 4 seconds, as it is empty. Scan for the data area takes about 2.3 hours. After that, most of the time seems to be going into sorting index groups.

Any pointers for speeding up the idxbuild? Spreading out the sortfiles over multiple volumes using -SS and a .srt-file? Using -TF to use more memory? Using more extents for the index- or data-area?

Regards, Martin.

Posted by James Palmer on 02-Mar-2020 12:28

There's a lot of levers here but myself and Paul Koufalis (actually mostly Paul) have done a bit of research here for a workshop we held at EMEA PUG last year. Here's a quick summary of what we found.

-B - bigger is not better. 512 should suffice.

-TB 64

-TMB 64

-TM 32

-thread 1 (default for Enterprise but no harm specifying)

-datascanthreads - you can happily increase this until it doesn't go any faster. Usually 1-2x CPUs is the limit.

-SG always use 64

-threadnum

-mergethreads

These 2 have an interesting relationship. You don't want to have too many threads as a result of these, but we think that in general you want to have one small, the other big. But experiment. You don't want to have more threads than 2x CPUs though. So in your case 8. Try using 2 for one and 4 for the other, then switch and see which is better.

-TF 80 - use 80% of RAM available. The more RAM you make available the better, but you can't allocate all of it as the system needs to run still. If you can persuade the sys admins to make more RAM available for the actual build it will help HUGELY.

-z -rusage - these add extra logging to the output. In particular you want to aim for 0 writes to disk during sort/merge. That's the holy grail anyway. The more RAM you make available the more likely it is.

Hope that helps! Let us know if it makes any difference.

Posted by George Potemkin on 02-Mar-2020 14:13

The results of the tests with 1TB table:

community.progress.com/.../57363

All Replies

Posted by James Palmer on 02-Mar-2020 12:28

There's a lot of levers here but myself and Paul Koufalis (actually mostly Paul) have done a bit of research here for a workshop we held at EMEA PUG last year. Here's a quick summary of what we found.

-B - bigger is not better. 512 should suffice.

-TB 64

-TMB 64

-TM 32

-thread 1 (default for Enterprise but no harm specifying)

-datascanthreads - you can happily increase this until it doesn't go any faster. Usually 1-2x CPUs is the limit.

-SG always use 64

-threadnum

-mergethreads

These 2 have an interesting relationship. You don't want to have too many threads as a result of these, but we think that in general you want to have one small, the other big. But experiment. You don't want to have more threads than 2x CPUs though. So in your case 8. Try using 2 for one and 4 for the other, then switch and see which is better.

-TF 80 - use 80% of RAM available. The more RAM you make available the better, but you can't allocate all of it as the system needs to run still. If you can persuade the sys admins to make more RAM available for the actual build it will help HUGELY.

-z -rusage - these add extra logging to the output. In particular you want to aim for 0 writes to disk during sort/merge. That's the holy grail anyway. The more RAM you make available the more likely it is.

Hope that helps! Let us know if it makes any difference.

Posted by martinz on 02-Mar-2020 13:21

Thanks James! I'll perform some new tests based on these suggestions!

Posted by George Potemkin on 02-Mar-2020 14:13

The results of the tests with 1TB table:

community.progress.com/.../57363

Posted by ChUIMonster on 02-Mar-2020 15:47

> All are on a SAN with SSD storage (I know, local storage would be much better, but we can't get that right now).

I'm glad to hear that the message is getting at least partway through.

But just in case anyone is thinking that the difference is some smallish percentage -- it is not.

Operations that take hours (or DAYS) on SAN storage (including "all flash SAN") will take minutes (or *seconds*) if you are able to replace the SAN with internal storage.

This is not a small difference.

Sure, not everybody needs that kind of performance.  Sure, there are plenty of applications where a few hours for a backup or an index rebuild are no big deal.

But if spending hours, or days, waiting for this stuff matters to your business the #1 thing that you can do to improve the performance of those tasks is to get off SAN storage and onto internal storage.

It is also less expensive.

Posted by dbeavon on 02-Mar-2020 16:13

>> not a small difference

In Microsoft Azure they allow you to pick "premium" SSD disk that is hosted locally, or remote disk.  Both are SSD.  It is more expensive to use local disk in Azure.  (But I know that's not what you were referring to).

There are lots of variables to consider, that depend on how you use the database.  Your users might not always need the expensive disk, depending on how frequently you need to do schema changes (outages).  This may come down to some very simple factors ... like how often data is written vs read, and how much data comes from your buffer pool rather than from the physical disk.

The nice thing about setting up hardware in the cloud is that you can switch back and forth and see what your customers are willing to afford.  In the cloud you might even be able to "cheat" and switch back and forth as needed. IE. use local disk for certain uncommon operations that only happen once a quarter (index rebuilds, dump/loads, etc) while using remote disk for day-to-day purposes.

Posted by martinz on 09-Mar-2020 07:29

So we doubled the amount of memory, and I used a lot of parameters suggested by James and George. It halved the time needed for the idx build. In fact, the time needed for the data scan fase stayed about the same(about 2.5 hours), and the time needed for the actual sorting and merging went from about 5.5h to just over 1.5h.

We tested about 8 different scenarios, varying the -TMB, -mergethreads, -datascanthreads and -pfactor parameters. For us, this worked best:

${DLC}/bin/proutil ${DBDIR}/${DB} -C idxbuild area Index_960 -TB 64 -TM 32 -TMB 4096 -SG 64 -TF 80 -T ${DUMPDIR} -threadnum 4 -mergethreads 2 -datascanthreads 8 -pfactor 80 -i -rusage -z

Posted by James Palmer on 09-Mar-2020 08:27

Not a bad improvement there! Good job!

Posted by Rob Fitzpatrick on 09-Mar-2020 14:30

>  In fact, the time needed for the data scan fase stayed about the same(about 2.5 hours)

If the time didn't change then perhaps the data scan phase(s) were not multi-threaded. Check the output in the log, you will see messages indicating the data scan was multi-threaded, if it was.  For -datascanthreads to take effect for an area, four conditions must be satisfied:

- the area being scanned is a Type II area;

- you answered "yes" when asked if you have enough disk space for sorting;

- none of the indexes being rebuilt are in the area being scanned;

- none of the tables in the area being scanned has a word index.

Posted by gus bjorklund on 09-Mar-2020 16:16

> On Mar 9, 2020, at 10:32 AM, Rob Fitzpatrick wrote:

>

> If the time didn't change then perhaps the data scan phase(s) were not multi-threaded

it sometimes happens that the scan phase was disk-bound and the disk(s) wont go any faster.

This thread is closed