Reduction of Disk Size by Packed LOB Container

tao_shen · ‎09-28-2020

Summary

On the purpose of prevent further increasing of LOB entries on table /AIF/PERS_XML, the deletion strategy was carried out on the production system. After the records, which is out of data retention, had been deleted, the number of LOBs on table /AIF/PERS_XML had reduced accordingly; However, the disk size of packed LOB container had never been reduced. This issue had brought the following concerns:

Increased backup runtime

Increased disk size

Increased migration downtime requirement

Impact the runtime of business users vs ideal runtimes

Base on the fact, we had looked into the root cause that prevent the clean up of disk size of packed LOB container and found effective solutions to reduce the disk size within an acceptable duration. The provided methods had been tested on different systems and the results are significant.

The issue was first identified in 2019, when the production S/4HANA sFin was on HANA 2.0 SPS03 at that time. The issue was still observed after the system had upgraded to HANA 2.0 SPS04 revision 46.

In summary:

The root cause of uncleaned disk size is due to HANA internal issues (Described in SAP Note 2950474 and 2910004). Workarounds are available on current HANA revision (2.00.046).

The workarounds need several manual steps which had been recorded in this blog. Further actions should be taken to protect the system from falling into the same issue.

Several tests and implementations had been taken on non-production and production system. The results are significant and can be further used as a implementation routine in daily operation.

Findings

Disk Size of Packed Lob Container

The following table shows an example of the disk size of packed LOB containers. The container id may not be a real one to protect the information.

The 1st container (ID 1,091,226,608,549) with READ_COUNT equal to 0 is the one which contains the information of packed LOB from HANA 1.0. The disk size of this container can be shrunk by the HDBCONS msl check -c <container_id> command, which is described in SAP Note 2222218 and 2910004. After this command had been implemented, the disk size shown in the monitor view M_TABLE_LOB_STATISTICS would not be shrunk until a system restart. The container ID should be convert from decimal number into hexadecimal number, which is 0xXXXXXXXXXX, and then can be used in the HDBCONS command.

The 2nd container (ID 1,091,814,053,503) is the one which contains the information of packed LOB from HANA 2.0. The disk size of this container can be shrunk by table consistency check and repair command (CALL CHECK_TABLE_CONSISTENCY ('REPAIR_HYBRID_LOB_OVERHEAD', 'SAPHDB', '/AIF/PERS_XML’);)

The 3rd line shows a summarized result of FILE LOBs on table /AIF/PERS_XML. The FILE LOBs are the hybrid LOBs which had a individual size greater than 4KB. These LOBs will never be packed and store individually on disk.

Through the AIF deletion jobs , the number of packed LOBs in container (id 1,091,226,608,549) was reduced the most: from 43,706,229 in Nov 2019 to 746,210 in Jan 2020; However, the disk size of that packed LOB container is still the same.

Meanwhile, the number of FILE LOBs also reduced from 36,137,847 in Nov 2019 to 450,890 in Jan 2020 through the AIF deletion jobs. In this case, the disk size of the FILE LOBs was reduced which is as expected.

There is few fragmentation in the FILE LOBs after the cleanup, but not as much as with the packed LOBs.

Disabled LOB garbage Collection in HANA SPS03

The LOB garbage collection was deactivated automatically due to a known HANA issue in SPS03. This issue was triggered when the parameter garbage collect_daily_schedule_s is used. The SAP HANA trace file contains a related entry "LOB garbage collection is now paused" and SQL: "HANA_TraceFiles_MiniChecks" (SAP Note 1969700) reports check ID T0842 ("Deactivation of CS LOB garbage collection").

As a workaround the following option available:

Unset the parameter garbage_collect_daily_schedule_s

Configured parameter garbage_collect_interval_s

Ascribe to the deactivation of LOB garbage collection, the fragmentation of packed LOBs can not be cleaned up in time, which cause additional disk usage overlying the constant size of packed LOB containers. After implement the workaround, we could see the execution of LOB garbage collection without issues.

After the system had been upgraded to HANA 2.0 SPS04 ( >revision 2.00.042). This bug had been fixed. Now in the system the parameter garbage_collect_daily_schedule_s kicked in, which control the LOB garbage collection behavior that was executed once per day.

However, the LOB garbage collection can not help to reduce either the disk size or the fragmentation on packaged LOBs. Thus, on HANA 2.0 SPS04, this function doesn’t help here.

Solutions

Reduce the Size of Packed LOB Container that Generated from HANA 2.0

SAP Note 2950474 - DB table growth in SAP Hana related to Packed LOB’s explains the symptoms that the HANA database may still show a high disk size usage, even after the cleanup of table data and reclaim had been done. It also provides a way to clean up the unused disk space of packed LOB containers, which is generated since HANA 2.0.

The following commands help to check and repair the disk size:

In the test and implementation, both commands had been executed and showed a positive results on disk size reduction of packed LOB containers generated since HANA 2.0. However, this command doesn’t help to shrink the disk size of packed LOB containers which is generated since HANA 1.0. Thus, the HDBCONS commands which is mentioned in SAP Note 2910004 should be further used in combination to reduce the overall disk size holistically.

Both commands are available since HANA 2.00.040.

Reduce the Size of Packed LOB Container that Generated from HANA 1.0

SAP Note 2910004 - Increase Disk Usage After Performing DDL Operation on Table With Packed LOB Column provides the way to additionally reduce the disk size of packed LOB containers, which is generated since HANA 1.0. The reason that the efficient clean up on this containers was blocked may due to at least one of the following DDL statements was executed on this CS table at a point in time when the table used packed LOBs:

ALTER TABLE ... LOB REORGANIZE ...

ALTER TABLE ... DROP ... (only if the dropped column was a LOB column that used packed LOBs)

ALTER TABLE ... ALTER ... (only if the altered column was a LOB column that used packed LOBs)

DROP TABLE ...

The permanent solution is to upgrade HANA to:

Revisions >= 2.00.047.00 (SPS04) or higher

A workaround is available on the current HANA revision:

Revisions >= 037.02 (SPS03) or higher

Revisions >= 042.00 (SPS04) or higher

The command HDBCONS -e hdbnameserver 'distribute e <MSL.HOST>:<MSL.PORT> msl check -c <HEXADECIMAL_NUMBER>’ should be manually executed. HANA restart is required after the implementation of this command to validate the result.

In the test and implementation, this command had been executed and showed a positive results on disk size reduction of packed LOB containers generated since HANA 1.0. Before HANA restart after the implementation, the result from M_TABLE_LOB_STATISTICS didn’t show a change; After the restarted, the result showed that disk size had been reduced significantly.

For details about execution of this command, please refer to this SAP Note 2910004.

In a System Replication Scenario

In a system replication scenario, the result of both consistency check/repair and the HDBCONS command will not be transported to the secondary node, in case the operation mode logreplay(_readaccess) is used. To make the reorg effective and validate the results on both nodes, Two options that can be achieved:

Execute the table consistency check command and the HDBCONS command on both nodes independently.

In operation mode logreplay(_readaccess), the reorganization results by HDBCONS command can not replicated automatically to secondary node. In this case, the HDBCONS command should be performed separately; This also require a separate HANA restart to explicitly validate the result on both node.

Re-initialize a full shipment (re-registry to secondary node)

Instead of implementing the command on both nodes, after the consistency check and HDBCONS command had been triggered on primary node and the primary node has been restarted, a full initialization of system replication can also help to replicate the reorg result to secondary node. This means, no requirement to implement the same on secondary node, since a full DB snapshot will be replicated in the initialization phase. This way can simply the steps that make the implementation effective on both nodes.

LOB Reorganizations

LOB reorganization can help to reduce the size of packed LOBs:

ALTER TABLE "<table_name>" LOB REORGANIZE (<lob_column>) [ONLINE]

However,

With SAP HANA <= 2.00.046 orphan persistence files can remain in case packed LOBs were already used before and a manual cleanup is required.

Concurrent change operations are blocked with transactional table lock (SAP Note 1999998), starting with SAP HANA 2.00.047 you can use the ONLINE keyword to perform an online LOB conversion without locking. The ONLINE keyword is already available with earlier SAP HANA Revisions, but it doesn't actually eliminate the need for a table lock.

The LOB reorganization will reorg not only packed LOB, but also FILE LOBs (IO intensive and time consuming) for potential conversion to/from PACKED and MEMROY LOB. There is no way to use the reorg command to only work on packed LOBs.

Thus the manual triggered LOB Reorganization should only be required in rare cases.

In the test (revision 2.00.037.03), the LOB reorganization showed a significant result on reducing the disk size of LOB containers, as well as combining the LOB containers; However, the whole process took around 4 days to be completed, which is not acceptable on production system.

LOB Garbage Collection

Garbage collection is required for hybrid LOBs. It is performed by the LobGarbageCollector (column store) / GarbageLobFileHandler (row store) threads. LOB garbage collection itself doesn't purge the LOB files on disk level. This is handled at a later time by the persistence garbage collection. Script HANA_GarbageCollection_LOBs_ColumnStore_Reclaims can help to display details of column store LOB garbage collection.

The garbage collection does NOT help to get rid of fragmentation within a packed LOB container.

The garbage collection used to be blocked in production on HANA SPS03 due to a HANA bug. This bug had lead to a situation that no LOB garbage collection is executed. The bug has been fixed in HANA SPS04.

Implementations and Tests

The following table shows the implementation steps on production system. Plan B was used. Depends on the downtime requirement and operation complex, either Plan A or Plan B is practical.

Results in each steps

Before implementing all commands on primary node. The disk size of packed LOB containers has a huge gap compare to binary size.

After implementing all commands on primary node without restart the primary node. The disk size of packed LOB container (id 1,091,226,608,549) didn't change; While the disk size of packed LOB container (id 1,091,814,053,503) was reduced. Both symptoms were expected.

After restart the primary node, the disk size of packed LOB container (id 1,091,226,608,549) was reduced as expected.

The same results noted when implement on secondary node.

Useful Notes

2950474 - DB table growth in SAP Hana related to Packed LOB’s

1977584 - Technical Consistency Checks for SAP HANA Databases

2910004 - Increase Disk Usage After Performing DDL Operation on Table With Packed LOB Column

2222218 - FAQ: SAP HANA Database Server Management Console (HDBCONS)

2169283 - FAQ: SAP HANA Garbage Collection

2905296 - Long Running Version Garbage Collection for Rowstore Tables

2880702 - Removing Large Amounts of Row Store LOB Garbage Cannot Catch up or Fails With "Allocation failed"

Thanks for all colleagues that participated and contributed in solving this issue.