Hana DB Row Store Reorganization

How To Perform Hana DB Row Store Reorganization

Row store memory size is a lot bigger than the actual data size in row store and shows high fragmentation ratio

A row store table requires more memory to store records, the table takes a free page from existing segments. If no segment has a free page, a new segment is allocated. Deleting a large number of records may result in a number of sparse segments. In such a case, row store reorganization is very useful and can be performed for memory compaction.

• Row store reorganization is recommended, when allocated row store size is over 10GB and free page ratio is over 30%.
• Online Row Store Reorganization can be performed for any HANA Database revisions for SPS10 or later
• For HANA 2.0 Database Revision 020 (2.00.020.00), online reorganization is disabled until further notice. Refer to SAP Note 2506825

->Row Store Reorganization can be performed either Online (or) Offline

In this blog I will show how to perform Online Row Store Reorganization

Prerequisites:

HANA Database has to be upgraded prior to running row store reorganization.

Our recommendation is to run it at idle time to maximize compaction.

Row store reorganization is recommended, when allocated row store size is over 10GB and free page Ratio is over 30%. If the result of “Reorganization Recommended” is “TRUE“, then row store memory can be reclaimed after row store reorganization.

If the prerequisites are not satisfied, row store reorganization MUST not be executed. The prerequisites, queries, and monitoring views used for the queries are applicable to Rev 85.01 and Rev.91 and subject to change in future releases.

SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row
store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE),10,2) "Free Space Ratio in%",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size 
in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE ( CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG' ) GROUP BY HOST, PORT;

Estimation on maximum possible memory space that can be reclaimed:

SELECT SUM(NUM_FREE_DATA_PAGES)*16/1024 “Estimated Maximum Memory Saving in MB” FROM SYS.M_DEV_MEMORY_SEGMENT WHERE SEGMENT_TYPE = 0;

Check disk space where log files are located.

SELECT (SUM(NUM_FREE_DATA_PAGES)*16/1024)*1.5 "Estimated Log Volume Size in MB" FROM SYS.M_DEV_MEMORY_SEGMENT WHERE SEGMENT_TYPE = 0;

Online Row Store Reorganization execution steps:

Note: Be informed that online row store reorganization acquires exclusive table lock of a number of tables which are dynamically chosen at runtime of online row store reorganization. Thus, update operations on those tables cannot be done until the online row store reorganization finishes reorganization of corresponding tables.

1. Upgrade database to Rev.85.01 or later and Rev.91 or later for SPS08 and SPS09, respectively.
2. Make a complete database backup.
3. (Optional but strongly recommended) Confirm that the system is in relatively in idle state.
4. Make a new connection to the system (HANA Studio is recommended)
5. Run Row Store Consistency Checker and check whether the system is in inconsistent state. For Details, refer to SAP Note 1977584.

CALL CHECK_TABLE_CONSISTENCY('CHECK_ROW_TABLES', NULL, NULL);

6. If the result is not empty, DO NOT run online row store reorganization and follow the solution of

SAP Note 1977584.

7. Execute Online Row Store Reorganization command

ALTER SYSTEM RECLAIM DATA SPACE

8. To see the progress, check the “Job Progress” Tab in “Performance” Tab of the HANA Studio

9. Check the indexserver trace files after row store reorganization is done. The trace of Online Row

Store Reorganization is separately managed in indexserver_<hostname>.<port>.row_store_reorg.

<sequence>.trc

– Successful case (Note that the traces can be changed depending on the Revision)

10. Check the memory fragmentation status after reorganization. Due to its limitation stated above,you may encounter the case where reorganization is still recommended even after running online Reorganization.

SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30)
AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE
'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100
/ SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in%",TO_DECIMAL(
SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL(
SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE (
CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG' ) AND PORT LIKE '%03' GROUP BY
HOST, PORT;

11. Repeat step (5) Run Row Store Consistency Checker and check whether the system is in inconsistent state. For details, refer to SAP Note 1977584.

CALL CHECK_TABLE_CONSISTENCY('CHECK_ROW_TABLES', NULL, NULL);

If the result is not empty, open a SAP incident on component HAN-DB in order to get further

Assistance from SAP side. It is recommended to export the result of the consistency check and

Upload it.

Result of this activity is:

We could see the allocated row store memory before reorganization around 32 GB now it is 24 GB, around 8 GB have been reclaimed due to this activity.

After row store reorganization

Before row store reorganization

Hope this blog is useful.Please let me know your experience with SAP Hana DB Reorg