0

After table partitioning(Hash by partition 4) observed increased in total memory consumption on memory on disk. Earlier we did the partition (Hash by partition 12) observed decreased in total memory consumption on memory on disk.

Both tables are SLT tables and memory dumps are taken before resuming slt replication.

What is the reason of either increase or decrease in memory can anyone explain?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Mayankb024
  • 11
  • 2

1 Answers1

0

Without the table and column details (i.e. from system view M_CS_ALL_COLUMNS) we can only speculate about the reasons in this case.

My guess is that the compression of the individual columns now (with 4 partitions) is less efficient than before (12 partitions).
Since HANA does perform compression optimisation - that is, it tries to find the best compression algorithm for every column in a table, given all other columns - this might have changed after the re-partitioning.

Another option is that this compression optimisation actually hasn't been done yet. In that case, you can run
UPDATE <table name> WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE)
to manually trigger it.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thanks for this information, While partitioning we checked the process in "M_JOB_PROGRESS" . Optimize compression was done for each partitioning. Do we still need to run optimize compression. and do we need to pause SLT replication while doing it. – Mayankb024 Mar 29 '20 at 08:14
  • Why don’t you verify the *guess* about lvl of compression optimization by checking the mentioned system table? Don’t just rush to any potential action - understand the actual cause for the problem first. – Lars Br. Mar 29 '20 at 08:46
  • It's normal that different columns have different compression types - as long as you don't see just `DEFAULT COMPRESSION` there has been some compression optimisation. Well, without seeing the contents of `M_CS_ALL_COLUMNS` for that table from *before* the repartitioning we have no idea what changed. Maybe you can return to 12 parts and check what makes up the difference. – Lars Br. Mar 30 '20 at 23:49
  • Compression type is DEFAULT COMPRESSION only for my partitioning column. 4 column has partitioning type as default. – Mayankb024 Mar 31 '20 at 02:58
  • Hi Lars if we applied forced compression, we can change the compression type for those partitioned could which which has compression type default ? will it help to release memory – Mayankb024 Apr 01 '20 at 07:11
  • You're likely looking at columns with rather many distinct values. These typically will end up with DEFAULT compression and, in case of actually unique values, lead to compression ratios > 100%. That's normal behavior and nothing to worry about. As written before: check out what the situation is with more partitions and compare. – Lars Br. Apr 01 '20 at 08:07
  • Thinking of this: you don't seem to be overly familiar with the way the HANA column store works - how do you know about the disk size for the old and the new partitioning? Where did you get the numbers for that? – Lars Br. Apr 01 '20 at 08:08
  • Yes Lars, i am new to HANA. Earlier it was single partitioned and i checked the disk size from table Run time information and memory consumption from M_CS_TABLES. Check out the situation with more partition , you mean to check the memory if we increase the partition from 4 to larger number? – Mayankb024 Apr 01 '20 at 12:32
  • Can you extend the question with the information from `M_CS_ALL_COLUMNS` and the table definition including the partitioning definition? Guessing around what might be the cause for the storage increase is not likely to lead to an answer. – Lars Br. Apr 02 '20 at 00:55
  • I checked M_CS_ALL_COLUMN and observed for partitioning column Memory size in main is greater than uncompressed size . Could you tell more what all fields from M_CS_ALL_COLUMNS table we need to check . I didn't take dump of this table before partitioning. PARTITION BY HASH ("PAOBJNR") PARTITIONS 4 – Mayankb024 Apr 02 '20 at 03:38
  • Hi Lars, Could you please tell me what all information is required from M_CS_ALL_Column table – Mayankb024 Apr 06 '20 at 11:36