Late mate coloumn store error while copying table

[Former Member]

I m getting while copying data from one table to another.

Could not execute 'Insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B"' in 10:51.063 minutes .

[129]: transaction rolled back by an internal error: TrexUpdate failed on table 'SAP<SID>:/BIC/APWBSTRPR00' with error: exception 2617:

ims_search_api/LateMatColumn.cpp:772

exception in LateMatColumnSource

, rc=2617 - enforce TX rollback

I have seen couple of notes

1905429 - Error "exception in LateMtColumnSource" executing Data Services job against SAP HANA table.

2125399 - SAP HANA: table consistency check returns error "Maximum rowid in table is larger than max rowid in runtime data"

  1975448 - Problems due to Late Materialization and chunk-wise Data Processing

2127827 - Error in IndexServer trace: 'LateMatColumn.cpp (00629) : lateMat loadPage failed, no valid transaction SYS_REPO:OBJECT_HISTORY'

2113745 - Exception in LateMatColumnSource

However, we are on SPS09 97.01. I dint see any relevant Note for our version and also those notes dint helped to resolve the issue.

[former_member185511]

hi,

last time i faced similar problem, so it is better to optimize your query. If you are not working with a company who keeps changing memory parameters, nothing to do with global.ini. You are exceeding max values already set by system.

Split your query into smaller queries dont try to use 1 statement to do all the job

You have several inner joins and also IN statements ? You can paste your query also here.

[Former Member]

Insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B -> this is the query I am executing. I want all my data present in  /BIC/APWBSTRPR00B (25 GB)to /BIC/APWBSTRPR00.

I have free space of 150 GB of free space.

Also, How to split the above query ? (To reduce the memory utilization)

[former_member185511]

as Lucas Oliveira said, you can use CALMONTH or whatever can be usable to process less data that was what i mean at first

try 1 month first, and in a while statement you can process all your CALMONTH's one by one.

[Bojan-lv-85]

Hello Pavan,

your estimation of the memory utilization for the table-copy is unfortunately not that simple in reality, to show you why exactly I would need to OOM dump. Before we dig any deeper I would suggest to take a look at the following note

1975448 - Problems due to Late Materialization and chunk-wise Data Processing

pt.3: High memory consumption due to large intermediate result sets

You can try to lower the mentioned parameters, but go ahead with caution as those might severely degrade system performance. But they might help you to perform the copy of the table.

BR, Bojan

[Former Member]

HI Bojan,

I tried reducing the parameters to half size . However, still same issue. I am really wondering how HANA is using complete (158 GB of my free memory) when i try to copy 28 GB Table. I am pasting the termination dump here.Can you tell me why it is using such a huge memory.

[81655]{302482}[60/74430534] 2016-01-20 16:25:40.248528 e SERVER_TRACE     LateMatColumn.cpp(00432) : Error during late mat plan execution (single threaded)for tableSAPSID:/BIC/APWBSTRPR00B (16933): AttributeEngine: not enough memory(6952)

[81655]{302482}[60/74430534] 2016-01-20 16:25:40.252800 e SERVER_TRACE     LateMatColumn.cpp(00770) : lateMat loadPage failed SAPSID:/BIC/APWBSTRPR00B (16933): rc=6952

[87897]{302482}[60/74430534] 2016-01-20 16:25:40.253057 e SERVER_TRACE     LateMatColumn.cpp(00595) : ~LateMatColumnSource() caught exception in prefetch job: exception  1: no.70006952  (ims_search_api/LateMatColumn.cpp:772)

    exception in LateMatColumnSource

exception throw location:

1: 0x00007ffb40335899 in TRexAPI::LateMatColumnSource::loadPage(ltt_adp::vector<ltt::smartptr_handle<TRexCommonObjects::ColumnBase>, ltt::integral_constant<bool, true> >&, unsigned int, unsigned int)+0x1175 at LateMatColumn.cpp:772 (libhdbcsapi.so)

2: 0x00007ffb3ff9dc3e in TRexAPI::LateMatColumnSource::loadAndActivatePage(unsigned int)+0x8a at LateMatColumn.h:158 (libhdbcsapi.so)

3: 0x00007ffb40335bad in TRexAPI::LateMatColumnSource::PagePrefetchJob::run(Execution::JobObject&)+0x89 at LateMatColumn.cpp:891 (libhdbcsapi.so)

4: 0x00007ffb2f88d221 in Execution::JobObjectImpl::run(Execution::JobWorker*)+0x7f0 at JobExecutorImpl.cpp:843 (libhdbbasis.so)

5: 0x00007ffb2f89bbe2 in Execution::JobWorker::runJob(ltt::smartptr_handle<Execution::JobObjectForHandle>&)+0x2e0 at JobExecutorThreads.cpp:205 (libhdbbasis.so)

6: 0x00007ffb2f89cb34 in Execution::JobWorker::run(void*&)+0x1a0 at JobExecutorThreads.cpp:370 (libhdbbasis.so)

7: 0x00007ffb2f8d4cb9 in Execution::Thread::staticMainImp(void**)+0x875 at Thread.cpp:496 (libhdbbasis.so)

8: 0x00007ffb2f8d591d in Execution::Thread::staticMain(void*)+0x39 at ThreadMain.cpp:26 (libhdbbasis.so)

[Bojan-lv-85]

Hello Pavan,

do not forget that the table you are actually trying to copy is ~25GB in main. If you are copying this table you are actually accessing the read-optimized main of "/BIC/APWBSTRPR00B" and inserting into the write optimized delta of "/BIC/APWBSTRPR00" which is multiple times larger for the same data.

As long as no delta merge is performed this delta will grow until all records have been inserted. I suppose you saw some allocators like

Pool/AttributeEngine/Delta/LeafNodes

Pool/PersistenceManager/UnifiedTable

in the OOM which was raised. So I still would say that your observation is to be expected.

BR, Bojan

[Former Member]

Hi Bojan,

I agree that table will grow as long as there is no delta merge performed.

How to eliminate this problem ?

I believe even for Delta merge it will take the same amount of memory ( Double). So, How can i eliminate this ? I am on SPS09 97

Heap memory fragmentation: 4

Top allocators (component, name, size). Ordered descending by inclusive_size_in_use.

1: System:                                     /                                                          314115853439b (292.54gb)

2: System:                                     Pool                                                       309814401109b (288.53gb)

3: System:                                     Pool/PersistenceManager                                    67636438468b (62.99gb)

4: Statement Execution & Intermediate Results: Pool/itab                                                  58036638512b (54.05gb)

5: System:                                     Pool/malloc                                                54948752352b (51.17gb)

6: Column Store Tables:                        Pool/malloc/libhdbcs.so                                    39605604664b (36.88gb)

7: System:                                     Pool/PersistenceManager/UnifiedTable container             33646552060b (31.33gb)

8: System:                                     Pool/PersistenceManager/PersistentSpace(0)                 33582881232b (31.27gb)

9: System:                                     Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA      28681336384b (26.71gb)

10: System:                                     Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page 28581990400b (26.61gb)

11: Column Store Tables:                        Pool/AttributeEngine                                       26659263616b (24.82gb)

12: System:                                     Pool/NameIdMapping                                         26435352568b (24.61gb)

13: Column Store Tables:                        Pool/NameIdMapping/RoDict                                  26435352504b (24.61gb)

14: Column Store Tables:                        Pool/AttributeEngine-IndexVector-Single                    19816325576b (18.45gb)

15: System:                                     Pool/RowEngine                                             16973620998b (15.80gb)

16: Column Store Tables:                        Pool/AttributeEngine-IndexVector-Sp-Indirect               16622241032b (15.48gb)

17: System:                                     Pool/RowEngine/CpbTree                                     15084967728b (14.04gb)

18: Statement Execution & Intermediate Results: Pool/malloc/libhdbcsstore.so                               14753859592b (13.74gb)

19: Column Store Tables:                        Pool/AttributeEngine/idattribute                           10515954232b (9.79gb)

20: Column Store Tables:                        Pool/AttributeEngine/Delta                                 8512856704b (7.92gb)

Top allocators (component, name, size). Ordered descending by exclusive_size_in_use.

1: Statement Execution & Intermediate Results: Pool/itab                                                               56643302840b (52.75gb)

2: Column Store Tables:                        Pool/malloc/libhdbcs.so                                                 39605604664b (36.88gb)

3: System:                                     Pool/PersistenceManager/UnifiedTable container                          33646552060b (31.33gb)

4: System:                                     Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page              28581990400b (26.61gb)

5: Column Store Tables:                        Pool/NameIdMapping/RoDict                                               26435352504b (24.61gb)

6: Column Store Tables:                        Pool/AttributeEngine-IndexVector-Single                                 19816325576b (18.45gb)

7: Column Store Tables:                        Pool/AttributeEngine-IndexVector-Sp-Indirect                            16622241032b (15.48gb)

8: System:                                     Pool/RowEngine/CpbTree                                                  15084967728b (14.04gb)

9: Statement Execution & Intermediate Results: Pool/malloc/libhdbcsstore.so                                            14753859592b (13.74gb)

10: Column Store Tables:                        Pool/AttributeEngine/idattribute                                        10515954232b (9.79gb)

11: Column Store Tables:                        Pool/AttributeEngine                                                    7630412680b (7.10gb)

12: Column Store Tables:                        Pool/AttributeEngine/Delta/LeafNodes                                    6888021472b (6.41gb)

13: Column Store Tables:                        Pool/AttributeEngine-IndexVector-SingleIndex                            6167638560b (5.74gb)

14: System:                                     Pool/PersistenceManager/PersistentSpace(0)/RowStoreLPA                  4756341152b (4.42gb)

15: Statement Execution & Intermediate Results: Pool/JoinEvaluator/JERequestedAttributes/Results/Latemat                4157603840b (3.87gb)

16: System:                                     AllocateOnlyAllocator-unlimited/FLA-UL<3145728,1>/MemoryMapLevel2Blocks 2991587328b (2.78gb)

17: Monitoring & Statistical Data:              Pool/Statistics                                                         2144295336b (1.99gb)

18: System:                                     Pool/CSRowLocking                                                       2030043136b (1.89gb)

19: Column Store Tables:                        Pool/AttributeEngine-IndexVector-Sp-Sparse                              1772136600b (1.65gb)

20: Statement Execution & Intermediate Results: Pool/itab/VectorColumn          

[lucas_oliveira]

Since delta merge is partition-wise, partitioning the table before load data into it might be a way out of here (if that was not done already). Loading each partition data gradually might be necessary as well. Have you tried that already?

BRs,

Lucas de Oliveira

[Former Member]

Yes Lucas, I have tried it. Let be more clear . I have 150 GB of free HANA In-memory( No application is running on top of HANA) So, 150 GB should be free.

Scenario 1:

1. Table "/BIC/APWBSTRPR00B" (25 GB)  is not partationed (i.e 0 partations)

2. Created table "/BIC/APWBSTRPR00" (Empty table) -- > No partations

3. Executed query Insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B"'

4. The result is -- > OOM (Late mate coloumn store error)

Scenario 2:

1. Table "/BIC/APWBSTRPR00B" (25 GB)  is not partationed (i.e 0 partations)

2. Created table "/BIC/APWBSTRPR00" (Empty table)

3. Partition table in to 4 (Hash partation)

4. Executed query Insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B"'

5. The result is -- > OOM (Late mate coloumn store error)

scneario 3:

Tried the above methods with

1975448 - Problems due to Late Materialization and chunk-wise Data Processing

pt.3: High memory consumption due to large intermediate result sets i.e by reducing below parmaters into half of its value.

late_materialization_threshold

late_materialization_threshold_for_insert

P.S: I am still faciing the same error and not able to understand " How does copying of single table (25 GB) in HANA is troubling so much "

[Bojan-lv-85]

just played around a little bit, had  a 1.7mill records table which was allocating 2GB main.

Tried to copy it as you did and failed as you failed.

My observation is that the delta size of the table I was copying to was reaching 10GB+ so factor 5+.

Reducing late_materialization does obviously not help here as it obviously has no influence on the delta size during the insert operation.

However, I managed to copy the table by introducing the parameter for chunk wise data processing:

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','system')

SET('indexing', 'max_chunk_size_for_insert') = '20000' WITH RECONFIGURE;

No OOMs hit with this setting, you can try it, but only for the purpose of your copy operation, I would unset the parameter as the overall impact on other operations is not documented.

Looking at note 1934491 this parameter might be a good hint for "INSERT...SELECT" operations.

Give it a try!

[Former Member]

I still get the same error. I still dont understand, how a single table is using this much of memory to copy it.

[91087]{305347}[46/74894228] 2016-01-22 16:34:24.713321 e SERVER_TRACE     LateMatColumn.cpp(00432) : Error during late mat plan execution (single threaded)for tableSAPSID:/BIC/APWBSTRPR00 (38271): AttributeEngine: not enough memory(6952)

[91087]{305347}[46/74894228] 2016-01-22 16:34:24.714168 e SERVER_TRACE     LateMatColumn.cpp(00770) : lateMat loadPage failed SAPSID:/BIC/APWBSTRPR00 (38271): rc=6952

[88677]{305347}[46/74894228] 2016-01-22 16:34:24.714698 e table_update     TableUpdate.cpp(01738) : TableUpdate::execute() caught exception for table SAPSID:/BIC/APWBSTRPR00B, message: exception 70006952: exception in LateMatColumnSource

, rc=6952: exception  1: no.70006952  (ims_search_api/LateMatColumn.cpp:772)

    exception in LateMatColumnSource

exception throw location:

1: 0x00007ffb40335899 in TRexAPI::LateMatColumnSource::loadPage(ltt_adp::vector<ltt::smartptr_handle<TRexCommonObjects::ColumnBase>, ltt::integral_constant<bool, true> >&, unsigned int, unsigned int)+0x1175 at LateMatColumn.cpp:772 (libhdbcsapi.so)

2: 0x00007ffb3ff9dc3e in TRexAPI::LateMatColumnSource::loadAndActivatePage(unsigned int)+0x8a at LateMatColumn.h:158 (libhdbcsapi.so)

3: 0x00007ffb40335bad in TRexAPI::LateMatColumnSource::PagePrefetchJob::run(Execution::JobObject&)+0x89 at LateMatColumn.cpp:891 (libhdbcsapi.so)

4: 0x00007ffb2f88d221 in Execution::JobObjectImpl::run(Execution::JobWorker*)+0x7f0 at JobExecutorImpl.cpp:843 (libhdbbasis.so)

5: 0x00007ffb2f89bbe2 in Execution::JobWorker::runJob(ltt::smartptr_handle<Execution::JobObjectForHandle>&)+0x2e0 at JobExecutorThreads.cpp:205 (libhdbbasis.so)

6: 0x00007ffb2f89cb34 in Execution::JobWorker::run(void*&)+0x1a0 at JobExecutorThreads.cpp:370 (libhdbbasis.so)

7: 0x00007ffb2f8d4cb9 in Execution::Thread::staticMainImp(void**)+0x875 at Thread.cpp:496 (libhdbbasis.so)

8: 0x00007ffb2f8d591d in Execution::Thread::staticMain(void*)+0x39 at ThreadMain.cpp:26 (libhdbbasis.so)

[88677]{305347}[46/74894228] 2016-01-22 16:34:24.760012 e table_update     TableUpdate.cpp(01757) : rollback transaction because rc=6952 is set

[88677]{305347}[46/74894228] 2016-01-22 16:34:57.424840 e indexmgr         IndexMgr.cpp(01029) : removeLocked failed currentState delete minState read_config

[88677]{305347}[46/74894228] 2016-01-22 16:34:57.424868 e indexmgr         IndexMgr.cpp(01029) :  index "SAPSID:/BIC/APWBSTRPR00B (38275)" running delete 1; handles: handle 0x7fd0e5975ae0 thread 88677 state delete acquired 2016-01-22 16:34:57.424, handle 0x7fa812916d08 thread 88677 state read_config acquired 2016-01-22 16:34:29.990

[Bojan-lv-85]

did you clear the plancache before rerunning the query with the new parameter setting?

1. change the parameter to lets say 20000

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','system')

SET('indexing', 'max_chunk_size_for_insert') = '20000' WITH RECONFIGURE;

2. clear the plan cache: ALTER SYSTEM CLEAR SQL PLAN CACHE;

3. rerun the query

If you are still running into OOMs afterwards then you need to think about another way of copying this table.

Maybe if you have an unselective field like MANDT with only few different values you could limit the inserted records per run with filtering on this field.

BR, Bojan

[Former Member]

1. I have cleared the cache and tried. This settings are working for low memory table ( like 2 GB to 10 GB).  however, this is not working for my case(25 GB table size).

2. I tried reducing the value to max_chunk_size_for_insert') = 10000 . But, still the same issue.

3. I facing the same issue for other tables as well.

4. I am not sure how to modify the query.

5. According to my understanding this is something that HANA product should do this small task.

6. Customer cannot spend this much of time to copy a single table. I am disappointed by this

7. Also, I believe the above settings should be maintained by default.

[lucas_oliveira]

Hi Pavan,

Have you try to gradually bring your data over the target table?

You could:

(1) partition the empty target table by CALMONTH (range within each quarter for instance - depends on your data obviously).

(2) gradually get each month from the source table into the target table, making sure the target table is merged after every insert.

So instead of

insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B"'

You could try:

insert into "/BIC/APWBSTRPR00" select * from "/BIC/APWBSTRPR00B"' where CALMONTH between '201001' and '201003' -- first quarter of 2010

Then:

merge delta of "/BIC/APWBSTRPR00"

(3) unload the target table every time that partition is complete (all months are already in the table). If you have the export of your source table, you could delete the records you've just added from your source and merge it as well.

(4) repeat until source = target

Then unload source table and repartition the table. Let us know if that works.

Note: the whole process of getting your source table into the target will take more than 25GB for sure. As you add the data, it will fall into the delta part of this table which is not fully compressed. Very difficult to foresee what's the consumption at the end. That's why partitioning the table would be a possible way out *if* the issue is still memory (it does seems so at this point).

Note2.: Not sure if you need the table to be named /BIC/APWBSTRPR00 or you need really need a copy on your system after all.. If you only need it named as /BIC/APWBSTRPR00, you could simply RENAME it.

BRs,
Lucas de Oliveira

[Former Member]

Hi Lucas,

Still I dint got the data from source to target. I dint have a column by name "CALMONTH".

P.S: I like the idea that you are proposing. However, I dint have a column by name "CALMONTH".

Also, I still dint got the reason and wondering "why HANA is using such a huge amount of memory for a single table copy ?"

[lbreddemann]

Hi Pavan

I saw this thread and was wondering where you get the information from that there is 150 GB free memory available.

The OOM trace excerpt you posted shows that the allocation failed when the system was using 292.54 GB in total.

Most of it is in use for dealing with column store data (the remaining 139 gb are used for other purposes):

3: System:                                Pool/PersistenceManager  (62.99gb)

4: Statement Execution & Interm. Results: Pool/itab                (54.05gb)

6: Column Store Tables:                   Pool/malloc/libhdbcs.so  (36.88gb)

                                                              SUM (153.92gb)

As this looks like it is a SAP BW system, I assume that you don't shutdown and unload all tables before you try to copy the table.

Now, while you copying a column store table, this single activity ends up in multiple separate tasks in SAP HANA.

1) the source table main + delta store are kept in memory completely (including indexes, existing join translation tables, etc.)

2) an index of visible rows is kept in memory to ensure that a consistent view of the data gets transferred.

3) the data needs to get "materialised" and then inserted into the delta store of the target table. The materialisation happens in chunks so this requires memory for the materialisation and memory for the whole delta store of the target table.

4) the delta store does not compress the data but in fact blows it up to a certain degree.

5) if delta merge has not been disabled and is left at default (delta merge to disk), every now and then a second copy of the target table gets created. When the delta merge kicks in, we end up with

  orig. table (main+delta)

+ target table (main1 + delta1 + main2 + delta2)

+ delta merge processing memory

+ materialisation buffer

6) part of the usual delta merge is also to write the results to disk. This again requires memory as space in the persistency needs to be allocated.

Once this is done, the main1 and delta1 of the target table can be deallocated.

The new main2 should be considerably smaller than main1+delta1 before the merge.

But since the insert operation continues while the delta merge is happening, delta2 will take up considerable amount of data by now.

So far to the processing side of the story.

You mentioned that the table currently has 25 GB in size. How did you  get to this value?

Where all columns fully loaded into memory when you retrieved the numbers?

Looking at this thread so far I have the feeling that this would be best handled via a support message.

So, I recommend to open one and have the colleagues check in detail, why there is not enough memory for this table copy.

- Lars

[former_member183326]

Hello,

It looks as though you are running into a memory issue.

Please look at note 1999997.

Please check if your GAL is properly set. Also check to see if you have a memory limit set.

You could try the following....but again I am not sure if this is relevant.

1.Set back the parameter global.ini -> memorymanager -> statement_memory_limit to default value

.

2.Try to copy again

3.Once this executes go back to global.ini > memorymanager > statement_memory_limit and reset to the previous configuration.

Could you also tell me if you are using a stand alone stats server or embedded?

Please run the mini checks from SAP Notes 1969700, 1999993.

These checks will let you know what needs to be changed or seen to.

Regards,

Michael

[Former Member]

Hi Michael,

Yes, It is due to memory. But we have 138 GB of free memory before I starting copying the data from(/BIC/APWBSTRPR00B) one table to another(/BIC/APWBSTRPR00).

What i dont understand is ... why does HANA runs OOM for copying 21 GB (Table size) from one table to another ?

I do understand that it will have delta memory. According to my understanding it should not use more that 42 GB (double size of table 21*2) during any kind of operation.

Also, I check statement_memory_limit parameter. It is maintained with default values.

Regards,

Pavan Gunda