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

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: https://support.netcellent.com/portal/en/kb/articles/elliott-8-directory-structure-and-ntfs-rights-23-10-2024
  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


    • Related Articles

    • Slow PSQL Relational Engine Performance

      Release Date: 12/15/2017 Many users encounter situations where a particular relational engine SQL query can sometimes be slow. This can be complicated to diagnose due to a lot of reasons. Sometimes it's because of a SQL SELECT statement that's not ...

    • Configure PSQL Server Memory Usage

      Version: Any Release Date: 12/17/2021 Q - Can you tell us why PSQL process is taking up so much memory? Is there anything we can do? See sample screen below: A - PSQL server by default can take up about 60ish% of the server memory. This is ...

    • Setup of the PSQL 13 Report Engine

      Release Date: 8/14/2019 Version: PSQL 13 & up Benefits of PSQL 13 Report Engine The most significant feature in PSQL 13 is the report engine feature. Before PSQL 13, the Elliott application, web services and other third party applications that needed ...

    • New Elliott PSQL Server Processor and RAM Suggestions

      Q - We are going to upgrade to a new Windows Server 2012 R2 for PSQL 11 64-bit by the end of the year. Does PSQL take advantage of additional Intel processor cores and/or give some advice or algorithms on the amount of RAM that should be installed in ...

    • Restart PSQL Engine to Fix Slow IO Performance Issue

      Release Date: 03/26/2022 Version: PSQL 13 Summary A slow Elliott IO scenario has been observed. After restarting the PSQL services, the problem is resolved. Scenario An Elliott user reported that an Elliott defer processing function used to take 10 ...