I had the occation to rebuild a btree index (Bin location) on a file with 430,000 records. Tested on my development laptop and took 1:45 (1min 45secs). Then started the same process on a client terminal server running HyperV and took more than 8:55. Then performed the same process on the main server where OI is located and the rebuild took 1:40. Why does the rebuild run more than 4 times quicker on the main server that on the terminal server. Same file, same hardware, same LH etc. Similar poor performance on work stations.

The client has been complaining about performance for a long time since the client changed from server2008 to server2019. Client installed SD hardrives etc to try and improve performance without any improvement.

Had my IT expert look into the performance issue and discovered the throughput on the terminal server collecting the index data is 3.6kb/sec while in the server is 60kb/sec. The sort function using the temp folder used about the same CPU on the terminal sever as the main server.

Running UD 4.7.2 and OI9.4.6

Any explanation would grateful.

Chris

Hi Chris,

My theory for why OpenInsight slows down reading from server2019 across the network is that the way openinsight uses files triggers server2019 SMB slow behaviors. There are various articles describing tuning, for example this one from Microsoft re

SMB slow file transfer

I believe that antivirus and the OS do more processing of requests from the network than of requests from processes on the local machine.

Workarounds

The UD 4.7 client will read the revparam file many times. The UD 5.3 client reads revparam when you open the volume and caches the result. So, I believe but have not tested that the UD 5.3 will be more performant than the 4.7 on server 2019 across a network. If you don't want to upgrade to the UD5 you can use a revparam with a sharename parameter to avoid reading the revparam file across the network. Make a local folder with only a revparam in it, attach that local folder, then all communication with the UD will be by TCPIP, so you will not invoke the SMB slowness.

Thanks Bob for the reply.

Is there something you can do about the SMB bottleneck in server2019. I know Revelation are loosing customers as a result of poor performance as I get asked frequently. Something needs to be done.

Is there a white paper to help me modify OI parameters to optimise performance as I have no experience what is required to change table settings etc.

Here are some test results on generating a report with 4 distinct sections (lap times):

Lap01 Time taken for a select 14674 records from 150614 records in the INVOICE_HIST file

Lap02 Time taken to process 14674 records (get item nos etc)

Lap03 Time taken for a select 53566 records from 93736 records in the WAREHOUSE_TRANS file

Lap04 Time taken to process 53566 records (Get transaction data based on first select)

This is the result on the main server:

https://www.dropbox.com/s/xhg2hhkcllddedp/Screenshot_20211119-090110_Clock.jpg?dl=1

This is the result on the terminal server:

https://www.dropbox.com/s/k6tzrhns4bkaz1u/Screenshot_20211119-102042_Clock.jpg?dl=1

THis is the result on my stand alone laptop:

https://www.dropbox.com/s/dluhcaxrxpcfpp7/Screenshot_20211119-121126_Clock.jpg?dl=1

It appears the processing times a similar on all 3 scenarios

The main difference is the time taken to select the required records.

Some questions I have:

Optimum table setting for the index files. Rebuilding index on file with 430,000 records is very slow compared with stand alone laptop

Revparam settings

OV & LK ratio

Optimise for networks.

Use another database e.g SQL for large files (No experience)

etc.

Any advice would be appreciated.

Chris

Post removed by author

Hi Chris,

We published a whitepaper on revparam tuning, perhaps that will help

https://www.revelation.com/revweb/KB_Articles/KB1089.htm

We have lo released a tool named rti_remaketable which will remake tables with larger frame sizes and lower thresholds, this can increase performance at the cost of wasting disk space.

Sorting of temporary files is a big reason for slow index rebuilds. You could try using SET_SORT_FILE "%TEMP% before rebullding the indexes, That should tell oi to create the sort files locally. I recommend you rebuild the indexes on the server.

FWIW, We have re-written the index rebuild process in OI10. It does all sorting in memory, and it can rebuild all the indexes in one pass through the data if you use UPDATE_INDEX(table, '', 1)

- Bob

View this thread on the Works forum...