Restart PSQL Engine to Fix Slow IO Performance Issue
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.elliott.com/knowledgebase/articles/1975899-feature-view-database-activities-through-control
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:
- We will first restart PSQL Services to see if the performance is resolved.
- If restarting PSQL Services does not resolve the issue, then we will reboot the server.
- 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:
- Right click on "Actian PSQL Relational Engine" and choose "stop."
- 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.
- When both the relational and transactional engines are stopped, right click on "Actian PSQL Transactional Engine" and choose "Start."
- 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