Did you know that running a reorganization or mass cleanup in HANA will increase data volume usage at FS by minimum of 25%-50%
WHY IT IS MANDATORY TO HAVE A CLOSER LOOK AT DATA VOLUME FILESYSTEM DURING TABLE REDISTRIBUTION IN HANA?
In HANA DB, running a mass cleanup or a reorganization will result in higher data volume OS level space(25%-50%) including memory .
The main reason behind this is a delta merge operation . A delta merge needs about twice the size of the table (partition) in memory and on disk to perform the merge. More precisely: size of source main + size of target main + size of source delta. Additionally a temporary working space is needed which is about the size of the largest column of the table (partition)
When we recently performed a redistribution for node removal, one of our data volume increased from 65 % to 97% even before it could complete the operation by 30%. To make it running ,we had either to extend the file system or to look out for other options .We decided to perform na OS level data defragmentation.
On checking further, we found that the specific data volume had more than 30% of its data not reclaimed at OS level using the command below .
select * from SYS.M_VOLUME_FILES WHERE FILE_TYPE = ‘DATA’
The output of the above command showed that USED_SIZE was atleast 30% less than the TOTAL_SIZE(actual size that this data volume was holding at OS level) . ie, space that was used temporarily at OS level was not reclaimed back to the OS file system. And hence there was a need to de-fragment this data .
We ran the below command for that specific data volume and our data volume reduced from 97% to 64% instantly ie , we ran data volume defragment on the affected node to free up unused space occupied at OS level.
ALTER SYSTEM RECLAIM DATAVOLUME ‘<node server name>:3<nn>03’ 150 DEFRAGMENT;
More details on this statement usage can be found in the below email which has the required details .
For running data volume re-org refer my below post for more details.https://blogs.sap.com/2020/09/11/how-to-reclaim-fragmented-space-at-os-level-from-hana-data-volume/
If we were not quick enough to stop the re-org job if there is a heavy growth in filesystem of data volume , we will be in a risk of filling up data volume filesystem. In this case though the database will be available until its log volume goes to full state, SAVEPOINT can not complete and system will turn to be much slower than normal.
Thanks for reading!
Like and leave a comment if it was useful!
Follow for more such posts! 🙂