Copying table via SQL - disk space discrepancy

[patrickbachmann]

Hi folks,

I have used SQL editor to 1) copy the data definition of a table from table A to table B and 2) copy all records from table A to table B using an INSERT statement.  It has worked well and when I do a record count the two tables now have EXACTLY the same amount of records. 

However, when I look at administration monitoring and look at tab SYSTEM INFORMATION/SIZE OF TABLES ON DISK, HANA is showing the new copy being three times larger than the original!  My question is if anybody knows of a bug with the disk size reporting?  From what I can tell the entire definitions and contents (record counts) of each are exactly the same so I'm baffled why it's reporting the copy as being larger.  We are hoping that restarting HANA may fix the discrepancy but thought I would post this question first in case anybody has seen this before.

Thanks.

[Former Member · Accepted solution]

Hi Patrick,

What do your results look like if you run the following query for both tables.

SELECT SCHEMA_NAME,TABLE_NAME,MEMORY_SIZE_IN_TOTAL,MEMORY_SIZE_IN_MAIN,MEMORY_SIZE_IN_DELTA

FROM M_CS_TABLES

Just add a where clause to filter out the TABLE_NAME you are comparing.

[former_member184768 · Accepted solution]

Hi Michael,

I think, the query you mentioned will provide the memory consumption and not the disk space.

Regards,

Ravi

[patrickbachmann · Accepted solution]

Anjan, the field properties are indeed all the same and both tables are columnar. 

Michael, I will try that just for curiosity sake.

All, we have restarted HANA and now the disk space appears to be reporting accurately.  We've had this problem before where we don't believe the disk space numbers and restart and the numbers are refreshed.  Anybody have any idea why we have to restart to get correct numbers?

Thanks!

[patrickbachmann · Accepted solution]

Hi Michael, I tried your SQL because I was curious and it pulls up my table name but strangely the memory size (for all three size metrics) is showing 0.  If I run it without specifying a table I get a complete list of tables but all have 0 for metrics except for system tables such as _SYS_STATISTICS or SYS_REPO.  Something does not seem to be updating the data correctly.

Thanks

[patrickbachmann · Accepted solution]

Actually I wonder if these tables aren't moved to memory yet from disk is because we just restarted HANA this morning...

[former_member184768 · Accepted solution]

You are right. The tables won't be loaded to the memory after restart unless they are loaded explicitly using LOAD statement or queried or merge operation is performed.

Please check the ESTIMATED_MAX_MEMORY from the same M_CS_TABLES. It will give you an idea of how much memory might be consumed if the table is loaded to the memory.

I think regarding the mismatch in the disk space number, it might the issue with the garbage collector. HANA doesn't free up the memory unless required as the release of memory back to resource pool and allocation of the memory blocks from the resource pools are expensive operations. Not sure if the same is applicable to the disk space. May be, it is.

Regards,

Ravi

[patrickbachmann · Accepted solution]

Good information everyone.  Thanks a million.

[former_member191765 · Accepted solution]

Ravi,

Just on curiosity.. "HANA doesn't free up the memory unless required " Is this true..? I believe any allocation memory should be free when the main session is done with operation.

[former_member184768 · Accepted solution]

Hi Anjan,

I raised OSS note earlier (revision 31) with SAP on memory getting blocked in MAIN / DELTA memory despite the fact that the table data has been deleted. Also even if the merge operation is carried out, you can see 0 records in DELTA, but still some memory is consumed in DELTA.

In reply to the OSS message, it was mentioned that the release of memory back to pool and allocation of memory blocks from the pool is an expensive operation.

I think other members from SAP on this community can provide the confirmation on such behavior.

Regards,

Ravi

[patrickbachmann]

Folks, I just wanted to update you on new findings about this issue.  I had a message open with SAP and they suggested to upgrade to 55.  After upgrading to 55, the disk space utilization is now refreshing automatically without having to restart HANA!   I did a test where I copied a bunch of data from one table to another and for a few minutes the disk size on the new copy was inflated but then automatically shrunk to expected size.

-Patrick

[Former Member]

Hi Patrick,

even though this question is marked as solved I'd like to raise another possible explanation.

After copying the table's contents into another table, the data is stored in the table's delta memory, where the contents require more memory than in the table's main memory.

Can you confirm that a delta merge had been executed and finished when you checked the table's size? You could also check the table's runtime information in the table definition, to see how much memory is used in main and delta. A factor of three sounds plausible for delta compared to main.

Regards,

Markus

[former_member184768]

Hi Markus,

I think there is a confusion here. Patrick mentioned about Disk size and your point is related to the memory size.

If you look into the runtime Information for the table, you can see these two values separately. The memory size is related to the MAIN and DELTA memory, but disk size is related to the physical storage.

@ Patrick, can you please confirm which parameter did you raise the question about.

Regards,

Ravi

[patrickbachmann]

Yes my original question was about disk space size being much bigger which was resolved with the restart.  The memory size tips was a bonus for me.  I will have to research this delta merge to fully understand it.  When I glance at several of my tables now randomly I see there is a small amount of delta space being used for each, including this table that is now reporting correctly after the restart.  I'm copying some more data today from table to table so I will keep a watchful eye on the delta size etc and see how each size metric is affected through the process.

Thanks

[Former Member]

But also the disk size would be significantly larger when the table has not been merged compared to the original table.

When comparing the memory size to the table size of the table, a large difference could also be caused when a table has recently been merged (therefore small in memory), but no savepoint occured yet (on disk the delta still exists). However, this timespan should be rather small, as the default configuration triggers a savepoint every 5 minutes.

[patrickbachmann]

Markus you have hit the nail directly on the head.  My SAP rep has just sent me a document explaining the delta merge process and I'm not finished going through it yet but it seems to explain the entire disk size fluctuation mystery for me.  So thank you so much for continuing to add to this thread, it's much appreciated.

[former_member184768]

, This is the benefit of having members from SAP on this forum. The knowledge base with them is vast compared to us mere mortals, who try to survive on the borrowed knowledge.

This has been very informative discussion. Thanks to all of you.

Regards,

Ravi

[Former Member]

Hello Markus,

Can you help me to know where i can set the timing for "Save point". if want it to be for every 10 minutes instead of 5 minutes?

Regards,

Krishna Tangudu

[patrickbachmann]

Ok guys, now that I read everything I can learn about DELTA MERGE I did another test and there definitely seems to be a problem with DISK SIZE getting updated.  I copied 1 year of data from BSEG table into a new table called BSEGCOPY.  I watched it copy for a couple of hours and I could see the DELTA SIZE increase steadily until it reached about 19,000,000 KB.  Then when it was done the AUTO MERGE seemed to work and everything was moved from DELTA AREA into the MAIN MEMORY area.  The main memory now shows around 19,000,000 KB and the DELTA SIZE is shrunk to around 2,000 KB (it never seems to ever reach 0 but obviously the delta merge has successfully moved the data to main memory).

So the memory data metrics seem correct. However when I look at the physical DISK SIZE of the new copy it is insanely bigger than original file. 

BSEG SIZE   69,924,208,640

COPY SIZE 405,843,570,688 <----WOW

Not only is the copy many times larger than the original but the original also has much more records than the copy!  So I'm positive that restarting HANA will fix this number (because it resolved the problem before) but what is causing this to not update?  And yes the table definitions are identical.  Should I open a new discussion?

Thanks!

[Former Member]

Hi Krishna,

it's in configuration -> global.ini -> persistence -> savepoint_interval_s

Regards,

Markus

[Former Member]

Thank you so much Markus.

[Former Member]

Patrick,

This raises a big alarm now!! Thanks for carrying out the test and sharing it. I guess it opens up 2 issues -

1. Disk Size - Whether the size is actually big or it is only temporary number.

2. record Count - I still cant believe the record count of the copy is higher.

I guess you should open a new discussion.

[patrickbachmann]

Hi Bikas, I had another partner do some testing and he experienced the same thing with the disk size on the copy being larger and suggested I create an OSS note.  I just want to clarify what I mean by record count.  I meant the original has more records than the copy and yet the copy is still strangely larger than the original.  So the record counts in both the original and copy are correct.  The original has aproximately 10 years or so of data and the copy has 2 years and yet the copy is many times larger.

[patrickbachmann]

Here is my response from SAP on this issue, which some of you will find interesting especially Ravi whom mentioned the garbage collector.

FROM SAP

It needs some time for the copied disk size to be equal to the

original.(without restarting)

The disk_size column in m_table_persistence_statistics is showing the

number bytes that is currently occupied by any part of the table.

Beside the current actual payload this includes additional bytes because the persistence is storing the data in pages with multiple fixed sizes

that could result in significant overhead compared to the actual

payload.

Further more this size might include bytes that are no longer used by

the table but are not released yet. They will be relase only later by

some garbage collection.

[former_member191765]

Just.. to make sure.. how are the fields properties are they same..? and table type row vs. col.