 | Programmer's Guide (v9 SP2 (9.5) revision 1) |  |
|
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:
The following sections guide you through answering these questions. Once you have these values, you can select a page size that best fits your file.
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.
Table 5-5 shows the file size per page record length in Pervasive PSQL 9.5 and later.
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 you will have when using uncompressed data pages, depending on the characteristics of your file.
The following table shows how many bytes of record overhead you will have when using compressed data pages, depending on the characteristics of your file.
Table 5-8 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 5-6 and Table 5-7 .
|
Task Description
|
Example
|
|---|---|
|
1. Determine the logical record length. For instructions, refer to Calculating the Logical Record Length .
For files with variable-length records, the logical record length refers only to the fixed-length portion of the record.
|
72
|
|
2. Add 2 (two) for the record usage count (do not add this for file formats earlier than 6.x). For a compressed record's entry:
pre-6.x: 5 bytes
6.x & 7.x: 7 bytes 8.x & later: 9 bytes |
72 + 2 = 74
|
|
3. For each linked-duplicatable key, add 8 (eight).
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.
|
74 + 8 = 82
|
|
4. For each reserved duplicate pointer, add 8 (eight). The example file has no reserved duplicate pointers.
For a compressed record's entry, add 9 (nine) for reserved duplicate pointers.
|
82 + 0 = 82
|
|
5. If the file allows variable-length records, add 8 (eight) for pre-8.x files and 10 (ten) for 8.x or later files.
The example file does not allow variable-length records.
|
82 + 0 = 82
|
|
6. If the file uses blank truncation, add one of the following. If the file does not use blank truncation, add nothing.
w Add 4 (four) if the file uses VATs.
For a compressed record's entry, add an additional 4 (four) byte record-length field.
w Add 2 (two) if the file does not use VATs.
The example file does not use blank truncation.
|
82 + 0 = 82
|
|
7. If the file uses Sys Data to create a Sys Key, add 8.
The example file does not use Sys Data.
|
82 + 0 = 82
|
|
PHYSICAL RECORD LENGTH
|
82
|
|
PHYSICAL RECORD LENGTH FOR 9.5 FILE CREATED WITH COMPRESSION
|
9 + 8 = 17
|
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 5-6 and Table 5-7). 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:
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. Page size is handled differently for pre-8.x file fomats than for later versions. Pre-8.x files are always a multiple of 512 bytes up to 4,096 bytes as described in Table 5-9. 8.x and later file formats use exponential values of 512. If the internal record length (user data + overhead) is small and the page size is large, the wasted space could be substantial (up to 2,554 bytes, considering a 4,096 page size and an internal record length of 6).
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.
As the table indicates, if you select a page size of 512, only 2 records can be stored per page and 114-118 bytes of each page are unused depending on the file format. However, the 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 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, 4096 bytes), it will result in a large amount of wasted space. For example, Table 5-10 shows the behavior of 14 byte record length for a pre-8.x file version.
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, you must perform the following tasks outlined in Table 5-11.
Select any 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 seven key segments defined in a file using a 512 page size.
|
Chapter contents
Prev topic: Calculating the Logical Record Length
|