This blog explains the technical background of table split as part of the database migration option (DMO).

As a prerequisite, you should have read the introductionary document about DMO: Database Migration Option (DMO) of SUM – Introduction and the technical background in DMO: technical background.

During the migration of application tables, the migration of big tables might dominate the overall runtime. That is why SAPup considers table splitting to reduce the downtime of the DMO procedure. Table splitting shall prevent the case that all tables but a few have been migrated, and that only a small portion of all R3load processes these remaining (big) tables processes. The other R3load processes would be idle (to be more precise: would not run), and the long tail processing of the big tables would increase the downtime unnecessarily. See figure 1 below for a schematic view.

Fig_01.jpg

SAPup uses the following approach to define the tail: if the overall usage of R3load pairs (for export and import) drops below 90 %, SAPup handles all tables that are processed afterwards as being part of the tail (see figure 2 below).

Fig_02.jpg

During the configuration of the DMO procedure, you will configure a number of R3load processes, which determines the number of R3loads that may run in parallel. This explanation will talk about R3load pairs that are either active of idle, which is rather a virtual view. If an R3load pair did execute one job, it will not wait in status idle, but end. SAPup may then start another R3load pair. Still for the discussion of table split, we consider a fixed number of (potential) R3load pairs, which are either active or idle. The following figure 3 illustrates this view.

Fig_03.jpg

Prerequisites

To follow this blog, you have to be familiar with the basics of DMO, and with the DMO R3load mechanism, as discussed in the SCN blogs Database Migration Option (DMO) of SUM – Introduction and DMO technical background.

Automatic table splitting

SAPup will automatically determine the table split conditions, and there is no need and no recommendation to influence the table splitting. Your task is to find the optimal number of R3load processes during a test run, and provide the table duration files for the next run. (SAPup will use the table duration files to calculate the table splitting based on the real migration duration instead of the table size; see DMO guide, section 2.2 “Performance Optimization: Table Migration Durations”).

You may still want to learn more on the split logic, so this blog introduces some background on table splitting. Note that SAPup will not use R3ta for the table split.

Table split considerations

Typically, you will expect table splitting to happen for big tables only, but as we will see, the attempt to optimize the usage of all available (configured) R3load processes may result in splitting other tables as well. Still, splitting a table into too many pieces may result in a bad export performance: lots of parallel fragmented table segments will decrease read performance, and increase the load on the database server. A table may be big, but as long as has been completely processed before the tail processing starts, there is no reason to split that table. That is why the tool will calculate a minimum of table splits to balance all requirements.

The logic comprises four steps: table size determination, table sequence shuffling, table split determination, and assignment to buckets. A detailed explanation of the steps will follow below. During the migration execution, SAPup will organize tables and table segments in buckets, which are a kind of work packages for the R3load pair to export and import. During the migration phase, each R3load pair will typically work on several buckets, one after the other.

Step 1: Sorting by table size

SAPup will determine the individual table sizes, and then sort all tables descending by size.

In case you provide the table duration file from a previous run in the download folder, SAPup will use the table migration duration instead of the table size.

Fig_04.jpg

Assuming we only had sixteen tables, figure 4 above shows the sorted table list. The table number shall indicate the respective initial positioning in the table list.

Step 2: Shuffle table sequence

Migrating the tables in sequence of their size is not optimal, so the table sequence is reordered (“shuffled”) to achieve a good mixture of bigger and smaller application tables. Figure 5 below tries to illustrate an example.

Fig_05.jpg

SAPup uses an internal algorithm to shuffle the table sequence, so that table sizes alternate between bigger and smaller.

Step 3: Table split determination

SAPup will now simulate table splitting, based on the number of configured R3load processes. Note that changing the number of configured R3load processes later during the migration phase will affect the runtime of the procedure.

For the simulation, SAPup will work on “slots” that represent the R3load pairs, and will distribute the tables from the shuffled table list into these slots. Note that these R3load “slots” are not identical to the buckets. SAPup will use buckets only at a later step. A slot is a kind of sum of all buckets, which are processed by an R3load pair.

Initially, the simulation will assign one table from the shuffled table list into each slot until all slots are filled with one table. In an example with only eight R3load pairs, this means that after the first eight tables, all slots have a table assigned, as shown in figure 6 below.

Fig_06.jpg

In our example, SAPup has filled all slots with one table, and the second slot from above has the smallest table, so it has the fewest degree of filling.

Now for all following assignments, SAPup will always assign the next table from the list to the slot that has the fewest degree of filling. In our example, SAPup would assign the next table (T7) to the second slot from top. After that, SAPup will most probably assign the next table (T9) to the first slot, see figure 7 below (sounds like Tetris, doesn’t it?).

Fig_07.jpg

Finally, SAPup has assigned all tables from the shuffled table list to the slots, as shown in figure 8 below. Note that the figures are not precise in reflecting the table sizes introduced in figure 4 and 5.

Fig_08.jpg

As last part of this simulation run, SAPup will now determine which tables to split. The goal is to avoid a long tail, so SAPup will determine the tail, and split all tables that are part of the tail.

SAPup determines the tail by the following calculation: SAPup sorts the slots by filling degree, and the tail begins at the point in time at which the usage of all R3load pairs is below 90%. All tables that are part of the tail – either completely or partially – are candidates for a split, as shown in figure 9 below. As an example, table T2 is shown as being part of the tail.

Fig_09.jpg

SAPup determines the number of segments into which the table will be split by the degree by which the table belongs to the tail. The portion of the table that does not belong to the tail is the scale for the table segments to be created. For the example of table T2, this may result in three segments T2/1, T2/2, and T2/3.

SAPup will now extend the shuffled table list by replacing the detected tables by its table segments. Figure 10 shows the example with three segments for table T2.

Fig_10.jpg

SAPup starts the next iteration of the simulation, based on the shuffled table list with table segments.

If the calculated tail is negligible (lower than a specific threshold) or if the third simulation has finished, SAPup will continue with step 4.

Step 4: Table and table segments assignment to buckets

The result of step 3 is a list of tables and table segments whose sequence does not correlated to the table size, and which was optimized to fill all R3load slots with a small tail. Now SAPup will work with buckets (work packages for R3load pairs) instead of slots. This is a slightly different approach, but as the filling of the buckets will use the same sequence of tables before, the assumption is that it has the same result.

SAPup will assign the tables of this list to the buckets in the sequence of the list. The rules for this assignment are

  1. A bucket will get another table or table segment assigned from the list as long as the bucket size is lower than 10 GB.
  2. If the next table or table segment is bigger than 10 GB, the current bucket is closed, and SAPup will assign the table or table segment to next bucket.
  3. SAPup will put segments of a split table into different buckets– otherwise two table segments would reside in one bucket, which neutralizes the desired table split.

The first rule results in the effect that a bucket may have more table content than 10 GB. If a table of e.g. 30 GB was not determined for a split, the respective bucket will have this size.The second rule may result in the effect that a bucket is only filled to a low degree, if the following table / table segment was bigger than 10 GB so that it was put into the following bucket.The third rule results in the effect that e.g. for a table with four segments of size 5 GB each, several buckets will have a size of 5 GB. Figure 11 below tries to illustrate this with some examples.

Fig_11.jpg

Now SAPup has defined the distribution of tables and table segments into buckets, which in turn are part of a bucket list. All this happens during the phase EU_CLONE_MIG_DT_PRP for the application tables (and during phase EU_CLONE_MIG_UT_PRP for the repository). Note that the DT or UT part of the phase name is no indication whether or not the phase runs in uptime (UT) or downtime (DT): EU_CLONE_MIG_DT_PRP runs in uptime.The migration of application tables happens in downtime during phase EU_CLONE_MIG_DT_RUN. During the migration phase, SAPup will start the R3load pairs and assign the next bucket from the bucket list. As soon as an R3load pair is finished (and closes), SAPup will start another R3load pair and assign the next bucket to this pair, as shown in the following figure 12.

Fig_12.jpg

Relevant log files are

  • EUMIGRATEDTPRP.LOG: tables to split, number of buckets, total size
  • EUMIGRATEDTRUN.LOG: summary of migration rate
  • MIGRATE_DT_RUN.LOG: details like R3load logs

Additional considerations

Typically, each R3load pair will execute more than one bucket. Exceptions may happen for small database sizes. As an example, for a total database size of 9992.3 MB and 20 R3load pairs (so 40 configured R3load processes), the tool would reduce the bucket size to put equal load to all R3load pairs. The log will contain a line such as “Decreasing bucket size from 10240 to 256 MB to make use of 160 processes”. Below you see the respective log entry in EUMIGRATEUTPRP.LOG:

1 ETQ399 Total size of tables/views is 9992.3 MB.

2 ETQ399 Decreasing bucket size from 10240 to 256 MB to make use of 20 processes.

1 ETQ000 ==================================================

1 ETQ399 Sorting 10801 tasks for descending sizes.

1 ETQ000 ==================================================

1 ETQ399 Distributing into 20 groups of size 500 MB and reshuffling tasks.

1 ETQ000 ==================================================

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Rajendran Srinivasan

    Hi Boris

     

    Thanks for the details of how the DMO tool handles large table splitting. Some questions:

     

    1. Does each bucket result in one TSK, cmd set ?

    2. If so, each R3load pair will work on only one TSK, cmd set or each R3load pair can handle more then one set ?

    3. What if one bucket fails. What is the best way of rerunning that failed R3loads (for that bucket, of course after addressing the issue), instead of waiting for the tail end of the phase ? (I had asked you the same question in the other forum too..sorry for the duplicates).

    4. And a final question on row counts comparison. Though DMO makes sure it reports the discrepancies, if any, for auditing purpose if we want to generate a list of tables and the rows exported/imported, is there a file or log where I can find that information (don’t want to go through a count(*) from the source and target database again ! ) ..

     

    Once again, thanks for all the too detailed technical insight into this DMO process.

     

    Regards

     

    Raj

    (0) 
    1. Boris Rubarth Post author

      Hi Raj,

      here are some remarks on your questions:

      1. Yes, each bucket has one control file set.
      2. Each R3load pair will work on one bucket (i.e. one TSK, cmd set) at a time. As soon as this bucket was processed, the R3load processes are done and end. SUM will check this and start the next pair (virtually the “same” pair again) for the next bucket, for the next TSK, cmd set.
      3. If a bucket fails, currently no workaround: you have to wait.
      4. Table comparison: no log file available that lists the table names.

       

      Regards, Boris

      (0) 
  2. Nicholas Chang

    Hi Boris Rubarth

     

    Thanks for always sharing!

     

    btw, i got a question for below statement.

     

    “SAPup will now simulate table splitting, based on the number of configured R3load processes. Note that changing the number of configured R3load processes later during the migration phase will affect the runtime of the procedure.”

     

    What will happen if i add extra (eg:5 pairs) R3load procesess during the DMO export/import phase via set procpar after the simulation had performed for initial R3load processes configured, will the newcoming R3load processes being utilized for the export/import?

     

    Thanks,

    Nicholas Chang

    (0) 
    1. Boris Rubarth Post author

      Hi  Nicholas Chang,

       

      adapting the number of R3load processes during the migration will be taken into consideration by SUM. It will not happen immediately, but as soon as the next R3load pair is done, the number of active R3loads is considered.

       

      Regards, Boris

      (0) 
  3. Ankit Gupta

    Dear Boris Rubarth,

     

    Apart from above questions, I want to add 3rd ques :

     

    Still, splitting a table into too many pieces may result in a bad export performance: lots of parallel fragmented table segments will decrease read performance, and increase the load on the database server”

     

    As DMO is calculating the splits based on no. of R3load processes, how to decide the correct value ? If we take more R3loa,  splits will be more, then above statements state the export performance could be impacted because of high sequential read. If we keep very less R3load, splits will be less and import into HANA would perform bad.

     

    Regards,

    Ankit

    (0) 
    1. Boris Rubarth Post author

      Dear Ankit,

       

      the number of R3load processes should be as high as possible, related to the performance of the application server used. A test run is required to balance the number of R3load possible, and the next run should be started with this optimal number.

       

      Regards, Boris

      (0) 
      1. Ankit Gupta

        Hello Boris,

         

        As you mentioned, if the splits are more, there could be high sequential read impact. How can we foresee and avoid ?

         

        Regards,

        Ankit

        (0) 
  4. Claudio Quina

    Dear Boris,

     

    During phase EU_CLONE_MIG_DT_PRP I’ve noticed that one table has an index with 12 fields, and these fields are being used to do the split, so this process is running for ever, more than 3 days now. I want to influence the fields to be used using file EUCLONEDEFS_ADD.LST. My question is, if I stop now the SUM processes and create the file with new fields to be used, will this new file be picked up without needing to do the split again from the scratch in all of the other tables that have already finished?

     

    Kindly advise.

    Kind Regards,

    Claudio Quina

    (0) 
  5. Prabhat Sharma

    I am currently running the ECC upgrade & migration to HANA, during the execution phase (downtime) we have been seeing all the logs of export/import and it was showing the total time spent by EXP R3load and export PIPE but as soon as this phase completed, all the logs for exp/imp got deleted automatically, just CMD/STR/OUT files are remaining in migrate_dt directory. please let me know how i can analyse my whole migration now…other thing, is this normal, could i have saved my logs by skipping any step?

    (0) 
  6. Kenneth Paul

    Hi Boris,

    Using the benchmarking tool I have determined that 280 is the optimal number of R3load processes to be used for my ECC migration. So while executing DMO on the production system do I need to just give 280 R3load as soon as I enter downtime & continue till the end of downtime or should I start with a lower number of R3load process & keep increasing slowly?

    Thanks!
    Kenneth

    (0) 
    1. Boris Rubarth Post author

      Hi Kenneth,

      the recommendation is as follows:
      after you have used the benchmarking tool to determine the optimal number of R3loads, you should have a DMO test run with this number of R3loads, and check whether it is also the optimal number for DMO. Once you have your number, you enter this number for the downtime processing and keep it during the complete procedure.
      Hope you have already checked the following blog on DMO performance optimization:
      https://blogs.sap.com/?p=135725

      Thanks, Boris
       

      (1) 

Leave a Reply