HANA tables size on disk is reducing

[Former Member]

Dear Experts ,

While gathering statistics for tables growth in HANA, I found that, for some tables (BSEG,ANLA,etc.), size on Disk has reduced over certain period of time (say a week).Though,there is no deletion of data done. Do you know how disk size is reducing for tables ?  Is there data file reorganisation happening ? If yes , when it is triggered and by which process? Can we trace this ?

Regards

Kartik

[lbreddemann · Accepted solution]

Hi Kartik,

the easiest explanation for this is the delta merge process (you may want to read up on how SAP HANA manages column store data).

Take this example where I create a table and fill it. Automatic delta merge is switched off:

create column table nomerge (id int) no auto merge;

-- fill some random data... run the command a couple of times..

insert into nomerge (select rand()* 1000 from users cross join users);

select table_name, disk_size, page_count from M_TABLE_PERSISTENCE_STATISTICS where table_name ='NOMERGE';

/*

TABLE_NAME  DISK_SIZE   PAGE_COUNT

NOMERGE     215699456   47        

*/

merge delta of nomerge;

/*

TABLE_NAME  DISK_SIZE   PAGE_COUNT

NOMERGE     37904384    11       

*/

See? Once the data is moved into the main store it's way more compact, even in the on-disk representation.

If you'd dig deeper you'll likely find that the in-memory representation of this data is even smaller.

I leave this mystery to you to solve

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for the explanation.

So , what I can derive is --

For a table, In case of no delta merge, data in the delta store is written to disk. After delta merge, data moves to main storage and compressed there, after that data is again written to the disk or you can say re-organised. It means,after delta merge, data in main memory and disk are compressed one with the same compression techniques.

Please let me know if my understanding is correct.

I have gone through many documents on memory management but could not find much detail on how HANA manage column store data in disk.

Regards

Kartik

[lbreddemann · Accepted solution]

"It means,after delta merge, data in main memory and disk are compressed one with the same compression techniques."

No, that doesn't follow from anything I wrote at all.

Typically the actual compression of the data structures on memory and on disk are very different.

But data that is stored in the main store gets persisted in the main store persistence while the delta store is stored differently.

After all, it really doesn't matter that much how the data is stored to disk.

The primary storage is memory for SAP HANA, so whatever ends up on disk is equivalent to a database backup.

- Lars

[Former Member · Accepted solution]

Thanks Lars, its much clear now.

Regards

Kartik

[Former Member]

Can you run SQL: "HANA_Tables_ColumnStore_TableSize_History" (SAP Note 1969700) for a table that has shown a size reduction on disk within the statistics server retention time (typically 30 days)? You have to replace 'QRFC_I_QIN_LOCK' with the name of the table showing the size reduction:

            TO_TIMESTAMP('01.01.1000 00:00:00', 'dd.mm.yyyy hh24:mi:ss') BEGIN_TIME,

            TO_TIMESTAMP('31.12.9999 23:59:00', 'dd.mm.yyyy hh24:mi:ss') END_TIME,

            '%' HOST,

            '%' SCHEMA_NAME,

            'QRFC_I_QIN_LOCK' TABLE_NAME,

            'PARTITION' OBJECT_LEVEL,

            'DAY' TIME_AGGREGATE_BY    

What is the output?

[Former Member]

Hi Martin,

Thanks for you comment. I followed the steps. Here is the output for table ANLH.

However , I am not able to derive anything from the output. I think all the data is related to memory usage not the disk usage of the table.

SAMPLE_TIME	TABLE_NAME	RECORDS_TOTAL	RECORDS_MAIN	RECORDS_DELTA	TOTAL_GB	MAIN_GB	DELTA_GB	DELTA_PCT	MERGES
14-10-2014	ANLH	24866380	24793481	72899	0.63	0.62	0.01	2	0
13-10-2014	ANLH	24815457	24793481	21976	0.62	0.62	0	0.67	0
12-10-2014	ANLH	24793499	24793481	18	0.62	0.62	0	0.01	0
11-10-2014	ANLH	24793481	24793481	0	0.62	0.62	0	0	0
10-10-2014	ANLH	24885489	24743396	142093	0.55	0.53	0.02	3.87	1
09-10-2014	ANLH	24893814	24738843	154971	0.52	0.5	0.02	4.32