Postgres database is having delayed response

[Ashritha]

Hi,

Querying database and value stream taking long time to respond.

Below is the example of querying database that co0ntains 62 entries. Taking 7.13s to respond. 

The server was working fine 2 days ago. Within milliseconds services were responding.

Because of delay in querying all the services throwing timeout error and failed error.

Following steps are already considered:

1. Disabled a scheduler that was queueing up threads (Created 10 days ago)
2. Restarted ThingWorx foundation and database
3. Application logs does not have any data related to it.
4. There were no code changes done in past 5 days 
5. Script Timeout increased to 3600s
6. CPU , Disk and Memory status looks fine

System Details:

Application : ThingWorx Foundation - 9.1.0

Operating System: CentOS Linux 7 (Core)
Database : PostgresDB

Memory, CPU , Diskspace:

ThingWorx server:

8008648 K total memory
2697844 K used memory
3781920 K active memory
1199188 K inactive memory
2507472 K free memory
82700 K buffer memory
2720632 K swap cache
0 K total swap
0 K used swap
0 K free swap
93072005 non-nice user cpu ticks
978 nice user cpu ticks
77834557 system cpu ticks
10034483535 idle cpu ticks
793565 IO-wait cpu ticks
0 IRQ cpu ticks
347919 softirq cpu ticks
7431254 stolen cpu ticks
8743194 pages paged in
187003280 pages paged out
0 pages swapped in
0 pages swapped out
4038115232 interrupts
3440227288 CPU context switches
1612011373 boot time
29430064 forks

Database Server:

3880176 K total memory
454784 K used memory
1976572 K active memory
1541996 K inactive memory
141972 K free memory
260 K buffer memory
3283160 K swap cache
0 K total swap
0 K used swap
0 K free swap
192783285 non-nice user cpu ticks
1458 nice user cpu ticks
25476518 system cpu ticks
5117884375 idle cpu ticks
8882670 IO-wait cpu ticks
0 IRQ cpu ticks
1602227 softirq cpu ticks
191860 stolen cpu ticks
44717398946 pages paged in
35325522221 pages paged out
0 pages swapped in
0 pages swapped out
1898092808 interrupts
588968486 CPU context switches
1611120281 boot time
28155205 forks

ThingWorx Composer loads as earlier. Please help me with this issue.

Thank you !!

[VladimirN · Accepted solution]

A somewhat similar question - "If i execute any query service it will take long time respond": https://community.ptc.com/t5/ThingWorx-Developers/If-i-execute-any-query-service-it-will-take-long-t...

Also some helpful articles:

• "Tuning the PostgreSQL Database to Optimize ThingWorx Performance": https://www.ptc.com/en/support/article/CS316989
• "REST API calls to a PostgreSQL Secondary Persistence Provider and connections to ThingWorx Composer are hanging on ThingWorx Platform": https://www.ptc.com/en/support/article/CS344049