Sudden inflation in data warehouse.
Scenario: RTC 1.0.0.1 migrated to 2.0.0.1 successfully. A week or so ago the rtc server process began dying with out of memory exception. Yesterday I noticed:
com.ibm.team.reports.service.internal.ReportRestService.postRenderReport
by xiexiong@xx.ibm.com, 1 days, 07:53:57:617 running time
in the Active Services pages. I don't know (yet) the exact report this user is attempting to run, but spot checks of reports seem to work fine. However, I found a "Data Warehouse Metrics" report which I ran and when I saw one table with 3.5 million rows it really caught my attention. Running the report back 6 months it shows an *instant* jump from about 750,000 rows to just over 3 million. This may be due to the changes in the RTC 2.0.
Drilling into the data it appears that the WORKITEM_STATES table is the culprit with the massive row count. I just ran a db2 reorgchk and the statistics for the WORKITEM_STATES table and its indexes look fine.
Q: Is this massive change expected upon a migration ?
Q: Could *bad* reports be causing this server grief ?
com.ibm.team.reports.service.internal.ReportRestService.postRenderReport
by xiexiong@xx.ibm.com, 1 days, 07:53:57:617 running time
in the Active Services pages. I don't know (yet) the exact report this user is attempting to run, but spot checks of reports seem to work fine. However, I found a "Data Warehouse Metrics" report which I ran and when I saw one table with 3.5 million rows it really caught my attention. Running the report back 6 months it shows an *instant* jump from about 750,000 rows to just over 3 million. This may be due to the changes in the RTC 2.0.
Drilling into the data it appears that the WORKITEM_STATES table is the culprit with the massive row count. I just ran a db2 reorgchk and the statistics for the WORKITEM_STATES table and its indexes look fine.
Q: Is this massive change expected upon a migration ?
Q: Could *bad* reports be causing this server grief ?
6 answers
Scenario: RTC 1.0.0.1 migrated to 2.0.0.1 successfully. A week or so ago the rtc server process began dying with out of memory exception. Yesterday I noticed:
com.ibm.team.reports.service.internal.ReportRestService.postRenderReport
by xiexiong@xx.ibm.com, 1 days, 07:53:57:617 running time
in the Active Services pages. I don't know (yet) the exact report this user is attempting to run, but spot checks of reports seem to work fine. However, I found a "Data Warehouse Metrics" report which I ran and when I saw one table with 3.5 million rows it really caught my attention. Running the report back 6 months it shows an *instant* jump from about 750,000 rows to just over 3 million. This may be due to the changes in the RTC 2.0.
Drilling into the data it appears that the WORKITEM_STATES table is the culprit with the massive row count. I just ran a db2 reorgchk and the statistics for the WORKITEM_STATES table and its indexes look fine.
Q: Is this massive change expected upon a migration ?
Q: Could *bad* reports be causing this server grief ?
Hi
Don't know the cause - but check you are running the server with at least 1.5 G of memory for the VM. This appears to be the new default on RTC 2.0.0.2 - and it should give you enough memory headroom while the problem is investigated.
What database are you using?
anthony
Scenario: RTC 1.0.0.1 migrated to 2.0.0.1 successfully. A week or so ago the rtc server process began dying with out of memory exception. Yesterday I noticed:
com.ibm.team.reports.service.internal.ReportRestService.postRenderReport
by xiexiong@xx.ibm.com, 1 days, 07:53:57:617 running time
in the Active Services pages. I don't know (yet) the exact report this user is attempting to run, but spot checks of reports seem to work fine. However, I found a "Data Warehouse Metrics" report which I ran and when I saw one table with 3.5 million rows it really caught my attention. Running the report back 6 months it shows an *instant* jump from about 750,000 rows to just over 3 million. This may be due to the changes in the RTC 2.0.
Drilling into the data it appears that the WORKITEM_STATES table is the culprit with the massive row count. I just ran a db2 reorgchk and the statistics for the WORKITEM_STATES table and its indexes look fine.
Q: Is this massive change expected upon a migration ?
Q: Could *bad* reports be causing this server grief ?
Hi
Don't know the cause - but check you are running the server with at least 1.5 G of memory for the VM. This appears to be the new default on RTC 2.0.0.2 - and it should give you enough memory headroom while the problem is investigated.
What database are you using?
anthony
I upped the jvm to 2200M and that has helped and I sometimes see the allocated pegged to that with a small % free.
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09051"
with level identifier "03020107".
Informational tokens are "DB2 v9.5.0.1", "s080328", "U814639", and Fix Pack
"1".
Product is installed at "/opt/IBM/db2/V9.5"
In 2.0, we changed the layout of the WORKITEM_STATES table. Yes the table has a much larger number of rows but each row has just three integer columns. So overall between 1.0 and 2.0, the data warehouse size (bytes) has been reduced significantly.
I was seeing the issue with long running reports (> 1 day as shown in the server page 'active services'). Fortunately, those have dwindled since I last
restarted this particular RTC process earlier this week. That process was keeping my CPU at about 70% average use. I could never get from the
user exactly what report(s) was being executed ...
Thanks for your help....