Choosing a Page Size

Choosing a Page Size

All pages in a data file are the same size. Therefore, when you determine the size of the pages in your file, you must answer the following questions:

•

What is the optimum size for data pages, which hold the fixed-length portion of records to reduce wasted bytes.

•

What is the smallest size that allows index pages to hold your largest key definition? (Even if you do not define keys for your file, the transactional database engine adds a key if the Transaction Durability feature is enabled.)

The following sections guide you through answering these questions. With your answers, you can select a page size that best fits your file.

Optimum Page Size For Minimizing Disk Space

Before you can determine the optimum page size for your file, you must first calculate the file’s physical record length. The physical record length is the sum of the logical record length and the overhead required to store a record on a data page of the file. (For more generalized information about page size, see Page Size.)

The transactional database engine always stores a minimum of 2 bytes of overhead information in every record (as a usage count for that record). The transactional database engine also stores an additional number of bytes in each record, depending on how you define the records and keys in your file.

The following table shows how many bytes of record overhead required without record compression, depending on the characteristics of your file.

Table 17

Record Overhead in Bytes Without Record Compression

File Characteristic

Duplicate Key (per key)

Variable Pointer (with variable record)

Record Length (if VATs1 used)

Blank Truncation Use (with VATs/without VATs)

1VAT: variable-tail allocation table
2na: not applicable

The following table shows how many bytes of record overhead required when using record compression, depending on the characteristics of your file.

Table 18

Record Overhead in Bytes With Record Compression

File Characteristic

Duplicate Key (per key)

Variable Pointer

Record Length (if VATs1 used)

Record Compression Flag

1VAT: variable-tail allocation table
2na: not applicable

The following table shows how many bytes of page overhead are required depending on the page type.

Table 19

Page Overhead in Bytes

The following table shows how many bytes of overhead you must add to the logical record length to obtain the physical record length (based on how you define the records and keys for your file). You can also find a summary of this record overhead information in Table 17 and Table 18.

Table 20

Physical Record Length Worksheet

Task Description

1

Determine the logical record length. For instructions, refer to Calculating the Logical Record Length.

The example file for this worksheet uses a logical record length of 72 bytes. For files with variable-length records, the logical record length refers only to the fixed-length portion of the record.

2

Add 2 for the record usage count.

For a compressed record’s entry, you need to add the usage count plus the variable pointer plus the record compression flag:

6.x and 7.x: 7 bytes (2 + 4 + 1)
8.x and later: 9 bytes (2 + 6 + 1)

3

For each linked-duplicatable key, add 8.

When calculating the number of bytes for duplicatable keys, the transactional database engine does not allocate duplicate pointer space for keys defined as repeating duplicatable at creation time. By default, keys that allow duplicates created at file creation time are linked-duplicate keys. For a compressed record’s entry, add 9 (nine) for pointers for duplicate keys.

The example file has one linked-duplicatable key.

4

For each reserved duplicate pointer, add 8. The example file has no reserved duplicate pointers.

5

If the file allows variable-length records, add 4 for pre-8.x files and 6 for 8.x or later files.

The example file does not allow variable-length records.

6

If the file uses VATs, add 4.

The example file does not use VATs.

7

If the file uses blank truncation, add one of the following:

•

2 if the file does not use VATs

•

4 if the file uses VATs.

The example file does not use VATs.

8

If the file uses System Data to create a System Key, add 8.

The example file does not use System Data.

PHYSICAL RECORD LENGTH

Using the physical record length, you now can determine the file’s optimum page size for data pages.

The transactional database engine stores the fixed length portion of a data record in the data pages; however, it does not break the fixed-length portion of a record across pages. Also, in each data page, the transactional database engine stores overhead information (see Table 17 and 18). You must account for this additional overhead when determining the page size.

A file contains unused space if the page size you choose minus the overhead information amount is not an exact multiple of the physical record length. You can use the formula to find an efficient page size:

Unused bytes = (Page Size minus Data Page Overhead per Table 17 and Table 18) mod (Physical Record Length)

To optimize your file’s use of disk space, select a page size that can buffer your records with the least amount of unused space. The supported page size varies with the file format. See Table 21. If the internal record length (user data + record overhead) is small and the page size is large, the wasted space could be substantial .

Optimum Page Size Example

Consider an example in which the physical record length is 194 bytes. The following table shows how many records can be stored on a page and how many bytes of unused space remains on a page for each possible page size.

Table 21

Physical Record Length Example: 194 Bytes

Applicable File Format

Records per Page

(512 – 6) mod 194

8.x through 9.0

(512 – 8) mod 194

(1,024 – 6) mod 194

8.x through 9.0

(1,024 – 8) mod 194

(1,024 – 10) mod 194

(1,536 – 6) mod 194

8.x through 9.0

(1,536 – 6) mod 194

(2,048 – 6) mod 194

8.x through 9.0

(2,048 – 8) mod 194

(2,048 – 10) mod 194

(2,560 – 6) mod 194

8.x through 9.0

(2,560 – 6) mod 194

(3,072 – 6) mod 194

8.x through 9.0

(3,072 – 6) mod 194

(3584 – 6) mod 194

8.x through 9.0

(3584 – 6) mod 194

(4096 – 6) mod 194

8.x through 9.0

(4096 – 8) mod 194

(4096 – 10) mod 194

(8192 – 8) mod 194

(8192 – 10) mod 194

(16,384 – 10) mod 194

For planning purposes, note that page and record overhead may increase for future file formats. Given a current file format, a record size that exactly fits on a page may require a larger page size to fit in a future file format.

Also note that the database engine automatically upgrades the page size if the record and overhead cannot fit within a specified page size. For example, suppose that you specify a page size of 4,096 for a 9.x file, but the record and overhead requirement is 4,632. The engine will use a page size of 8,192.

As the table above indicates, if you select a page size of 512, only 2 records can be stored per page and 114 to 118 bytes of each page are unused depending on the file format. However, if you select a page size of 4,096, 21 records can be stored per page and only 16 bytes of each page are unused. Those same 21 records would result in over 2 KB of lost space with a page size of 512.

If you have a very small physical record length, most page sizes will result in very little wasted space. However, pre-8.x file versions have a maximum limit of 256 records per page. In that case, if you have a small physical record length, and if you choose a larger page size (for example, 4,096 bytes), it will result in a large amount of wasted space. For example, Table 22 shows the behavior of 14 byte record length for a pre-8.x file version.

Table 22

Example of Pre-8.x File Versions: Record Length 14 Bytes

Records per Page

2

(512 – 6) mod 14

(1,024 – 6) mod 14

4

(1,536 – 6) mod 14

(2,048 – 6) mod 14

(2,560 – 6) mod 14

0

(3,072 – 6) mod 14

(3,584 – 6) mod 14

(4,096 – 6) mod 14

Minimum Page Size

The page size you choose must be large enough to hold eight key values (plus overhead). To find the smallest page size allowable for your file, add the values specified in Table 23.

The table uses a 9.5 file format as an example.

Table 23

Minimum Page Size Worksheet

Task Description

1

Determine the size of the largest key in the file, in bytes. (Using the example Employee file, the largest key is 25 bytes.)

In files that do not have a unique key defined, the system-defined log key (also called System Data) may be the largest key. Its size is 8 bytes.

2

Add one of the following:

•

For keys that do not allow duplicates or that use repeating duplicates, add 8 bytes.

•

For keys that use linked duplicates, add 12 bytes. (This example uses linked duplicates.)

3

Multiply the result by 8. (The transactional database engine requires room for a minimum of 8 keys on a page.)

4

Add index page overhead for the file format:

See entry for Index pages in Table 19.

MINIMUM PAGE SIZE

Select any valid page size that is equal to or greater than the result. Remember that the page size you select must accommodate the size of any keys created after file creation. The total number of key segments may dictate the minimum page size. For example, you can only have eight key segments defined in a file using a 512 page size.

Table 24

Minimum Page Size Worksheet

Number of Key Segments by File Version

“n/a” stands for “not applicable”