Using Table Splitting

[andreas_zigann]

Hello,

I am doing experiments in preparation of a unicode conversion of a 1 TB MAXDB system. Therfore I have exported the database with sapinst with a duration of 44 hours.

To reduce the downtime I did another try using the table splitting option with the export monitor. Because I have not found detailed information I did a mistake in konfiguration of the file import_monitor_cmd.properties, so the export monitor did not use table splitting but needed also 44 hours for export (in addition to calculation of table splitting of around 20 hours which can be done online).

At third try I konfigured the export monitor right, the duration increases to 7 days.

Technical details:

R3 Enterprise EXT 1.1, Kernel 640, MAXDB 7.5, actual R3ta

10 CPU, 30 GB RAM, SAN

8 parallel processes for export,

8 tables to split to 16 parts each (COEP, COSS, COSP, COSB, GLPCA, CDCLS, AUSP, IBINVALUES)

largest table: COSS with 85 GB

DB-Size filled: around 1 TB, 50 Datafiles

In special of MAXDB it would be best to use primary key for table splitting. But I found no documentation of the file R3ta_hints.txt where I can manipulate the splitting decision of R3ta. (I added IBINVALUES IN_RECNO as selective column).

And now the questions:

Do you know a detailed dokumentation of the file import_monitor_cmd.properties?

Do you know a documentation of the R3ta table splitting options and input possibilities of R3ta_hints.txt?

Can you give me some experiences of similiar tests or migrations?

Best regards

Andreas

[Former Member · Accepted solution]

Hi Andreas,

you should try to run R3ta without the hints file. Then I would expect, that it choose primary key fields for splitting. If you are not happy with the output, you may also write your own WHR files. The only problem is to choose WHERE clauses which select disjoint sets which have a union set identical with the original table. I think that it's very hard to choose perfect WHR files for MAXDB because of the fact that MAXDB needs to export the table in primary key order. Some of my colleagues have some experiance with MAXDBs of bigger size, but they exported non standard tables which probably not in the hints file. They told me that R3ta have created good WHR files.

I have one question:

How do you choose the tables to split? Do you choose the largest or the slowest tables? When choosing the slowest tables, why do you split into 16 packages? Could you send an example of your WHR file for table COSS?

Regards

Ralph

[andreas_zigann · Accepted solution]

Hello Ralph,

I have chosen the tables because of their size.

The entry in COSS-1.WHR is:

tab: COSS

WHERE "OBJNR" <= 'OR000010431871'

I will try to calculate a new table splitting without hint file, I expect R3ta to decide for the field with the best selectivity, not for the whole key.

I took 16 packages to cause the MAXDB optimizer to use the Index, it used not to do so if I only use 5 packets due to old optimizer rules.

I thought 16 packages for 8 tables is small enougth to balace the runtime of the 8 processes.

Grettings

Andreas

[Former Member · Accepted solution]

Hi Andreas,

my recommendation would be to chose the tables by duration instead of their size. The reason is that different tables could be exported at different speeds.

For COSS I would use WHERE clauses of type:

WHERE MANDT = '001' AND LEDNR = '00' and OBJNR <= 'OR000010431871'

WHERE MANDT = '001' AND LEDNR = '00' and OBJNR > 'OR000010431871' AND OBJNR <= <next nr>

This should guarantee a key range scan which should be the fastest method for MAXDB. This should be true also for old versions of MAXDB. The number of packages I would chose by the number of times the export has to be faster than by whole table export. When your export of COSS took 42 hours and your projected downtime is 48 hours for export and import I would try to reach 12-14 hours for the slowest tables. That mean you should split COSS at least into four packages. I would not export to many packages because it is hard enough to build four or five WHERE clauses instead of 16. May be you could use the OBJNRs from R3ta but use your own WHERE files. Important in any case, for splitted tables, is to verify that you got all rows but only once.

Do you have written your export dump to seperate disks? If not, this normally lead to sub optimal performance.

Regards

Ralph

[Former Member · Accepted solution]

Hi Andreas,

I don't realize at the first sight that your system is running on MAXDB 7.5. Could you try to do an export on 7.6? I'm not sure when (7.5 or 7.6), but MAXDBs tree locking is may be responsible for the worse parallel performance and they try hard to reduce the impact by locking at smaller granularity.

Best regards

Ralph

[andreas_zigann · Accepted solution]

We will change Hardware and OS Version in February of 2009, due to this I will be able to change the MAXDB Version and the SAP Kernel.

The current OS-Version is not compatible with MAXDB 7.6.

[andreas_zigann]

Hello Markus,

I don't think I can miss entries, because the WHERE Clauses should be

WHERE Variable <= Upper limit pakage 1

WHERE Variable > Upper limit package 1 and <= Upper limit 2

...

I would do another pakage with <> Productive Client to be sure to have all entries.

Example:

COSS-1.WHR:

tab: COSS

WHERE MANDT = '010' and LEDNR = '000' AND ("OBJNR" <= 'OR000010431871')

COSS-2.WHR:

tab: COSS

WHERE MANDT = '010' and LEDNR = '000' and ("OBJNR" > 'OR000010431871') AND ("OBJNR" <= 'OR000010796294')

COSS-3.WHR:

tab: COSS

WHERE MANDT = '010' and LEDNR = '000' and ("OBJNR" > 'OR000010796294')

COSS-4.WHR:

tab: COSS

WHERE MANDT <> '010' or LEDNR <> '000'

Damit habe ich effektiv 3 Pakete in denen ich die ersten 3 Felder des primary key verwende und ein 4. Paket, dass den nichtproduktiven Bereich abdeckt und eigentlich leer sein sollte.

Wenn nun in der Zeit jemand etwas einfügt, dann landet es in einem der Pakete. Zum Ermitteln der geeigneten Objektnummern würde ich das Splitting mit dem hint in der Onlinephase durchlaufen lassen.

Zu testen ist, wie MAXDB auf die <> reagiert. Ich halte dieses WHR-File aber für notwendig, um sicherzustellen, dass nichts verloren gehen kann.

It might be usefull to activate the MAXDB Kommandomonitor for your export. You should start it for processes running more than 1 hour. When I did the export, MAXDB decided to do everytime a full table scan. If you have the same situation, you would be faster if you export without splitting.

I need to reduce downtime to 36 hours or less inklusive import. With export monitor an import monitor I think I have to reduce the runtime of export to about 30 hours.

Greetings

Andreas

[markus_doehr2]

> It might be usefull to activate the MAXDB Kommandomonitor for your export. You should start it for processes running more than 1 hour. When I did the export, MAXDB decided to do everytime a full table scan. If you have the same situation, you would be faster if you export without splitting.

Seeing other databases being able to export 250 - 400 GB/hour on the same machine...

> I need to reduce downtime to 36 hours or less inklusive import. With export monitor an import monitor I think I have to reduce the runtime of export to about 30 hours.

Did you try to do to export and import in parallel? That helped us a lot during our Unicode migration.

Markus

[andreas_zigann]

400 GB / hour would be very fine, but I think I need more CPU and RAM to receive this speed.

The Export duration of 30 hours is calculated with the option of parallel export and import. I think the longest process may not need more than 6 hours, so if it will be the last process, I will need 30 hours of export plus 6 hours of this large process.

[markus_doehr2]

400 GB / hour would be very fine, but I think I need more CPU and RAM to receive this speed.

The significant speed for such tasks on MaxDB is the speed of a single I/O. This is different on other DBMS.

If the export of a single table with an avg. single I/O speed of 6 ms will take 1 hour, then it will take 2 hours if that speed is 12 ms despite if the SAN is able to deliver much more in total.

If you measure I/O throughput during such loads you will notice, that even if it´s the only system on the SAN the database is not able to leverage the full SAN speed. Our SAN for that export can do 250 MB/sec (random access), the export runs with 16 R3loads at a max. of 10 - 15 MB/sec. Doing that with e. g. Oracle on the same hardware maxes out the SAN with 4 R3loads (250 MB/sec).

Markus

[markus_doehr2]

I´m actually trying to do a likewise thing with a 2,1 TB MaxDB 7.6.

(in addition to calculation of table splitting of around 20 hours which can be done online).

I´d be careful doing that online. If either at the time R3ta starts to evaluate the distinct values or after that table is finished someone does an insert to one of those tables you may miss that entry.

I used DB50 - Statistics to find out the biggest tables. After I had those I created my own R3ta_hints.txt checking each table in SE11 and DB50 to find out which field has the most distinct values. I have 85 tables in the R3ta_hints.txt.

The R3ta run itself took almost three days (though database prefetching was activated).

Since I had good experiences with packages of 250 MB in former migrations I spllited the rest of the tables with that value. At the end there were 1,114 packages.

I started the export last friday, it´s still running.

After my experiences the MaxDB can´t leverage the parallel unload as it would be expected. You may see a little better speed if you set the parameter LRU_FOR_SCAN to YES which will then use the full available data cache for table scans (as R3load does) instead of just 10 % of the cache if set to NO.

What you see if you use parallel unload is (using x_cons sh ac 1)

T204   7     -1 User      21781 IO Wait (R)           0 0     45        98030278(s)
T206   7     -1 User      21911 IOWait(R)(041)        0 0               98030278(s)
T222   8     -1 User      21887 IOWait(R)(041)        0 0               132941587(s)
T228   8     -1 User      21775 IOWait(R)(041)        0 0               132941587(s)
T234   9     -1 User      21899 IOWait(R)(041)        0 0               105696431(s)
T236   9     -1 User      21893 IOWait(R)(041)        0 0               105696431(s)
T248  10     -1 User      21905 IO Wait (R)           0 0     40        101974717(s)
T249  10     -1 User      21311 IO Wait (R)           0 0     41        101974717(s)

The IOWaits with 041 are no real reads but in fact waits for another task reading the same tree/leafs.

Markus