Basic workarounds for ADS memory issues
Article ID: KB0082863
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0.X |
Description
Basic workarounds for ADS memory issues
Issue/Introduction
Basic workarounds for ADS memory issues
Resolution
Possible workarounds for memory issues. Can we try this behaviors in DEV/TEST environment before implementing settings in PROD.
1) Increase the heap size in composite significantly until it has enough mem to satisfy the query as it is.
2) Choose to add this {option force_disk} to the sql so make the query use disk instead of heap space.
3) Modify and/or change the nature of the query to avoid that huge fetch of data from SqlServer.
=============
To monitor memory usage on a per-request basis:
• Go to Studio, and select the Requests This will display the requests
• Click on the Max Memory column header. This will order the requests based on which ones are consuming the most memory.
The next time that you come across a memory spike that’s in progress, try this:
Use one Studio instance to find out when a spike is on the upswing.
In a second Studio instance, select the Requests panel and click on the Max Memory tab to order the requests based on memory usage. Now see whether any of the requests displays steadily increasing memory whilst the spike is on the upswing.
In the event that Studio gets closed abruptly, an alternative is to try using the web manager. The web manager provides the same requests monitoring functionality as Studio. The manager provided a “Sort” menu option. You can use this option to display the requests in order of memory usage.
Additionally, You can take the ADS server's full thread dump using the "kill –3 <PID of ADS serveboot proces>" at 1 minute intervals.
Also you can use the Click the "free unused memory" button in Studio>Manager>Memory panel
This tells ADS to write a thread dump and a snapshot of the top 10 memory-intensive queries to the <ADS>\logs\cs_server_status.log file.
This is automatically done for you every 5 minutes like in your DEV server if you have the Administration>Configuration>Composite Server>Configuration>Debugging>Server Status Logging Period set to 5 minutes .
Then every 5 minutes or whenever you Click the "free unused memory" button in Studio>Manager>Memory panel the <ADS>\logs\cs_server_status.log file will be updated with the server statistics.
This server statistics has the sections like the following :
########################################################################
INFO 2014-06-30 09:43:47.389 -0500 StatusReporter -
===============================================================
----------------
| Server Stats |
----------------
Server Name: uswxaprcmpd01.usrdnwx.baml.com:9410
Operating System: Linux
Number of processors: 16
Total Memory Used: 19% (5939MB of 31007MB)
....
------------
| Sessions |
------------
select * from /services/databases/system/sys_sessions where status = 'ACTIVE' order by idle_duration
------------
| Requests |
------------
select * from /services/databases/system/sys_requests where status NOT IN ('SUCCESS', 'FAILED', 'TOP_TIME', 'TOP_MEMORY') order by start_time
-----------
| Cluster |
-----------
select * from /services/databases/system/sys_cluster
----------
| Caches |
----------
select * from /services/databases/system/sys_caches where status IN ('REFRESHING')
-------------------
| Datasource Info |
-------------------
---------------
| Memory Dump |
---------------
---------------------
| Repository Tables |
---------------------
---------------
| Thread Dump |
---------------
----------------
| Top Requests |
----------------
select * from /services/databases/system/sys_requests where status IN ('TOP_TIME', 'TOP_MEMORY') order by start_time
########################################################################
From this file you will have to check the memory-intensive queries logged under :
----------------
| Top Requests |
----------------
select * from /services/databases/system/sys_requests where status IN ('TOP_TIME', 'TOP_MEMORY') order by start_time
-----------
Then you will have to run these requests seperately and then from the query execution plan check why these requests are using more memory .
1) Increase the heap size in composite significantly until it has enough mem to satisfy the query as it is.
2) Choose to add this {option force_disk} to the sql so make the query use disk instead of heap space.
3) Modify and/or change the nature of the query to avoid that huge fetch of data from SqlServer.
=============
To monitor memory usage on a per-request basis:
• Go to Studio, and select the Requests This will display the requests
• Click on the Max Memory column header. This will order the requests based on which ones are consuming the most memory.
The next time that you come across a memory spike that’s in progress, try this:
Use one Studio instance to find out when a spike is on the upswing.
In a second Studio instance, select the Requests panel and click on the Max Memory tab to order the requests based on memory usage. Now see whether any of the requests displays steadily increasing memory whilst the spike is on the upswing.
In the event that Studio gets closed abruptly, an alternative is to try using the web manager. The web manager provides the same requests monitoring functionality as Studio. The manager provided a “Sort” menu option. You can use this option to display the requests in order of memory usage.
Additionally, You can take the ADS server's full thread dump using the "kill –3 <PID of ADS serveboot proces>" at 1 minute intervals.
Also you can use the Click the "free unused memory" button in Studio>Manager>Memory panel
This tells ADS to write a thread dump and a snapshot of the top 10 memory-intensive queries to the <ADS>\logs\cs_server_status.log file.
This is automatically done for you every 5 minutes like in your DEV server if you have the Administration>Configuration>Composite Server>Configuration>Debugging>Server Status Logging Period set to 5 minutes .
Then every 5 minutes or whenever you Click the "free unused memory" button in Studio>Manager>Memory panel the <ADS>\logs\cs_server_status.log file will be updated with the server statistics.
This server statistics has the sections like the following :
########################################################################
INFO 2014-06-30 09:43:47.389 -0500 StatusReporter -
===============================================================
----------------
| Server Stats |
----------------
Server Name: uswxaprcmpd01.usrdnwx.baml.com:9410
Operating System: Linux
Number of processors: 16
Total Memory Used: 19% (5939MB of 31007MB)
....
------------
| Sessions |
------------
select * from /services/databases/system/sys_sessions where status = 'ACTIVE' order by idle_duration
------------
| Requests |
------------
select * from /services/databases/system/sys_requests where status NOT IN ('SUCCESS', 'FAILED', 'TOP_TIME', 'TOP_MEMORY') order by start_time
-----------
| Cluster |
-----------
select * from /services/databases/system/sys_cluster
----------
| Caches |
----------
select * from /services/databases/system/sys_caches where status IN ('REFRESHING')
-------------------
| Datasource Info |
-------------------
---------------
| Memory Dump |
---------------
---------------------
| Repository Tables |
---------------------
---------------
| Thread Dump |
---------------
----------------
| Top Requests |
----------------
select * from /services/databases/system/sys_requests where status IN ('TOP_TIME', 'TOP_MEMORY') order by start_time
########################################################################
From this file you will have to check the memory-intensive queries logged under :
----------------
| Top Requests |
----------------
select * from /services/databases/system/sys_requests where status IN ('TOP_TIME', 'TOP_MEMORY') order by start_time
-----------
Then you will have to run these requests seperately and then from the query execution plan check why these requests are using more memory .
Feedback
Yes
No
Powered by
