Tune database index size to maximize performance

Tips for rebuilding index files

As you know, changing the index page size (also known as an index node size) is a major database maintenance task, and it should be done carefully after you have completed a full, reliable backup. The FairCom database engine presently uses an 8K byte default page size for its b+tree indices for the solutions in its data management product line. These products include:

• FairCom DB multimodel database
• FairCom EDGE for IoT and Industry 4.0
• c-treeRTG data management solution for legacy systems

Each FairCom index page holds one or more key values. The FairCom database engine supports setting PAGE_SIZE from 512 bytes up to a maximum of 64K (65,536).

Meanwhile, operating systems have a default file block size in which they read and write data. For example, the most recent Linux and Windows operating systems are 4K. Other operating systems may vary.

Setting the index page size to match the OS or multiples of the OS file block size can improve I/O performance. In some cases, with large key lengths (consider a rough rule of thumb of > 100 bytes) and/or a large quantity of key values (consider a rule of thumb of > 500 million key values in a single index) setting the index block size to 32K or 64K could produce the best performance because it gives the index more space to hold a large quantity of keys in a single node. Note that in some cases, reducing the PAGE_SIZE to 4K might yield better throughput. The best way to fine tune your PAGE_SIZE setting is to try various sizes with your application to find your best setting.

To make it easy to change index page size, FairCom has produced a best practices guide for you to follow. The document includes the following sections:

• Background
• Recommendations
• Rebuilding Files
• Procedures

The index file size best practices document can be accessed here.

This is just one more example of how FairCom strives to provide you with the developer tools necessary to enable the fastest and most efficient database operations possible. Also available to you is a full developers’ library that includes technical documentation, best practices, and how-to’s for c-treeACE, c-treeEDGE and c-treeRTG.