Do I Need to Change PSQL Server Engine Default Parameters After Installing It?

Q - I am installing a PSQL server engine for my client.  Do I need to change any PSQL parameters after the installation?

A - Generally speaking, you don't need to change PSQL parameters after the installation.  There are two optional changes you can consider:
  1. Change the Btrieve Security Mode to "Mixed." This is necessary if you intend to implement NTFS security for Elliott. Please reference the following article for more detail on implementing NTFS security for Elliott: http://support.elliott.com/knowledgebase/articles/654601-elliott-8-directory-structure-and-ntfs-rights.
  2. By default, any Btrieve file size is limited to 2GB.  If the file exceeds 2GB, it will create another segment file with extension like ^01.  Then another one with ^02, ^03, and so on and so forth.  The system supports up to a maximum 32 Btrieve segment files with a maximum file size of 64GB. We suggest instead of limiting each segment to 2GB, that you allow the system to create Btrieve files larger than 2GB.
Keep in mind that when you perform these two changes, you will need to restart the PSQL engine afterward in order for the change parameters to take effect.  So you need to make sure that nobody is using the PSQL engine while you make these configuration changes.

Change Btrieve Security Mode to "Mixed"
To change the Btrieve Security Mode to "Mixed," you need to bring up the PSQL Control Center.  Under the "Engines" node, expand the "Database" node, right click on the "DEFAULTDB" database and choose "Properties."  See sample screen below: 



In the Properties window for DEFAULTDB, highlight "Security" on the left side and click on the "Btrieve Security" tab on the right side. Change the default selection from "Classic" to "Mixed."  See sample screen below:



Click Apply or OK to save.  The parameter you change here will not go into effect until you restart the PSQL engine. If you don't see DEFAULTDB, then there's something wrong with your installation. Please contact us before proceeding.


Turn Off Btrieve File Size 2GB Limitation
To turn off the Btrieve file size limitation, you need to bring up the PSQL Control Center.  Under the "Engines" node, right click on the server node and choose "Properties."  See sample screen below: 
  


In the properties window, highlight "Performance tuning" on the left.  By default, "Limit Segement Size to 2GB" is checked.  Un-check it and click "OK" or "Apply" to save. See sample screen below:



You will receive a message asking if you wish to restart the PSQL server engine. You can either choose to do it now or later to let the new parameter take effect.

Cache Memory Consideration
Cache memory allows PSQL to store the database file in server memory.  This allows PSQL to retrieve data from the server memory (fast) instead of going to disk (slow). So the amount of cache memory available will affect PSQL server performance greatly. By default, PSQL will use 20% of the server memory as level 1 cache and 60% as the level 2 cache.  The difference between level 1 and level 2 is that level 2 cache will compress the database files before storing it in the level 2 cache.  While this allows PSQL to store large amounts of cache data in level 2 cache, but it is slower due to the compression.

If your server has enough memory to cache your entire database (total *.BTR files size,) then you should consider using level 1 cache only.  The following is an example of the default setting of a server with 64GB of memory.



Cache Allocation Size in MB is the level 1 cache.  Since the server has 64GB of RAM, it is equivalent to 64 * 1024 = 65,536MB.  20% of 64GB = 65,536MB * 20% = 13,107MB which is reflected in the above example.

Max MicroKernel Memory Usage is the level 2 cache which defaults to 60 (percent).  

Therefore, by default, PSQL set the total cache memory of level 1 + level 2 at 80% of the server memory. Generally speaking, this setting is fine if you dedicate this server for PSQL purposes.  

On the other hand, if you have enough server memory to fully cache your database, then you should consider changing the level 1 cache to 80% of the server memory and level 2 cache to zero.  In this case, this server has 64GB of RAM, which makes 80% equal to 65,536MB * 80% = 52,428MB.  See sample screen below:




EMK

Feedback and Knowledge Base