Restart PSQL Engine to Fix Slow IO Performance Issue

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 minutes to complete, but is now taking 1 hour and 40 minutes to complete. This can be observed in the defer processing log. See sample screen below:

In above scenario, the IMQACOMP task running at 03/24/22 6:09 AM used to take 10 minutes. It started to take about 1 hour 40 minutes for the same task after 10:30 AM.

Investigation

In Elliott 8.5, when running a report, if a user has sufficient privileges, that user can press the CTL-SHIFT-D key while the report is running, and the system can then bring up a list of Database IO Activities.  You can use this to measure the database IO performance.  See this KB article for more details: https://support.netcellent.com/portal/en/kb/articles/feature-view-database-activities-through-control-shift-d-key

To investigate this problem further, we decided to login as an Elliott user who has sufficient privilege to use the CTL-SHIFT-D key and run the same task through the regular report printing.  In the middle, we pressed CTL-SHIFT-D and the following window was brought up:

In the above scenario, you can see that in about 0.03 second, Elliott performs 10 IOs.  This report is running on the PSQL server directly. This is considered very slow.  Generally speaking, depending on the server performance, Elliott should be able to process between 30 and 110 IOs in 0.01 second when running on the PSQL server directly. See the following sample screen:

Solution

Our conclusion is that there's something happening to the PSQL server. It could be issue from the PSQL database engine, or it could be an issue from the server OS environment.  So here is our plan:
  1. We will first restart PSQL Services to see if the performance is resolved.
  2. If restarting PSQL Services does not resolve the issue, then we will reboot the server.
  3. If rebooting the server does not resolve the issue, then we need to find out what other processes may be interfering with server performance. For example, antiviral software can cause problems. Also, is any server hardware showing signs of trouble?  Unfortunately, investigation from this point forward will be more difficult. 

Restarting PSQL Engine Services

To restart PSQL services, you will login to the PSQL server as an admin user.  Make sure all users are logged off.  Bring up "Services" and look for "Actian PSQL Relational Engine." See sample screen below:

  1. Right click on "Actian PSQL Relational Engine" and choose "stop."
  2. Right click on "Actian PSQL Transactional Engine" and choose "stop." This may take a while --  sometimes up to a minute or two. Just be patient and wait.
  3. When both the relational and transactional engines are stopped, right click on "Actian PSQL Transactional Engine" and choose "Start."
  4. Right click on "Actian PSQL Relational Engine" and choose "Start."
In this case, we were lucky that the Elliott IO performance was restored to normal speed after restarting the PSQL engine, so we didn't have to reboot the server.  Why the PSQL engine IO performance suddenly dropped significantly is unknown. This is the first time we observed this issue, so we believe this scenario should be rare.


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

    • 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 ...

    • 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 ...

    • How to Use Procdump.exe to Create a Memory Dump for PSQL Engine

      Released Date: 7/10/2017 If your PSQL server crashes from time to time, then it is necessary to get a memory dump of the PSQL engine processor and send that to Actian tech support for further analysis. The following procedure outlines how to do this. ...

    • How to Fix and Protect Audit Master Data from Corruption

      Release Date: 08/12/2021 Version: 7.x & Up In this article, we are going to talk about two issues related to Audit Master database corruption: How to fix the audit master data when it becomes corrupted How to prevent audit master data from becoming ...