Database Administrator's Guide

Tuning Index Page Size

FairCom Database Engine has a default page size for its b-tree indexes. Each page holds one or more index entries.

Changing the index page size is a major maintenance task and should be done carefully after a full, reliable backup. The procedures below represent the best practices for this operation.

Background

Operating systems have a default file block size they read and write data in. Most recent Windows operating systems are 4K. Other operating systems will vary. Setting the index page size to match the OS or multiples of OS file block size can improve I/O performance.

FairCom Database Engine supports setting PAGE_SIZE from 512 bytes up to a maximum of 64K (65,536).

Recommendations

You should set your c-tree server PAGE_SIZE to at least the OS file block size, nothing smaller. For example, if you set c-tree's PAGE_SIZE to 2K, then on Windows you are reading 4K at the OS level and throwing away 2K every time.

For most applications, 8K is a fairly good general rule of thumb. If you are looking to fully optimize your application, and if your files are fairly large (see below), you might gain performance by increasing this setting, in multiples of your operating system page size value.

It is difficult to define "fairly large files" because of all the different ways c-tree provides for accessing your files. The best method for tuning this value is to try a few different sizes with your precise application and a copy of your data. For example, increase from 8K to 16K and if your performance increases, then try 32K. Once your performance starts dropping, then scale back in smaller increments looking for the sweet spot for your application.

Rebuilding Files

Recommendation: If you are considering changing your PAGE_SIZE setting, be sure to rebuild ALL of your index files.

If you are reducing your PAGE_SIZE setting, and you miss rebuilding an index, and its value is set larger than your current PAGE_SIZE setting, then you will get an error 40 (KSIZ_ERR, Index node size too large) when this file is opened and it will need to be rebuilt to resolve this error.

Recommendation: Experiment on copies of your data and server folder. When you are confident with the results, back up your source data and then change it.

Procedures

Adjusting page size is a complex topic. To learn more details about it, see Important - PAGE_SIZE Conversions.

Warning: Changing the PAGE_SIZE is a maintenance task that should be done carefully and with a full reliable backup. Practice on a copy of your data and other folders until you are confident with the results. FairCom DB V12 has a default PAGE_SIZE 32K (V11 defaulted to 8K) and it will automatically attempt to convert the FairCom Internal files (FAIRCOM.FCS, *.FSD, *.FDD) during the initial startup. The automatic conversion requires the file ctscmp.exe (ctscmp on Linux/Unix) to be present in the working directory where the FairCom Server binary (faircom.exe for V12) is located (the \server directory for V12).

Notice that a file created with a larger PAGE_SIZE cannot be opened by a system with a smaller PAGE_SIZE.

Follow these procedures to rebuild the affected files:

Shut down the FairCom Database Engine, start it again, then shut it down again to be sure all logs are flushed and a clean final checkpoint has been written.
Make a good backup of your databases and your server folder.
Move this copy to a different machine from your live system. Keep a clean copy in case you need to start over.
Edit ctsrvr.cfg and add the PAGE_SIZE keyword if it is not already there:
PAGE_SIZE 32768
- The size should be a power of 2. The maximum page size is 64k (65536).
- The current default in V12 and later is 32768.
Go to the FairCom data directory.
Delete all log and start files (i.e., all files that start with "L00…", "S00…", "D00…"). With a clean shutdown you should not have any "I00…" files in the directory.
Optional for V12.0.1 and higher: For FairCom DB releases V12.0.0 and earlier, rebuild the three superfiles used by the FairCom Database Engine using the ctscmp utility including the -sect parameter:
- Remember that the sect size is the page size/128. For example, if your page size is 32768, use a sect size of 256.
   ctscmp.exe <server directory>\data\ctdbdict.fsd 256

   ctscmp.exe <server directory>\data\ctreeSQL.dbs\SQL_SYS\ctreeSQL.fdd 256

   ctscmp.exe <server directory>\data\FAIRCOM.FCS 256
Delete the current index files for your data files.
Change the indexes for your data files using the ctcmpcif or ctcmpcif.standalone utility including the -sect parameter (which is -256 in the example below):
./ctcmpcif (server directory)/data/ctreeSQL.dbs/MyData.dat ADMIN ADMIN FAIRCOMS -256

./ctcmpcif.standalone (server directory)/data/ctreeSQL.dbs/MyData.dat -256

If using ctcmpcif, restart the server first.
Fixed-length files: Rebuild the indexes for your fixed-length data files using the ctrbldif utility including the -sect parameter (which is -256 in the example below):
./ctrbldif (server directory)/data/ctreeSQL.dbs/MyData.dat -256

Variable-length files: Rebuild the indexes using ctcmpcif.exe as follows:

ctcmpcif.exe <your application data directory>\your_file.dat 256

Note: It is acceptable to use ctcmpcif.exe to rebuild both fixed and variable-length files. The ctcmpcif.exe utility creates a new copy of the data file by copying all the active records into a new file, and then calling ctrbldif.exe to generate a new index. This step is required to ensure the deleted space management index is properly built within the variable length data file. Fixed-length files don’t have any internal indexes, therefore calling ctrlbdif.exe is sufficient.
Restart the server and verify no errors were logged in <faircom>/data/CTSTATUS.FCS.
Connect to your data files and verify no errors were returned.