Which of the Following Redistribution Programs Can Significantly

Dear Readers,

In this blog, I am providing footstep past pace details about how to perform table redistribution and repartitioning in a BW on HANA system. Several steps described in this document are specific simply to a BW on HANA system.This activity was performed in a HANA 1.0 SPS12 arrangement.

In a scaled-out HANA system, tables and table partitions are spread across several hosts. The tables are distributed initially equally part of the installation. Over the fourth dimension when more and more information gets pumped into the system, the distribution may get distorted with some hosts holding very large amount of data whereas another hosts belongings much lesser amount of data. This leads to higher resource utilization in the overloaded hosts and may lead to diverse problems on those like higher CPU utilization, frequent out of retentiveness dumps, increased redo log generation which in plough can cause problems in organization replication to the DR site. If the rate of redo logs generation in the overloaded host is college compared to the rate of transferring the logs to the DR site, it increases the buffer total counts and puts pressure on the replication network between the master nodes and the corresponding nodes in secondary.

The tabular array redistribution and repartitioning operation applies advanced algorithms to ensure tables are distributed optimally across all the agile nodes and are partitioned appropriately taking into consideration several parameters mainly –
➢ Number of partitions
➢ Memory Usage of tables and partitions
➢ Number of rows of tables and partitions
➢ Table classification

Apart from these, in that location are several other parameters that are considered by the internal HANA algorithms to execute the redistribution and repartitioning chore.

DETERMINING WHETHER A REDISTRIBUTION AND REPARTITIONING IS NEEDED

You tin can consider the following to decide whether a redistribution and repartitioning is needed in the system.

➢ Execute the SQL script "HANA_Tables_ColumnStore_TableHostMapping" from OSS note 1969700. From the output of this query, you can see the total size of the tables in deejay across all the hosts. In our system, as y'all tin can see in the screen shot beneath the distribution was uneven with some nodes holding too much data compared to other nodes.

➢ If yous notice frequent out of memory dumps are getting generated in few hosts due to high memory utilization of those hosts by column store tables. You tin execute the following SQL statement to see the retentiveness space occupied by the column shop tables.
select host, count(*), round(sum(memory_size_in_total/1024/1024/1024)) equally size_GB from m_cs_tables group by host guild by host
As y'all can encounter in the below screen shot, in some hosts the memory space occupied by the column stores tables is much higher compared to other hosts.

➢ If new hosts are added to the system, tables are non automatically distributed to those. Only new tables created afterwards the addition of hosts may get stored there. The optimize table distribution activity needs to be carried out to distribute tables from the existing hosts to the new hosts.

➢ If besides many of the following mistake message are showing upwards in the indexserver trace file, the table redistribution and repartitioning action also takes of these bug.
Potential performance problem: Table ABC and XYZ are split up by unfavorable criteria. This will not forbid the data shop from working but it may significantly degrade operation.
Potential performance problem: Table ABC is split and table XYZ is split by an appropriate criterion but respective parts are located in different servers. This will not prevent the information store from working but information technology may significantly degrade performance.

CHECKLIST

✓ Update tabular array "table_placement"
✓ Maintain parameters
✓ Grant permissions to SAP schema user
✓ Run consistency check report
✓ Run stored procedure to check native HANA table consistency and itemize
✓ Run the cleanup python script to make clean virtual files
✓ Check whether at that place are whatever business tables created in row store. Convert them to column store
✓ Run the memorysizing python script
✓ Take a database backup
✓ Append crontab jobs
✓ Save the electric current table distribution
✓ Increase the number of threads for the execution of tabular array redistribution
✓ Unregister secondary system from primary (if DR is setup)
✓ Stop SAP application
✓ Lock users
✓ Execute "optimize table distribution" functioning
✓ Startup SAP
✓ Run pinch of tables

STEPS IN DETAILS

Update tabular array "table_placement"

OSS notation 1908075 provides an attachment which has several scripts for different HANA versions and different scale out scenarios. Download the attachment and navigate to the binder as per your HANA version, number of slave nodes and corporeality of retention per node.
In the SQL script, replace the $$PLACEHOLDER with the SAP schema proper name of your organization. Execute the script. This will update the tabular array "Table Placement" under SYS schema. This tabular array will be referred by HANA algorithms to take decisions on the tabular array redistribution and repartitioning activity.

Maintain parameters

Maintain HANA parameters as recommended in OSS notation 1958216 according to your HANA version.

Grant permissions to SAP schema user

For HANA i.0 SPS10 onwards, ensure that the SAP schema user (SAPBIW in our case) has the organization privilege "Tabular array Admin".

Run consistency check study

SAP provides an ABAP report "rsdu_table_consistency" specifically for SAP systems on HANA database. Commencement, ensure that you use the latest version of this report and utilise the OSS note 2175148 – SHDB: Regard TABLE_PLACEMENT in schema SYS (HANA SP100) if your HANA version is >=SPS10. Otherwise you lot may get curt dumps while executing this report if you select the option "CL_SCEN_TAB_CLASSIFICATION".
Execute this report from SA38 especially by selecting the options "CL_SCEN_PARTITION_SPEC" and "CL_SCEN_TAB_CLASSIFICATION". (You can select all the other options equally well). If any errors are reported, fix those by running the report in repair way.

Annotation: This report should be run after the table "table_placement" is maintained as described in the first stride. This report refers to that table to determine and fix errors related to table classification.

Run stored procedure to check native HANA table consistency and catalog

Execute the stored procedures check_table_consistency and check_catalog for the non-BW tables and ensure in that location are critical errors reported. If any critical errors are reported, set up those first.

Run the cleanup python script to clean extra virtual files

If in that location are extra virtual files, the table redistribution and repartitioning operation may fail. Run the python script cleanupExtraFiles.py available in the python_support directory to make up one's mind whether there are whatever extra virtual files. Before you run the script, open up it in VI editor and alter the post-obit parameters every bit per your system.
self.host, cocky.port, self.user and self.passwd
Execute the following command first to determine whether there are any actress virtual files.
Python cleanupExtraFiles.py
If this command reports extra virtual files, execute the command over again with remove option to cleanup those.
Python cleanupExtraFiles.py –removeAll

Check whether at that place are any business tables created in row shop

The table redistribution and repartitioning operation considers only cavalcade store tables and non row store tables. So, if anybody has created whatsoever business tables in row store (by fault or without knowing the implications) those will not get considered for this activity. Large BW tables are not supposed to exist created in row store in the showtime place. Catechumen those to column store using the below SQL
Change table <table_name> cavalcade;

Run the memorysizing python script

Before running the bodily "optimize tabular array distribution" task, execute the below command –
Call reorg_generate(6,'')
This will generate the redistribution plan simply not execute it. The number '6' here is the algorithm id of "Balance mural/Table" which gets executed by the "optimize tabular array distribution" operation.
Later the higher up process gets executed, in the same SQL panel, execute the post-obit query-
Create tabular array REORG_LOCATIONS_EXPORT as (select * from #REORG_LOCATIONS)
This volition create the tabular array REORG_LOCATIONS_EXPORT in the schema of the user with which you executed this. Execute the query – Select memory_used from reorg_locations_export

If you run into the memory_used column has several negative numbers similar shown in the screen shot below, it indicates there is a problem.

Y'all tin also execute the below query and check the output.
select host, round(sum(memory_used/1024/1024/1024)) equally memory_used from reorg_locations_export group by host lodge past host
If you lot get the output every bit shown in the beneath screen shot, this indicates that the memory statistics is not updated. If you execute the "optimize tabular array distribution" operation at present, the distribution won't exist even and some hosts may end up having far larger number of tables with high retention usage and whereas others volition take very less number of tables and very less memory usage.

This is due to a HANA internal consequence which has been stock-still in HANA 2.0 where an internal housekeeping algorithm corrects these retention statistics. Every bit a workaround for HANA ane.0 systems, SAP provides a python script that you lot tin can find in the standard python_support directory. (Check OSS annotation 1698281 for more details about this script).

Note: This script should exist executed during low organization load (preferably on weekends).

Afterwards the script run finishes, generate a new program with the aforementioned method equally described higher up and create a new table for reorg locations with a new name, say reorg_locations_export_1. Execute the query – Select memory_used from reorg_locations_export_1. Now you won't be seeing
those negative numbers in the memory_used column. Executing the query – select host, round(sum(memory_used/1024/1024/1024)) as memory_used from reorg_locations_export_1 group by host order by host, will now show much better consequence. As yous can meet beneath, the values in the memory_used column is pretty much even beyond all nodes subsequently executing the memorysizing script and there are no negative values.

Take a database backup

Take a database backup before executing the redistribution activeness.

Suspend crontab jobs

Suspend jobs that you take scheduled in crontab, east.g. backup script.

Save the current table distribution

From HANA studio, go to landscape –> Redistribution. Click on save. This will generate the current distribution plan and relieve it in example it is needed to restore back to the original table distribution.

Increase the number of threads for the execution of table redistribution

For faster execution of the table redistribution and repartitioning operation, you tin set the parameter indexserver.ini [table_redist] -> num_exec_threads to a high value similar 100 or 200 based on the CPU capacity of your HANA system. This volition increase the parallelization and speed upwards the operation. The value should not exceed the number of logical CPU cores of the hosts. The default value of this parameter is xx. Make sure to unset this parameter subsequently the activity is completed.

Unregister secondary arrangement from primary (if DR is setup)

If you have system replication setup between primary and secondary sites, you lot will demand to unregister secondary from chief. If you perform table redistribution and repartitioning with organisation replication enabled, it will slow downwards the action.

Stop SAP application

Cease SAP application servers

Lock users

Lock all users in HANA except the organization users SYS, _SYS_REPO, SYSTEM, SAP schema user, etc. Ensure there are no sessions active in the database before you lot proceed to the next step. This will also ensure that SLT replication cannot happen. All the same if you lot want you lot tin can deactivate SLT replication separately.

Execute "optimize table distribution" operation

From HANA studio, go to mural –> Redistribution. Select "Optimize tabular array distribution" and click on execute. In the adjacent screen under "Parameters" leave the field bare. This will ensure that table repartitioning will also be taken care of along with redistribution. However, if you desire to run only redistribution without repartitioning, enter "NO_SPLIT" in the parameters field. Click on adjacent to generate the reorg plan and then click on execute.

Monitor the redistribution and repartitioning operation

Run the SQL script "HANA_Redistribution_ReorganizationMonitor" from OSS note 1969700 to monitor the redistribution and repartitioning action. Yous tin can also execute the below command to monitor the reorg steps

select IFNULL("Condition", 'Awaiting'), count(*) from REORG_STEPS where reorg_id=(SELECT
MAX(REORG_ID) from REORG_OVERVIEW) grouping by "Status";

Startup SAP

Showtime SAP awarding servers after the redistribution completes.

Run compression of tables

The changes in the partition specification of the tables as part of this action leads to tables in uncompressed form. Though the "optimize table distribution" process carries out compression as part of this activity, due to some bug tables can all the same be in uncompressed form after this activity completes. This will lead to high memory usage. Compression will run automatically afterward the next delta merge happens on these tables. If you want you can perform it manually. Execute the SQL script "HANA_Tables_ColumnStore_TablesWithoutCompressionOptimization" andHANA_Tables_ColumnStore_ColumnsWithoutCompressionOptimization" from OSS annotation 1969700 to get the list of tables and columns that need pinch. The output of this script provides the SQL query for executing the compression.

Result

Equally an outcome of this activeness, the distribution of tables evened out across all the hosts of the arrangement. The memory infinite occupied by column shop tables also became more than or less even. Also you can meet that the size of tables in the main node has reduced. This is because some of our BW projects had created big tables in the master node which take been moved to the slave nodes every bit part of this redistribution activity. This should exist the platonic scenario.

Size of tables on deejay (earlier)

Size of tables on disk (after)

Count and memory consumption of tables (before)

Count and memory consumption of tables (after)

I hope this article helps you all whoever is planning to perform this activity on your BW on HANA arrangement.

Cheers,

Arindam

Referrence:

OSS note 1908075 – BW on SAP HANA: Table placement and mural redistribution

OSS annotation 2143736 – FAQ: SAP HANA Table Distribution for BW

dunniganwheme1970.blogspot.com

Source: https://blogs.sap.com/2018/02/17/table-redistribution-and-repartitioning-in-a-bw-on-hana-system/