ASE parameter optimization for R3load import

[Former Member]

Hello ASE experts,

since I'm currently trying to import a multi TB database into ASE (using heterogeneous migration/R3load), I'm wondering if there's any guidance on how to optimize it, like memory parameters etc.

I couldn't find anything in the SAP Notes, or in SCN.

What I was thinking so far is that the data cache should be much larger than initially set be SAPINST, like (for my 64GB machine):

1>  sp_configure ‘max memory’,25000000

2>  go

1>  sp_cacheconfig 'default data cache', '40g'

2>  go

Looks to run little bit faster. Obvious however is also the high CPU consumption by ASE, and compression is enabled by default.

Creation of indexes does also not seem to be very fast - not sure if we could increase the memory area for sorts.

So any hints or comments in that area, any other experience?

Cheers,

Ulrich

[Former Member · Accepted solution]

Hello Ulrich,

You may want to adjust the following (this is hard to say remotelly, what types of contentions your system is facing, so i'm just providing some basic areas to look at):

1. partition your caches

2. tune your thread pools

3. add large pool to your data cache

4. increase degree of parallelism

Regards,

Konstantin

[Former Member · Accepted solution]

Hi Konstantin,

thanks. Topic number 4 - increase degree of parallelism - seems to become the hottest issue at the moment. I can split large tables during the export, and the import will run in parallel.

But finally I see very long runtimes for the index creations.

E.g. table GLPCA - 300 mio. rows - final compressed size in ASE is ~80GB (Oracle: 150GB):

I have split it into 10 pieces, and the import of a single split into ASE takes 2.5 hours. This is fine, in fact much faster than the export.

But then, it has to create 5 standard SAP indexes plus 3 customer indexes.

R3load is creating these indexes sequentially, and there is no parallelism. Each index requires about 24 hours to create, so the total runtime will be 8 days!

I could think about manipulating DDLSYB.TPL, adding a clause like

"with consumers=8"

to improve it. I was hoping that anyone has worked in that area before and likes to share his experience.

Large pool: Since page size is 16K, I was wondering if it makes sense to create a pool for even larger IOs? Right now it's simply:

IO Size  Wash Size

Config Size  Run Size

APF Percent

-------- ------------- ------------ ------------ -----------

16 Kb

61440 Kb  20480.00 Mb  20480.00 Mb

10

Cheers,

Uli

[Former Member · Accepted solution]

Hi Ulrich,

it seems your system is not yet fully configured to utilize all available server capacity (i believe you are io bound which should be resolved after applying configurations below), so try to set up the following first and check you index creation time, it should improve significantly:

1. Check that an amount of engines in you syb_default_pool is approximately equal to number of cores available on the server (should be little less, something like number of cores - 1)

2. Check if your cache is partitioned (should be about the same amount of partitions as the number of engines)

3. Create large pool with 128k size in your data cache (make it about 10GB ie 50% of your data cache) and increase number of APF percent for it to about 50 or even more. Also increase Wash Size. You may also try to increase APF and wash size for your remaining 16k pool.

4. Check you procedure cache size, it should be about 15% of the data cache.

5. Also try to play with internal query parallelism settings (parameters max parallel degree and number of worker processes)

6. Increase value of number of sort buffers to maximum (32767)

Also regarding your system - check you memory consumption, if you have some spare unallocated memory - give it to database (to main data cache)

Check your disk subsystem where devices for data and temporary dbs are located. It should be fast enough to provide several dozens or may be even hundreds MB per second throughput.

So try all of the above (with regard to your server available resources) and let us know if that helped.

Regards,

Konstantin

[JanStallkamp · Accepted solution]

Hi Uli.

Regarding the index creation: Parallelism is really helping here. We have used it for our migration tests a lot and your approach to edit DDLSYB.TPL is the way we have been speeding up some migrations in the lab a lot. Your DDLSYB.TPL could look like:

prikey: AFTER_LOAD ORDER_BY_PKEY
seckey: AFTER_LOAD
cretab: CREATE TABLE &tab_name&
        ( /{ &fld_name& &fld_desc& /-, /} ) &compression&
drptab: DROP TABLE &tab_name&
crepky: CREATE &unique& INDEX &pri_key&
        ON &tab_name&
        ( /{ &key_fld& /-, /} ) with sorted_data
drppky: DROP INDEX &tab_name&.&pri_key&
creind: CREATE &unique& INDEX &ind_name&
        ON &tab_name&
        ( /{ &fld_name& /-, /} ) with consumers = 3

The sorted_data option is only working for sorted exports. If you have some tables dominating the overall runtime you could also think about using a higher number of consumers for that specific tables. Just create a second DDLSYB.TPL, e.g. named DDLSYB_LRG.TPL where you set a higher number of consumers (5-10) and then manually change the CMD-files for the large tables to use this TPL-File.

Starting with ASE version 15.7.0.012 the speed of parallel index creation is improved by producers. Currently the recommendation is to have a ratio of 3 producers for 5 consumers, 4 producers for 7 consumers or 5 producers for 9 consumers.

A large I/O pool in the default data cache should improve performance, too. I would start with splitting the data cache in two pools of equal size for 18k and 128k. During the migration it might also be a good idea to increase the wash area of the pools to close to 50%. I would also set the APF limit to 50%. But keep in mind that this are settings only for the migration that have to be changed for 'normal' system operation.

Regards,

Jan

[Former Member · Accepted solution]

Hey guys,

great help!

By adjusting my data cache (creating a large 128K pool, setting wash size and APL to 50%), and modifying DDLSYB.TPL, I was able to create a secondary index for GLPCA in 4 hours, which is MUCH better than the ~24 hours it took before.

I've used "consumers=7". Looks like it strongly improves the sort operation, and I can see that also at the strongly increased CPU consumption.

The scan part of the index creation still seems to run sequentially. I will look at the new ASE release next in order to improve that as well, because I'm still running 15.7.0.011.

By the way, does that 15.7.0.012 correspond to the new version available in the SAP market place?

I did look at the change history, but couldn't find any statement about parallelizing index creation by running multiple producers, e.g. in the attachment of SAP Note 1590719.

Thanks,

Uli

[JanStallkamp · Accepted solution]

Hi Uli.

By the way, does that 15.7.0.012 correspond to the new version available in the SAP market place? 
I did look at the change history, but couldn't find any statement about parallelizing index creation by running multiple producers, e.g. in the attachment of SAP Note 1590719. 

Yes and no... 15.7.0.012 is the new version available in the market place. But the feature I mentioned unfortunately is not in this version but to my knowledge in the next one... Sorry for the confusion.

Regards,

Jan

[Former Member · Accepted solution]

Hi Ulrich,

We have imported also about 2 TB of data with this option enabled.

select into/bulkcopy/pllsort i think option is really exceptional in importing data and

128K data cache large IO Pool as you are already aware of the large data cache usefull especially in cases of index creation.