Server Configuration Parameters

When this setting is On, the database engine accepts user credentials stored on the client. The method and location of storage depends on the operating system of the client:

•

Windows clients: these credentials are stored in the Windows registry. When the Prompt for Client Credentials is set to On, then a popup dialog allows you to save the credentials by selecting the Save User name and Password check box. Alternatively, you can use the pvnetpass command-line utility to manage stored credentials.

•

Linux and OS X clients: credentials are stored in the PSQL registry by the pvnetpass utility.

When this setting is Off, the database engine forces the client to omit stored credentials from any database operation that requires credentials. Such credentials must be supplied by the application or through the login dialog. The login dialog still writes client-stored credentials if specified using the login dialog, even if this setting is Off. However, they will not be accepted.

When client-stored credentials are allowed, anyone can sit at that particular client computer and log in to the database using the stored credentials without knowing those credentials. This behavior can be convenient for environments in which strict authentication of individual users is not a concern, such as a physically secured work area where all users have the same level of access permissions. On the other hand, in environments where unauthorized personnel are present or authorized users have varying levels of access permissions, this setting must be Off.

See Also

Allow Client-stored Credentials

Wire Encryption


Type	Range	Default	Units	Requires Engine Restart
Single select	Never If Needed Always	If Needed	None	Yes

This parameter specifies whether the given client or server should use encryption for its network communications. The default value of If Needed means that the client or server only uses encryption if the other end of the communication stream requires it. For example, assume that Server A has its Wire Encryption value set to Always. Server B has its value set to Never. Your client has its value set to If Needed. In this case, the client will use encryption when communicating with Server A, but it will not use encryption when communicating with Server B.

The following chart summarizes the behavior given each possible combination of client and server values:

Table 11

Client/Server Results of Wire Encryption Combinations

Client Setting

Server Setting “Never”

Server Setting “Always”

Server Setting “If Needed”

Encryption used; level determined by highest Wire Encryption Level setting between client and server

Encryption used; level determined by client’s Wire Encryption Level setting.

If Needed

Encryption not used

Encryption used; level determined by server’s Wire Encryption Level setting

Encryption not used

Wire Encryption Level


Type	Range	Default	Units	Requires Engine Restart
Single select	Low Medium High	Medium	None	Yes

This setting specifies the strength of the encryption key that should be used for encrypted communications. The following table shows the levels available.

Table 12

Meaning of Encryption Level Values

Value

Meaning

Low

40-bit encryption key used

Medium

56-bit encryption key used

High

128-bit encryption key used

Encryption using a key 128 bits long is generally accepted as strong encryption. The other settings provide progressively less protection but higher performance, in the event that you require some level of encryption but are willing to accept a lower level of deterrence to gain better performance.

When a client and a server both require encryption and one specifies a stronger encryption level than the other, the two entities use the stronger level to communicate. When a client and a server both require encryption and one specifies a stronger encryption level than the other, the two entities use the stronger level to communicate.

Communication Protocols

Communication Protocols contains the following configuration settings:

•

Auto Reconnect Timeout

•

Enable Auto Reconnect (Windows only)

•

Listen IP Address

•

NetBIOS Port (Workgroup engines only)

•

Supported Protocols

•

TCP/IP Multihomed

•

TCP/IP Port

Auto Reconnect Timeout


Type	Range	Default	Units	Requires Engine Restart
Numeric	45 - 65535	180	seconds	Yes

This setting specifies how long the client will attempt to connect to the server before giving up. When an Auto Reconnect-enabled client first connects to a Auto Reconnect-enabled server, the server communicates this value to the client so that both components know how long to attempt to reconnect in the event of a network interruption.

Enable Auto Reconnect (Windows only)


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	Yes

This setting specifies whether you want the server to support clients attempting to auto-reconnect during a network outage. A setting of On means AutoReconnect is enabled.

Auto Reconnect is not in effect for a given client connection unless this setting is also enabled in that client’s configuration.

To specify how long a client will attempt to reconnect to the server before giving up, see Auto Reconnect Timeout, above.

Listen IP Address


Type	Range	Default	Units	Requires Engine Restart
String	Valid IP address or multiple addresses separated by a comma between each address	0.0.0.0	None	Yes

This option specifies the IP address or addresses the database engine listens on when TCP/IP Multihomed is Off. This option is ignored when TCP/IP Multihomed is On.

Multiple IP addresses may be specified but must be separated by a comma between each address. The string can be a combination of IPv4 and IPv6 addresses. Any of the IPv6 address formats supported by PSQL can be used. See Drive-based Formats in Getting Started with PSQL.

NetBIOS Port (Workgroup engines only)


Type	Range	Default	Units	Requires Engine Restart
Numeric	33 to 254	66	None	Yes

This option specifies the NetBIOS port the MicroKernel listens on. The Server engine does not support NetBIOS.

Supported Protocols


Type	Range	Default	Units	Requires Engine Restart
Multiple select	See below	TCP/IP	None	Yes

This setting specifies the protocols on which the database engine listens for client connections. If more than one protocol is specified, the database engine listens on all specified protocols. The default is TCP/IP. The available options are:

•

TCP/IP

•

SPXII

•

NetBIOS

You must have at least one protocol enabled at both the client and the server or they cannot communicate.

PSQL Workgroup

NetBIOS is valid only for PSQL Workgroup, not PSQL Server.

Linux and OS X

TCP/IP is the only supported protocol for PSQL running on Linux or OS X. Therefore, the Supported Protocols setting is not available for those environments.

TCP/IP Multihomed


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	On	None	Yes

This option specifies whether the database engine should listen for client connections on all network interfaces. If it is set to On, the database engine listens on all network interfaces, and the IP addresses listed in the Listen IP Address option is ignored. If this setting is Off, you must specify in Listen IP Address which addresses)for the database engine to use for client communications.

TCP/IP Port


Type	Range	Default	Units	Requires Engine Restart
Numeric	256 to 65535	1583	None	Yes

This setting configures the port number used by the Relational Engine.

This port number must be the same as that defined in any Client DSNs pointing to this server. For information on how to change the port number in a Client DSN, see Advanced Connection Attributes in ODBC Guide.

For additional information on ports, see Changing the Default Communication Ports in Getting Started with PSQL.

Compatibility

Compatibility contains the following configuration settings:

•

Create File Version

•

System Data

Create File Version


Type	Range	Default	Units	Requires Engine Restart
SelectOne	6.x - 9.x	9.x	None	No

This setting specifies the format in which all new files are created. The 10.x database engine can write to files using the existing file format. In other words, it writes to 7.x files using the 7.x file format, writes to 8.x files using the 8.x file format, and so forth. (The 10.x database engine can read files created with 5.x, 6.x, 7.x, 8.x, and 9.x versions of the database engine.)

Specify 6.x, 7.x, or 8.x only if you need backward compatibility with a previous version of the MicroKernel. Specifying a previous file version does not affect any existing 9.x files.

Note: Dictionary files (DDFs) must be created with a file format of 6.x or later. The New Database wizard uses the setting for create file version. The data files can be in any of the previous file formats supported. Only the DDFs must use a file format of 6.x or later.

System Data


Type	Range	Default	Units	Requires Engine Restart
Single select	See below	If needed	None	No

System data refers to a hidden unique key in each record. Because the MicroKernel relies on uniquely identifying rows in order to ensure transaction durability, a file must either have a unique key defined or have system data included in the file. The default value is If needed; the available options are:

•

None. By default, system data is not included on file creation. Application developers using the Create operation can override this setting.

•

If needed. System data is added to the file on file creation if the file does not have a unique key.

•

Always. System data is always added on file creation, regardless of whether the file has a unique key.

Note: The System Data setting does not affect existing files. This setting only affects how new files are created.

If you want to use transaction durability with a file that was not created with System Data turned on and does not have a unique key, you must re-create the file after setting System Data to Yes or If needed.

The Relational Engine always creates files with System Data. This information applies to files created through SQL, OLE-DB, JDBC, or any method other than the Btrieve API.

Even if a file has a unique key, you may want to include system data, because users can drop indexes.

Data Integrity

Data Integrity contains the following configuration settings:

•

Archival Logging Selected Files

•

Initiation Time Limit

•

Operation Bundle Limit

•

Transaction Durability

•

Transaction Logging

•

Wait Lock Timeout

Archival Logging Selected Files


Type	Range	Default	Units	Requires Engine Restart
Boolean	On, Off	Off	None	Yes

This setting controls whether the MicroKernel performs archival logging, which can facilitate your file backup activities. If a system failure occurs, you can use the archival log files and the BUTIL -ROLLFWD command to recover changes made to a file between the time of the last backup and a system failure.

To direct the MicroKernel to perform archival logging, you must specify the files for which the MicroKernel is to perform archival logging by adding entries to an archival log configuration file that you create on the volume that contains the files. For more information about archival logging, refer to Understanding Archival Logging and Continuous Operations.

Initiation Time Limit


Type	Range	Default	Units	Requires Engine Restart
Numeric	1 - 1800000	10000	milliseconds	No

This setting specifies the time limit that triggers a system transaction. The MicroKernel initiates a system transaction when it reaches the Operation Bundle Limit or the time limit, whichever comes first, or when it needs to reuse cache.

Operation Bundle Limit


Type	Range	Default	Units	Requires Engine Restart
Numeric	1 to 65535	65535	None	No

This option specifies the maximum number of operations (performed on any one file) required to trigger a system transaction. The MicroKernel initiates a system transaction when it reaches the bundle limit or the Initiation Time Limit, whichever comes first, or when it needs to reuse cache.

The MicroKernel Database Engine treats each user transaction (starting with Begin Transaction until End Transaction or Abort Transaction) as one operation. For example, if there are 100 Btrieve operations between the Begin Transaction and the End Transaction operation, then all the 102 Btrieve operations together are treated as a single operation.

Transaction Durability


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	Yes

Transaction Durability is the same as Transaction Logging except that Transaction Durability guarantees that all successfully completed transactions are committed to the data files in the event of a system crash.

For a full discussion of transaction logging and durability, see Transaction Logging and Durability.

Note: When you turn Transaction Durability on, some files may not be able to support the feature. A file must contain at least one unique key, or when it was created, the configuration setting System Data must have been set to “Yes” or “If Needed.” Otherwise, any changes to the file are not written to the transaction log. For more information about transaction durability and system data, see PSQL Programmer's Guide.

Because System Data does not affect existing files, you may need to recreate files that do not have a unique key and were not created with System Data turned on. Be sure to turn on System Data before recreating these files.

Caution: Gateway locator files allow different engines to manage files in different directories on the same file server. If your database contains data files in different directories, you must be sure that the same database engine manages all the data files in the database. If you have more than one database engine managing files within the same database, database integrity and transaction atomicity are not guaranteed. For more information on how to avoid this potential problem, see Redirecting Locator Files.

Related Settings

See more information on similar and related settings under Transaction Logging.

Transaction Logging


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	On	None	Yes

This setting controls whether the MicroKernel ensures atomicity of transactions by logging all operations that affect the data files.

If the related setting, Transaction Durability, is turned on, then logging takes place automatically, and the Transaction Logging setting is ignored.

For a full discussion of transaction logging and durability, see Transaction Logging and Durability.

Note When you turn Transaction Logging on, some files may not be able to support the feature. A file must contain at least one unique key, or when it was created, the configuration setting System Data must have been set to “Yes” or “If Needed.” Otherwise, any changes to the file are not written to the transaction log. For more information about transaction durability and system data, see PSQL Programmer's Guide in the Developer Reference.

Because System Data does not affect existing files, you may need to re-create files that do not have a unique key and were not created with System Data turned on. Be sure to turn on System Data before re-creating these files.

Caution: Do not turn off Transaction Logging unless your database does not require transaction atomicity among data files. Database integrity for multifile databases cannot be guaranteed if Transaction Logging is turned off.

Do not turn off Transaction Logging unless doing so is supported by your application vendor.

Related Settings

The server configuration setting Transaction Durability is similar to Transaction Logging, but provides a higher level of data safety along with a lower level of performance. The server configuration settings Log Buffer Size and Transaction Log Size are related to Transaction Logging. Log Buffer Size allows you to configure the balance between transaction recoverability and performance. The larger the log buffer, the fewer times it is written to disk, and thus the greater the performance. However, database changes that are in the log buffer are not durable through a system failure.

Transaction Log Size controls how large each log file segment gets before a new segment is started.

Note that all of these settings are ignored if Btrieve or SQL transactions are not being used.

Wait Lock Timeout


Type	Range	Default	Units	Requires Engine Restart
Numeric	0 - 2147483647	30000	milliseconds	Yes

The database engine and its clients use a coordinated retry mechanism when a record lock conflict occurs. If the engine cannot obtain a lock on every requested record within the duration for wait lock timeout, the engine returns control to the application with an appropriate status code.

Wait Lock Timeout Benefits

Wait lock timeout provides the following benefits if a lock conflict occurs:

•

Allows the database engine to continue processing requests while waiting for the lock to release.

•

Improves thread queuing if multiple threads are waiting for the locked resource.

•

Improves network performance by reducing network traffic.

When Wait Lock Timeouts Apply

Wait lock timeouts apply to only two kinds of applications:

•

Any application that uses the Relational Engine

•

Btrieve applications performing a change operation that does not need to be retried. Such applications receive a lock error within either one second or the wait lock timeout value, whichever is less.

Wait lock timeouts do not usually apply to Btrieve applications that use the MicroKernel Engine through a PSQL client on Windows, Linux, or OS X. Instead, such applications do one of the following:

•

Receive a page or lock conflict error immediately for read operations with “no wait” lock bias (200, 400) or a write operation to which a “no write wait” lock bias (500) has been applied.

•

Receive a page or lock conflict error within the lesser of either 1 second or the wait lock timeout value for nontransactional write operations where a “no write wait” lock bias (500) has not been applied.

•

Wait indefinitely if the operation involved is a read operation with a “wait” lock bias (100, 300).

•

Wait indefinitely if the operation involved is a write operation inside of a transaction and a “no write wait” lock bias (500) has not been applied to either the operation or the transaction.

On receiving a page or lock conflict error, the application may determine how to handle the conflict by retrying, waiting, or other options.

Handling of Page Locks

The MicroKernel Engine API provides controls to handle record lock situations. See Btrieve API Guide in the PSQL SDK documentation for a complete discussion. In brief, here are the control mechanisms:

•

Explicit record locks – You can add lock biases (100, 200, 300, or 400) to read operations when reading records to specify whether to wait. You may also apply these biases to the Begin Transaction operation.

•

Implicit page locks during a transaction – Most page lock conflicts are avoided because of row-level locking, but you can add lock bias 500 to your transactions to avoid waiting when a page lock occurs.

•

Exclusive file locks – Use concurrent transactions to avoid explicit file locks. If a file cannot be opened exclusively, the request always returns immediately.

Debugging

Debugging contains the following configuration settings:

•

Number of Bytes from Data Buffer

•

Number of Bytes from Key Buffer

•

Select Operations

•

Trace File Location

•

Trace Operation

Number of Bytes from Data Buffer


Type	Range	Default	Units	Requires Engine Restart
Numeric	0 - 65535	128	bytes	No

This setting specifies the size of the data buffer that the MicroKernel writes to the trace file. The Trace Operation setting must be set to On to use this setting. The size you specify depends on the nature of your tracing needs (whether you need to see the entire data buffer contents or just enough of the buffer contents to identify a record).

Number of Bytes from Key Buffer


Type	Range	Default	Units	Requires Engine Restart
Numeric	0 - 255	128	bytes	No

This setting specifies the size of the key buffer that the MicroKernel writes to the trace file. The Trace Operation setting must be set to On to use this setting. The size you specify depends on the nature of your tracing needs (whether you need to see the entire key buffer contents or just enough of the buffer contents to identify a key).

Select Operations


Type	Range	Default	Units	Requires Engine Restart
Multiselect	See below	All	None	No

The Selected list displays the available Btrieve API operation codes that are traced. Select from the list the desired operations to trace.

•

Abort Transaction (21)

•

Get Position (22)

•

Begin Transaction (19)

•

Get Previous (7)

•

Clear Owner (30)

•

Get Previous Extended (37)

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

Get Direct/Chunk (23)

•

Step Next (24)

•

Get Directory (18)

•

Step Next Extended (38)

•

•

•

•

•

•

Step Previous Extended (39)

•

Get Greater or Equal (9)

•

Unlock (27)

•

Get Last (13)

•

Update (3)

•

Get Less or Equal (11)

•

•

•

•

•

Get Next Extended (36)

Trace File Location


Type	Range	Default	Units	Requires Engine Restart
String	not applicable	file_path\PSQL\bin\mkde.tra	None	No

This setting specifies the trace file to which the MicroKernel writes trace information. The file name must include a drive or volume specification and path or use a UNC path. If you do not want the trace file in the default location, enter a different path or file name.

For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started with PSQL.

Note: Do not use the same trace file name for ODBC tracing and MicroKernel tracing.

Trace Operation


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	No

This setting enables or disables the trace feature, which allows you to trace each Btrieve API call and save the results to a file. Developers can use tracing to debug applications. The MicroKernel writes to the trace file using forced write mode, which ensures that data gets written to the file even if the MicroKernel unloads abnormally. The MicroKernel’s performance can be severely impacted, depending on the frequency of incoming requests. If you enable this option, you must specify a Trace File.

Note: You do not need to restart the engine in order to start and stop tracing. You can turn tracing on or off during runtime and apply the changes directly to the engine. If you receive a message from Configuration indicating that you must restart the engine for changes to take effect, you may safely ignore the message for this setting.

Directories

Directories contains the following configuration settings:

•

DBNames Configuration Location

•

Transaction Log Directory

•

Working Directory

DBNames Configuration Location


Type	Range	Default	Units	Requires Engine Restart
String	not applicable	Varies	None	Yes

This setting specifies the path to an alternate location for the DBNames configuration file.

For Server engines, this is a local file path, not a directory path. For Workgroup engines this could be a remote path that is accessible to the Workgroup MicroKernel. The defaults vary depending upon your particular engine platform.

•

Windows platforms: application_data_directory\

•

Linux or OS X server: /usr/local/psql/etc

If you do not want the configuration file in the default location, enter a valid path.

Transaction Log Directory


Type	Range	Default	Units	Requires Engine Restart
String	not applicable	Varies	None	Yes

This setting specifies the location the MicroKernel uses to store the transaction log. The path must be a valid path and include a drive or volume specification or UNC path. The defaults vary depending upon your operating system.

The engine ignores this setting unless Transaction Durability or Transaction Logging is turned on.

Caution: Do not use the same directory for multiple database engines. For example, it may seem convenient to set a remote server directory as the transaction log directory for more than one engine. However, the engines will be unable to determine which transaction log segments are used by which engine if it becomes necessary to do a log roll forward

If your database engine sees heavy use, you should configure your system to maintain the transaction logs on a separate physical volume from the volume where the data files are located. Under heavy load, performance is typically better when the log writes and data file writes are split across different drives instead of competing for I/O bandwidth on a single drive. For a full discussion of transaction logging, see Transaction Logging and Durability.

Working Directory


Type	Range	Default	Units	Requires Engine Restart
String	not applicable	Same directory as data file	None	Yes

This setting specifies the location of the MicroKernel working directory, which is used to store temporary files in operations such as building large indexes. If disk space is limited on certain volumes, you can use this option to specify a working directory on a volume with adequate space.

There is no default value specified; however, if you do not specify a working directory, the default will be the location of the data file. To specify a fixed working directory, enter a path in the Value text box. The path must include a drive or volume specification or a UNC path.

Information

Information lists the following display-only items:


Display-only Item	Discussion
Server name	The name of the machine on which the database engine is running.
Engine version	The release version of the database engine.
Engine type	The product category of the database engine, such as Server or Workgroup.

Memory Usage

Memory Usage contains the following configuration settings:

•

Allocate Resources at Startup

•

Back to Minimal State if Inactive

•

Minimal State Delay

•

Sort Buffer Size

•

System Cache

Allocate Resources at Startup


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	Yes

This setting instructs the MicroKernel to allocate resources, including threads and memory buffers, when the MicroKernel is started. The resources allocated at startup includes the background threads in addition to the L1 cache. PSQL components automatically allocate resources as needed. Therefore, in most cases, this setting can be off (the default).

With the setting off, the MicroKernel does not allocate any resources until the first operation request. If your server system supports a large number of users, you may prefer to have this setting on.

When first set to on, the setting may not produce any noticeable difference in the memory allocated because of how Windows behaves. When the MicroKernel allocates its L1 cache, the Windows operating system simply reserves pages of memory and does not actually commit them to the MicroKernel. Later, when the MicroKernel actually accesses cache memory, Windows then commits actual physical pages and the memory usage of PSQL components (such as ntdbsmgr or w3dbsmgr) increases.

If you look at the "VM Size" column in Windows Task Manager, you can see the memory value changing when the L1 cache gets accessed. You should also be able to see a difference in the number of threads when the background threads are accessed.

Back to Minimal State if Inactive


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	Yes

This setting causes the MicroKernel to free considerable memory and thread resources to the system and return to a minimal state after a certain amount of time without any active clients. The time interval is specified by the value of Minimal State Delay. The MicroKernel reallocates resources when another client becomes active.

Minimal State Delay


Type	Range	Default	Units	Requires Engine Restart
Numeric	0 - 2147483647	300	milliseconds	Yes

This setting specifies how long the MicroKernel waits during a period of inactivity before returning to a minimal state. (This is the initial state in which the MicroKernel begins.) By returning to a minimal state, the MicroKernel frees considerable memory and thread resources to the system. In some cases, you may not want the MicroKernel to return to a minimal state. For example, you may be running a batch file that uses the MicroKernel repeatedly. The MicroKernel reallocates resources when another client becomes active.

This setting is ignored if the value of Back to Minimal State if Inactive is set to Off (the default).

Sort Buffer Size


Type	Range	Default	Units	Requires Engine Restart
Numeric	0 - limited by memory	0	bytes	Yes

This setting specifies the maximum amount of memory (in kilobytes) that the MicroKernel dynamically allocates and de-allocates for sorting purposes during run-time creation of indexes.

If the memory required for sorting exceeds the size specified or is greater than 60 percent of the available process memory, the MicroKernel creates a temporary file. The amount of available memory for a process is a dynamic value and varies according to system configuration and load. If you specify 0 kilobytes, the MicroKernel allocates as much memory as needed, up to 60 percent of the available memory.

System Cache


Type	Range	Default	Units	Requires Engine Restart
Boolean	On, Off	Off (Server engine) On (Workgroup engine)	None	Yes

This option specifies whether the MicroKernel should use the system cache in addition to the MicroKernel’s own database cache, as set using the configuration parameter Cache Allocation Size.

If you are using the L2 cache, you should set System Cache to Off. Check the setting of Max MicroKernel Memory Usage. When Max MicroKernel Memory Usage is set to a value greater than zero, you are using L2 cache.

If you are not using L2 cache, performance can be enhanced by turning on System Cache. The MicroKernel relies on the system cache to organize and group pages to be written. It delays a flush long enough to allow the system cache to write the pages to disk in a more efficient manner. However, if your server has an advanced self-cached disk array, you might achieve better performance by setting System Cache to Off.

For Windows Server only, you can use the Paging File and Process objects in the Windows Performance Monitor utility to determine whether the Windows system cache is being used effectively. For the NTDBSMGR instance, monitor the % Usage and % Usage Peak in the Page File object and the Page Faults/Second and Page File Bytes in the Process object.

Performance Tuning

Performance Tuning contains the following configuration settings:

•

Automatic Defragmentation

•

Cache Allocation Size

•

Communications Threads

•

File Growth Factor

•

Index Balancing

•

Limit Segment Size to 2 GB

•

Log Buffer Size

•

Max MicroKernel Memory Usage

•

Number of Input/Output Threads

•

Transaction Log Size

Automatic Defragmentation


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	No

This setting turns automatic defragmentation on or off. When it is on, the feature works as follows:

•

One hour after the setting is turned on, the engine begins discovery of files to defragment.

•

The engine first identifies files active since the last engine restart.

•

The engine then tests those files for any of the following criteria.


Analysis	Greater Than or Equal to
Fragmented	15%
Unused	15%
Out of Order	5%

These criteria are the same as the ones defined under the Watch List topic. The advantage of automatic defragmentation is that you do not have to manually select files, add them to the Watch List, and then manually check and defragment them.

•

Defragmentation begins immediately on the first file that meets any of the criteria.

•

The engine defragments one file at a time. When it finishes with a file, it waits one minute before starting to defragment the next one.

•

For efficiency, files matching the following criteria are ignored:

•

Already defragmented in the last 24 hours

•

Smaller than 10 MB

If you want to defragment such files, add them to the Watch List for manual monitoring or use the dbdefrag utility.

•

When all files meeting automatic defragmentation criteria are done, the engine waits 60 minutes before beginning the next round of file discovery.

•

Messages in pvsw.log state whether a file was defragmented automatically or manually.

Note: If an active file is closed and its pages flushed from cache, the engine no longer considers it a candidate for automatic defragmenting. When the file is next opened, the engine discovers it again.

For more information about defragmenting data, see Monitoring Data File Fragmentation.

Cache Allocation Size


Type	Range	Default	Units	Requires Engine Restart
Numeric	1 megabyte to the amount limited by memory (see Note below)	Initialized dynamically at first start-up	megabytes (see Note below)	Yes

This setting specifies the size of the Level 1 cache that the MicroKernel allocates. The MicroKernel uses this cache when accessing any data files.

Speaking very generally, overall performance is usually best when the Cache Allocation Size is a value less than 40% of the physical memory on the system, and the Configuration setting Max MicroKernel Memory Usage is set to a value greater than 40%. Your exact optimal settings will depend on the size of your data files, the number of other applications running on the system, and the amount of memory installed in the computer.

Server Engine

On Windows, this setting is initially set to 20% of physical memory by the database engine the first time it starts and it writes that value to the Registry. After that, whenever the engine starts, it reads the value from the Registry. Changing the value using Configuration updates the value in the Registry. If you add or remove memory from the system, you must modify this setting to take best advantage of the new amount of memory available.

To optimize your performance, allocate a cache size no larger than the sum of the sizes of the files you are using. However, be careful not to take all available memory, especially when the server is running other applications. You cannot improve performance—and may waste memory—by specifying a value higher than you need.

Workgroup Engine and Client Cache

The database engine initially sets this value the first time it starts and writes the value to the Registry. The initial value is set to 20% of physical memory. The maximum size of the cache depends on the amount of memory in the system, but the engine sets an initial maximum of 64 MB. After the Registry setting is initialized, whenever the engine starts, it reads the value from the Registry. The engine never re-calculates the setting. Changing the value using Configuration updates the value in the Registry. If you add or remove memory from the system, you must modify this setting manually to take best advantage of the new amount of memory available.

Client Cache

This information also applies to the client software (Client cache) if the configuration setting Use Cache Engine is turned on.

Note: If you use PSQL Clients prior to PSQL v10, the value for Fcache allocation size must be specified in bytes, with a minimum of 64 KB (65,536 bytes). The maximum is limited by the amount of memory.

Communications Threads


Type	Range	Default	Units	Requires Engine Restart
Numeric	num_cores to 256	num_cores, where num_cores is the number of processors in the machine on which the database engine is running	None	Yes

This setting specifies how many threads the MicroKernel initially spawns to handle requests from remote clients. Communication threads are the elements that actually perform Btrieve operations on behalf of the requesting remote client process. In this way they are very similar to worker threads. Communications threads increase dynamically as needed up the maximum range allowed. The maximum is 256.

The Communications Threads setting can help improve scaling under certain conditions. For example, if you have many clients performing operations (typically writes) on one file, a lower setting should improve scalability. The lower number of threads prevents context switching on system resources. Another condition that this setting may improve is a slowdown caused by thrashing among large numbers of worker threads. Worker threads are dynamically created only if all the existing threads are waiting on record or file locks.

File Growth Factor


Type	Range	Default	Units	Requires Engine Restart
Numeric	0-100	15	Percent	Yes

This value specifies the approximate percentage of free pages to maintain in version 8.x and later format data files. The setting does not apply to any previous file format versions. The MicroKernel uses this value to decide whether to extend a file or use free pages first. The database engine uses turbo write accelerator so the performance of disk writes can be expected to improve as the number of free pages increases within the file. This improvement is due to the engine’s ability to write multiple contiguous file pages on disk. Thus, disk write performance is a trade-off against file size. However, maintaining too many free pages in a file can actually reduce overall performance.

To maintain a certain amount of contiguous free pages in a data file, the database engine must periodically expand the file. Keep in mind that the file size effects of this setting are exponential. For example, if you start with a file that has no free pages and you specify a File Growth Factor value of 50%, the file will eventually double in size. If you specify a File Growth Factor value of 75%, the file will quadruple in size. A value of 90% will cause the file to grow by as much as 10 times.

Note that only completely unused pages are counted as empty, so 15% empty pages does not mean that 15% of the file is unused, as some pages may not be completely full.

This value is only a hint to the MicroKernel. Depending on how heavily a given file is being updated, the actual percentage of empty space may be much less at any given moment.

This setting is not applicable to pre-8.x format files. These older files also have empty pages and the percentage of empty pages varies with the activity on the file.

Index Balancing


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	Off	None	Yes

This setting controls whether the MicroKernel performs index balancing. Index balancing increases performance on read operations. However, when you enable this option, the MicroKernel requires extra time and may require more disk I/O during insert, update, and delete operations. For more information about index balancing, see PSQL Programmer's Guide.

Limit Segment Size to 2 GB


Type	Range	Default	Units	Requires Engine Restart
Boolean	On Off	On	None	Yes

This setting specifies that a data file is to be automatically broken into 2 GB operating system file segments as its size passes that boundary. If set to On, this setting specifies that you want data files divided into 2 GB segments. If set to Off, this setting specifies that data files will remain a single, nonsegmented file. The advantage of using a larger nonsegmented file is more efficient disk I/O. Therefore, you can expect increased performance.

Any nonsegmented files are subject to the limit on file size specified by your operating system. If a previously created file is already segmented, that segmentation remains on the file.

See also Automatic Upgrade of File Version for related information.

Log Buffer Size


Type	Range	Default	Units	Requires Engine Restart
Numeric	262144 - limited by memory	The smaller value of the following: (physical memory in MB / 256) or 8 MB	bytes	Yes

This setting specifies the size of both the transaction log buffer and the archival log buffer that the MicroKernel uses. You can enhance performance by increasing the log buffer size, because the MicroKernel writes the log information to disk less frequently.

Note: If you set Log Buffer Size to a value greater than that of Transaction Log Size, then the MicroKernel automatically increments Transaction Log Size to the value you specified for Log Buffer Size.

Max MicroKernel Memory Usage


Type	Range	Default	Units	Requires Engine Restart
Numeric	0-100	60	Percent	No

This setting specifies the maximum proportion of total physical memory that the MicroKernel is allowed to consume. L1, L2, and all miscellaneous memory usage by the MicroKernel are included (Relational Engine is not included). The database engine uses less if the specified proportion is not needed or not available.

If the value zero (0) is specified, then dynamic caching is turned off. In this case, the only cache available is L1, the size of which is specified by Cache Allocation Size. If you have a dedicated database server machine, then you should set Max MicroKernel Memory Usage to the maximum value, or whatever proportion of memory is not occupied by the operating system. If you run other applications on your database server and you need to balance performance between all of these, then you should set this value lower so that the database cache does not compete as much with the other applications when using available memory.

Note: If Cache Allocation Size is set to a higher amount of physical memory than Max MicroKernel Memory Usage, then Cache Allocation Size takes precedence. For example, a machine has 1 GB of physical memory and you set Cache Allocation Size to 600 MB and Max MicroKernel Memory Usage to 40%. The L1 cache is allocated 600 MB of memory. Since Cache Allocation Size is higher, it takes precedence and the amount of memory allocated to the L1 cache exceeds the value specified for Max MicroKernel Memory Usage.

Use the following equation to determine the approximate value for Max MicroKernel Memory Usage.

(L1_cache_size + internal_allocations + L2_cache_size / size_of_physical_memory) * 100

where:

L1_cache_size is the Cache Allocation Size

internal_allocations is approximately 25% of the size of the L1 cache

L2_cache_size is the amount of memory that expands and contracts based on memory load of the system

size_of_physical_memory is the amount of memory installed in the machine

For more information on tuning performance, see Tuning Performance.

Number of Input/Output Threads


Type	Range	Default	Units	Requires Engine Restart
Numeric	1 to 1024	32	None	Yes

This setting specifies how many background I/O threads the MicroKernel spawns. These threads are responsible for writing all pages from the MicroKernel’s cache to the file on disk in an atomic and consistent manner. They also are responsible for initially opening a file and reading the File Control Record. Most of the other reads are done by local worker threads and communication threads. When the MicroKernel updates or writes to data files, it assigns each file to a particular I/O thread sequentially. When it reaches the last thread, the MicroKernel starts over until all data files have been assigned to a background thread. Because the MicroKernel does not spawn additional I/O threads as needed, specify the maximum number of I/O threads you anticipate needing.

For best performance, set this value based on the average number of open files. Monitor shows the current and peak number of files open. If your database has an average of 256 files open, then the default of 32 I/O threads makes each thread responsible for 8 files. A good rule of thumb is to have about 8 files per I/O thread. For example, if your average number of open files is 400, you should use about 50 I/O threads. Specifying a value higher than 64 may degrade performance, but that depends on the capabilities of the system.

Note: No accurate way is available to calculate the appropriate number of I/O threads because this setting depends on the machine’s characteristics, OS configuration, and the database engine’s planned work load.

Transaction Log Size


Type	Range	Default	Units	Requires Engine Restart
Numeric	65536 - limited by disk space	2 times the Log Buffer Size	bytes	Yes

This setting specifies the maximum size of a transaction log segment. When the log file reaches its size limit, the MicroKernel closes the old log segment file and starts a new one. You might want to limit the size of your transaction log segments, because this reduces the disk space that the MicroKernel uses temporarily. However, limiting the size of the transaction log segments does require more processing by the MicroKernel and can decrease performance, because it has to close and create log segments more frequently.

Note: If you set the value for this option less than the value you specified for Log Buffer Size, the Database Engine automatically adjusts Transaction Log Size by setting it to the value of Log Buffer Size.