Setup of the PSQL 13 Report Engine
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 to access Elliott data had to go through the PSQL Database Engine (DE). If you are a large organization with hundreds of Elliott users, you may find the PSQL Database Engine busy most of the time. This is especially true if your users create their own Crystal Reports and perform their own joining of tables. When joining of tables is not done correctly, it often results in poor performance and drags down Elliott's performance.
When faced with the PSQL DE server performance problem, many users chose to scale up their DE server. However, we noticed that the DE server performance does not improve much after 6-8 CPU. In PSQL 13, you now have the option to scale out. That is, you can install additional PSQL Report Engine (RE) servers to offload the burden of PSQL DE.
The name "Report Engine" suggests you can use RE for applications like Crystal Reports or Excel Query, which only read Elliott's data and do not perform updates. But the RE does support most of the relational operations including "SELECT," "UPDATE," "INSERT" or "DELETE." In relational operations you can treat it just like DE (even though the manufacturer suggests that you use RE for retrieving data only.) Generally speaking, operations that may affect database schema are not supported. For example, you can't use a relational operation to create a new database.
Hardware Requirements
Typically, we suggest the following hardware requirements for the server to host the PSQL Report Engine:
- CPU: 4-8 CPU.
- RAM Memory: It should have enough memory to fully cache your Elliott database. Just calculate the total size of the *.BTR files in your corresponding Elliott DATA folder, and use that as a basis to determine the RE server memory size. If you choose to virtualize your RE, then you can easily expand the memory size if necessary -- which we highly recommend that you do.
- Disk space: No physical Elliott data will reside on the RE server. We recommend that you create a C: partition that's big enough for the Windows OS and future server SP updates. Typically, 200-300GB for the C: partition will be enough.
Installation
Installation of the PSQL Report Engine is easy. Just follow the default suggestions of the installation wizard to complete the installation.
Setup
After the installation of RE, please bring up PSQL 13 Control Center. Expand the "Engines" node, right click on your RE server name, and choose Properties. See sample screen below:
Enter Name of Storage Server
In the Properties window, go to "Access" and enter the name of the Storage Server. This is the server where you install the PSQL 13 database engine (DE). Then click on Apply to update. See sample screen below:
Configure Cache Allocation
Right click on the node of your RE server on the left hand pane of the PSQL 13 Control Center, and choose Properties. In the Properties Window, go to "Performance turning" and enter the Cached Allocation Size in MB. We suggest that this should be anywhere between 50 - 80 percent of your RE server memory. The example below is 32GB = 32 * 1024MB = 32,768 MB.
Make sure you do not over or under allocate cache memory. Under allocation may result in less than desired performance. Over allocation may cause the server to run out of memory, which can make the server run even slower if Windows OS virtual memory disk swapping starts taking place. Monitor in Task Manager and make sure the actual server physical memory usage does not exceed 90%.
EMK