Overview

Some customers complain of poor performance with System 5. If after verifying that workstations and server meet the minimum specifications to run Pervasive and System 5, and that there are no other issues with the network in general, then it might help to make a couple of adjustments to Pervasive. The following recommendations are taken from the PSQLv12 Advanced Operations Guide, available here.

This wiki is not an extensive guide, and does not cover such issues as monitoring the performance of the database, database fragmentation or disk I/O, memory and CPU bottlenecks. For more in-depth information, consult the PSQLv12 Advanced Operations Guide.

Edit

Standard Server Configuration

Before you begin to modify any Pervasive settings, first ensure that the server is correctly configured.

1. Ensure that the Power Saving Features on the Network Interface Card (NIC) are turned OFF.

2. It is recommended to disable TCP/IPv6.

Be careful with disabling TCP/IPv6 as it has been known to cause problems with Windows Small Business Server. It is best to leave this option untouched until a conversation has taken place with the customer's IT person, and if it is agreed to disable this option, have their IT person do it themselves.

3. Ensure that a Static IP address is assigned.

4. Ensure that the Server's Power Plan Options is set to High Performance.

If the server is virtualised (e.g., Hyper-V, VMware, XenDesktop, etc.), ensure that the above steps are completed on both the physical and virtual servers.
Edit

Standard Workstation Configuration

Before you begin to modify any Pervasive settings, first ensure that the workstation is correctly configured.

1. Ensure that the Power Saving Features on the Network Interface Card (NIC) are turned OFF.

2. It is recommended to disable TCP/IPv6.

It is best to leave this option untouched until a conversation has taken place with the customer's IT person, and if it is agreed to disable this option, have their IT person do it themselves.

3. It is recommended that a Static IP address is assigned in a Client-Server Environment. However, this should be mandatory in a Peer-to-Peer environment.

4. Ensure that the Workstation's Power Plan Options is set to High Performance.

Edit

Before You Modify Configuration Parameters

The following sections assume the following:

1. PSQL Control Center (PCC) is already running.

Make sure that PSQL Control Center (PCC) has been started as an Administrator

2. You have appropriate (i.e., Administrative) operating system privileges to configure the engine.

3. For some engine settings, you must restart the database engines after making configuration changes, so it is recommended that everyone be logged out of System 5.

Edit

Client Parameters

You must be connected to the client machine to change the client parameters. You must change the parameters at each workstation whose settings you wish to change.

Edit

To minimize client-side connection delays

1. In PSQL Explorer, expand the Local Client node in the tree (click the expand icon to the left of the node).

2. Right-click MicroKernel Router.

3. Click Properties.

4. Click Communication Protocols in the tree.

5. For Supported Protocols, ensure that the desired protocols are selected (check marked) and the protocols not being used are not check marked.

6. Click Apply.

The client is now prevented from attempting to communicate on protocols that are not being used.

7. Click Access in the Properties tree.

8. If you are using only a remote Server or remote Workgroup engine, ensure that Use Local MicroKernel Engine is not check marked. (That is, it is set it to Off.)If you are using only a local Workgroup engine, ensure that Use Remote MicroKernel Engine is not check marked. (That is, it is set it to Off.)

If you sometimes use a Workgroup engine and you sometimes connect to a Server engine or a remote Workgroup engine, you must leave both settings On (check marked).

In such a mixed environment with shared and unshared data, you can set Gateway Durability to On at each client. This setting forces the client software to keep a list of the names of any machines on which it is unable to connect to a database engine. In order for the client software to determine no engine exists on a given computer, it waits for all of its network protocol requests to time out.

If your data is stored on a server that does not have a PSQL database engine on it, and you have set Use Remote MicroKernel Engine to Yes, the client must time out at least once to discover that there is no engine on that machine. Gateway Durability ensures that this time-out only happens the first time your application tries to access that data.

Using Gateway Durability fixes the network topology. If you later install a Server or Workgroup engine on the remote computer, you must turn off Gateway Durability on each client so that the list of computers without database engines is deleted (thus allowing you to connect to the new database engine). You may turn Gateway Durability back on immediately, but it starts with an empty list.

9. Click OK.

The client is now prevented from attempting to connect to any database engine types that are not in use.

Edit

Server Parameters

Edit

To minimize server-side connection delays

1. In PSQL Explorer, expand the Engines node in the tree (click the expand icon to the left of the node).

2. Right-click the database engine for which you want to specify configuration settings.

3. Click Properties.

4. For Supported Protocols, ensure that the desired protocols are selected (check marked) and the protocols not being used are not check marked.


5. Click Apply.

The server is now prevented from attempting to communicate on protocols that are not being used.

Ensure that at least one protocol you have selected for the Server configuration is the same one as selected for the Client configuration. Your client and server cannot communicate if they are not using the same protocol.

6. Click Memory Usage in the Properties tree.

7. Select Allocate Resources at Startup to set the value to On (check box selected).

This option specifies that the database engine should allocate all necessary memory when it starts up, rather than when the first connection request comes in. Choosing this value requires more memory, but from the client perspective allows the engine to become operational faster.

8. Ensure that Back to Minimal State if Inactive is set to Off (check box cleared).

This option specifies that the database engine should not release resources back to the operating system if the engine is inactive. All resources remain allocated and ready for use at the next client connection.

9. Click OK.

10. Click Yes to restart the engine for these changes to take effect.

Edit

Ensuring Adequate Resource Allocation

If your database server platform has adequate memory and CPU power, you should ensure that your database engine can take full advantage of the available hardware resources to service multiple clients and multiple data files most efficiently.

Edit

To configure multiple client and file handling

1. The setting Number of Input/Output Threads allows you to specify how many threads are available to handle file operations. (Click Performance Tuning on the server Properties tree.)


As a guideline, the value of this setting should be about 1/8 the number of files the application has open, on average. For example, if the application has 40 files open most of the time, I/O Threads should be set to 5. Specifying a value higher than 64 may degrade performance, but that depends on the capabilities of the system.

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.

Using Monitor, click on the Resource Usage tab. In the window that appears, the Files display shows the current and peak number of files open. You can generate an average reading by recording several Current values over time. Then you can specify an appropriate setting for I/O Threads based on the average value.