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:
  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:


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
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master
  32. PSQL 13, Micrsoft SQL Integration Service & Pervasive PSQL OLE DB Provider
  33. Your Firewall Needs to Allow Outbound Traffic to the Netherlands for PSQL Licensing Server Purposes
  34. A Case of Btrieve Error 046 on ARCRCLOG A/R Credit Card Log File
  35. A Support Case of Migrating to Different Version of DDF
  36. How to Clear the Message "Unable to Read your Users record (9/068)"
  37. Setup of the PSQL 13 Report Engine
  38. How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
  39. How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019
  40. Elliott Database Naming Convention
  41. What Does Btrieve Error 080 Mean?

Feedback and Knowledge Base