The $delta$ container is not releasing space

[troyj]

Hi,

I am curious if anybody has seen this scenario.

I am involved in a migration effort of erp 617 SP04 to hana 8.x utilizing sum and swpm.

The first iteration of the migration was on hana 8.3. The second iteration was on 85.02.

What I noticed in the second iteration is that the $delta$ container for column tables is not releasing used space after merge operations post swpm work.

One thing to keep in mind is the source for both iterations is the same but different in age by a few months.

Table Example with almost the same records and attribute container values:

First Iteration: the $delta$ container was 16,77,216 bytes after the migration completed.

Second Iteration: the $delta$ container was 82,040,586,240 bytes after the migration completed.

If I load the table, from the second iteration, completely into memory, I can see the total memory size is 13,558,978,829 bytes.

The delta is 37,016 bytes.

After trying to manually merge the table nothing changes pointing to empty space in the $delta$ virtual file container that is not being released.

From a database perspective there is about 800GB of potentially wasted space from this occurring.

Thanks,

Troy

[lbreddemann · Accepted solution]

Hey Troy,

as usual we need data to be able to help you.

Show us the commands you used.

Show us the contents of system views

TABLES

M_CS_TABLES

for the given table.

Also, make sure to check M_DELTA_MERGE_STATISTICS about the _reason_ for apparently not merging the table.

Finally, make sure the delta merge decision function is set to default. I've seen that a migration changes this but sometimes it doesn't seem to get switched back.

- Lars

[troyj · Accepted solution]

Lars,

Thanks for your response.

The queries I am using to compare the data are:

select * from m_table_virtual_files where table_name='PRICAT_K008C';

select * from m_cs_tables where table_name='PRICAT_K008C';

select schema_name,table_name,column_name,compression_type,loaded from m_cs_columns where table_name='PRICAT_K008C';

I attached the output of iteration 1: this is the current baseline data we are comparing future iterations too as it seemed to compress 3x better than iterations 2-4.

Since we blasted the test environment for another migration I just have images for iteration 2 I wasn't able to get all of them attached.

I will upload the data from the next iteration as I expect it to be the same as the last 3.

the virtual files data shows where the $delta$ container is taking up most of the space in the table.

I was able to create a copy of the PRICAT_K008C table and its final size was under 10GB with different column compression types being applied and the delta container correctly resized.

I will also look at the m_delta_merg_statistics data after the next migration test.

Thanks,

Troy

iteration1_results.txt.zip

[lbreddemann · Accepted solution]

Hi Troy,

the provided data is a bit patchy and hard to align.

The first iteration data shows that the delta merge has happened once and that it moved all records from the delta storage to the main storage.

Here the delta merge obviously worked.

The iteration2_cs_1.png shows a completely empty table. So, this really doesn't tell us much.

The data from iteration2_virtual_files.PNG I really cannot align with any of the other data.

However, my best guess would still be that performing a persisted delta merge (that what usually happens) would fix this.

- Lars

[troyj · Accepted solution]

Hi Lars,

Sorry about the alignment. The no_merge_results.csv should have the data aligned better.

I did check the m_merge_delta_statistics view and can see 12 errors for "column store error:[2482] table optimization was not indicated".

In the Hana troubleshooting and performance analysis guide it says if this error occurs frequently review the smart merge cost function with SAP experts. We currently have the default setting for the smart merge formula.

One thing to note is we see this error on the first iteration where we saw the $delta$ container space released.

I also tried merge delta of <table> with parameters ('FORCED_MERGE' = 'ON') and saw the 2482 error.

When looking at the total memory used for the PRICAT_K008C table it is only using 12.6GB of space. The $delta$ container is using 74.6GB of persistence space.

I also tested loading the table into memory and see that the larger $delta$ space slows down the load time even though it appears to be empty.

I appreciate your time for this discussion.

Thanks,

Troy

no_merge_results.txt.zip

[lbreddemann · Accepted solution]

Hey Troy,

I did a quick test on a rev. 94 and couldn't reproduce this.

(as a side note, the delta log implementation changed with SPS 9 so the result wouldn't be comparable on storage container level anyhow).

Please make sure that _all_ your merge related parameters are set to default - the smart merge is just one of the merge types.

If the problem resides, this requires a more in-depth analysis then what is practical to do here on SCN.

Open a support incident for that then.

- Lars

[troyj · Accepted solution]

Thanks Lars,

I appreciate your help on this. We just found note 2144274 last night that talks to this issue and that it is a bug in 8502. the fix is supposed to be in 8503.

I am surprised with all of the searching on the knowledge base that the note just showed up yesterday, but I am glad it is resolved.

Thanks,

Troy

[Former Member · Accepted solution]

how your issue resolved? are you upgraded to SP 8503?

is there any option without migrate to a SAP HANA database Revision 85.03

regards

prasad