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

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History?
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance

Feedback and Knowledge Base