SAP HANA - Partitioning Data Volumes

Description

Since SAP HANA 2.0 SPS03 and above versions, data volumes on the indexserver can be partitioned so that read and write operations can run in parallel with increased data throughput.

Each SAP HANA service has one data volume which by default stores its data in one single file. Administrators have the option to partition the data volume for the indexserver so that data files can be segmented and distributed over several stripes. Partitioning is a logical distribution of data pages over different partitions and striping is a physical segmentation of files; using partitioning and striping data can be read or written in parallel threads.


In the case of Network File Systems data can also be written in parallel across multiple connections. Partitioning data volumes in this way will therefore speed up all read/write operations on the volume including savepoints, table optimizations, restarts, table loading operations and backups.

For non-NFS type filesystems the benefits of adopting the feature depends on setup provided by the hardware vendor / TDI design. Please discuss the feasibility with your hardware / storage vendor or team responsible for designing the storage layout.


If you create a new partition on the index server it is added simultaneously to all indexservers in the topology. New partitions become active after the next savepoint on each indexserver, this is shown in the partition STATE value which changes from ACTIVATING to ACTIVE. By default all data volumes have a single partition with the numeric ID zero. A numeric partition ID is assigned automatically to new partitions by the HANA persistency layer. If the partition numbering is for any reason inconsistent across all indexservers then any attempt to add new partitions will fail.


Partition details are saved in the indexserver.ini configuration file:

basepath_datavolume[<partition id>]=<path>

Important Facts


Consider the following for SAP HANA data volume partitioning:

  • Data is not immediately distributed when a data volume partition is added to an existing system. Fresh I/O writes are distributed to the new data volume partition and eventually the database achieves even distribution from a size point of view.

  • If immediate even distribution of data is required, consider using SAP HANA backup and recovery (only file and backint based backup and recovery).

  • Dropping a SAP HANA data volume partition involves reading data from the dropped partition and writing it into existing partitions. Since this could involve significant I/O activity depending on the quantity of reads/writes, such an activity should be performed during low business workload timeframes.

  • In a running system replication setup, you may not be able to drop an active data volume partition as system replication uses data volume snapshot technology. You may see the error "Cannot move page inside/out of DataVolume". In this case it may be necessary to disable system replication functionality, drop the partition, and then setup system replication again.

Syntax

To activate it, you need to first enable on systemdb, a parameter called PERSISTENCE_DATAVOLUME_PARTITION_MULTIPATH :

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('customizable_functionalities', 'PERSISTENCE_DATAVOLUME_PARTITION_MULTIPATH') = 'true' WITH RECONFIGURE;

Later on tenant DB, you can now create the new partitions.

Add a partition for indexserver :

ALTER SYSTEM ALTER DATAVOLUME ADD PARTITION

Add a partition for indexserver at specific path :

ALTER SYSTEM ALTER DATAVOLUME ADD PARTITION PATH '<new path>'

Drop a partition for indexserver by partition id :

ALTER SYSTEM ALTER DATAVOLUME DROP PARTITION <id>

Starting with SAP HANA 2.0 SPS 04 you can optionally specify a system replication site ID :

ALTER SYSTEM ALTER DATAVOLUME ADD PARTITION SYSTEM REPLICATION SITE <site_id>
ALTER SYSTEM ALTER DATAVOLUME DROP PARTITION <id> SYSTEM REPLICATION SITE <site_id>

The default partition with ID zero cannot be dropped. If you drop a partition then all data stored in the partition is automatically moved to the remaining partitions and for this reason dropping a partition may take time. This operation also removes the partition entry from the configuration file.

Monitor Partitions

You can see the current data volume configuration from the following two views:

  • M_DATA_VOLUME_STATISTICS: This provides statistics for the data volume partitions on the indexserver including the number of partitions and size.

  • M_DATA_VOLUME_PARTITION_STATISTICS: This view provides statistics for the individual partitions, identified by PARTITION_ID, and includes the partition STATE value.

Starting with SAP HANA 2.0 SPS 04 you can use SQL: "HANA_Disks_Data_Partitions" (SAP Note 1969700) to display an overview of existing data volume partitions.

Recent Posts

See All

SAP HANA Available, Used & Deprecated Features

In this blog post, I would be sharing a very basic information about SAP HANA features. With every latest revision, we hustle sometimes in finding out whether a SAP HANA feature is newly introduced or