Data Migration taking forever to complete

[David_Tsatsi]

Hi Guys limro02, Shauna_dorsey, CA Capacity Management

 

Is it really common to have data loads from eHealth data adapter to take forever to load and to migrate?

 

This is only 48 hours of data for a 10 physical servers only but its still stuck on "Loading ancillary data". It has been 5 Hours but still not complete.

 

The previous one took 7513 sec/2 h to complete and it also had 48hours of data.

 

Im only collecting system, network and disk metrics.

 

Regards

David

[Legacy User]

David,

   If it is hung for that long, you should check to see if Data Manager still has a connection to eHealth.  If it does not then the load may have failed because of a database disconnect.  This can be caused by a firewall time-out value that is shorter than your longest query.   In the Staging log does it show when MAP's are completed?  The one it is stuck should be clear from the staging log.  (That MapID tells you when SQL query it was running when it got stuck.)

 

  Sometimes the E-health database on Oracle might need to some maintenance to improve the performance.

 

Also, if the firewall disconnects Oracle database connections, that is a bad thing...you may need to use the "best practice" and pull in only the last 24 hours of data once a day, or stage data twice a day and get the most recent 12 hours of data.

 

There is another DM parameter that you can adjust "Bucket size" that will allow it to pull a smaller number of servers.

Check DM Help for information on that one.

 

I hope this helps,

 

David D.

[David_Tsatsi]

Hi David

 

My question was a bit unclear.

 

The load from eHealth to Data Manager has successfully completed and the data is now in the staging.tables. The problem is migrating the data from the staging tables to the data tables. That is what is taking a very long time. And i'm not sure why.

 

Regards

David

[Legacy User]

Questions: 

1.  How many rows are in the Staging table "Server_Metric_Staging"?

2.  How many rows did you just Stage from eHealth? (use your PID or TransactionID) to count rows in Server_Metric_Staging.

3.  What does your most recent PURGE report look like?

4.  Does Purge report show number of ROWS deleted from Server_Metric_Staging? If yes, then it is not truncating.

5.  Did you know that if there are failed Staging jobs that your purge job cannot truncate staging tables, but must always perform row deletes?

 

Staging tables will get very large and very fragmented if the purge job cannot "truncate" the staging tables.

Delete rows from a staging table are very slow ... often can require hours to complete.

Truncate staging table is completed in a few seconds.

A freshly truncated staging table will allow the migrations to run more quickly.

Another slowdown can happen because of null values in the staging table.

If there are lots of rows loaded with NULL values in the Staging table, then pre-Migration DM will delete all the NULL rows from each staging table.  This is incredibly slow (hours) on a fragmented table.

 

I hope some of this helps to solve your problem,

David D.

[Legacy User]

We just went through this. Kip Lamb can give a more complete response to this…

Among the things you mention below, we unchecked the “Migrate immediately after staging” option on the load schedules. My understanding is that the migration process is “single threaded”, and allowing Data Manager to manage the migrations reduces resource contention. Doing this, along with conducting daily rather than weekly loads, has dramatically improved our performance.

 

Bill Brown

 

Capacity & Performance Management

EBS-BB Availability – CBT -- TOG

Wells Fargo Bank N A | 103 Gelding Garth | Peachtree City, GA 30269

MAC: G2747-010

Tel [phone]

[e-mail]<mailto:[e-mail]>

http://hop.wellsfargo.com/ebsbbav-capacity

This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.

[Legacy User]

Bill is correct... the Migrate Immediately should NEVER be checked.

It was something that was put in place for development purposes, and should have been removed.

"Migrate Immediately" may work for a while, but it will cause major problems.

 

The best practice is:  Migrate Immediately - Just say NO!

 

David

[Legacy User]

David,

   Did you get your question answered?

 

Did the Migration start running faster?

 

David

[David_Tsatsi]

Hi dupda03

 

No not yet. I have actually logged a support call for this. I don't know if this has to do with the fact that we recently rebuilt the tables in order to delete the empty rows in the tables.

 

Regards

David

[limro02]

Might be worth checking the STDERR log to see what's happening

[vrbto01]

Worth to think about Oracle Optimizer Statistics:

Tech Tip: Data Load / Staging poor performance / failure