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.
| Aspect | Offline reorganization | Online reorganization |
| Availability | SAP HANA database is completely unavailable | Currently reorganized table is blocked against changes (transactional object lock) ---------------------------------------------------------------- Read operations to all tables and modifications of other SAP HANA tables are still possible |
| Restart required | Yes (reorganization happens during restart) | No |
| Defragmentation | Maximum defragmentation | Significant, but not always maximum defragmentation |
| Memory footprint | Optimal memory footprint | Increased memory footprint in case of concurrent changes or open transactions |
| Runtime | Optimal | Increased in case of resource or object lock contention |
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
No comments:
Post a Comment