Friday 19 August 2022

Monitoring Table Size in SAP HANA

This is a second blogpost about RybaFish Charts tool, If you never heard about the tool – please check the introduction article.

The real power of RybaFish Charts is in custom KPIs. RybaFish supports three KPI types: regular, gantt and multiline. Today we are going to create regular KPI to track the memory consumption by a certain column store (CS) table:

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials
Table Size Monitoring

Custom KPI configuration might be bit confusing at first, but we will go through all the details step by step.

Data Source


Let’s figure out where we can get information on table size in SAP HANA.

Information on current memory consumption by CS tables is available in monitoring view M_CS_TABLES. It contains a lot of interesting information, but we are only interested in total memory consumption so let’s compose an SQL query providing this information for a certain table:

select schema_name,
    table_name,
    sum(memory_size_in_total) memory_size
from m_cs_tables
where schema_name = 'MY_SCHEMA'
    and table_name = 'MY_TABLE'
group by 
    schema_name,
    table_name
order by sum(record_count) desc; 
 
Output:

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials

We aggregate data because here because partitioned tables represented by separate rows in this monitoring view and we are interested in total memory consumption despite partitions.

This query gives us current memory consumption by the table, but to be able to build a graph we need measurements of memory consumption in different points in time. Luckily, there is a standard statistics service table containing the required information: _SYS_STATISTICS.HOST_COLUMN_TABLES_PART_SIZE.

We can modify the query above to provide table size over the time:

select 
    server_timestamp time,
    sum(memory_size_in_total) table_size
from _sys_statistics.host_column_tables_part_size
where
    schema_name = 'MY_SCHEMA'
    and table_name = 'MY_TABLE'
group by server_timestamp
order by server_timestamp desc;

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials

This information collected by the statistics server every hour.

The required data is already in the database, we only need to plot it. And this is where RybaFish Charts comes makes things super easy: we can set up a custom KPI and put this information on the chart along with standard KPIs.

In addition to timestamp and table size we will need to supplement the output with hostname and port number because RybaFish will do filtering based on those values. We need to add those columns to the query:

select 
    server_timestamp time,
    host,
    port,
    sum(memory_size_in_total) table_size
from _sys_statistics.host_column_tables_part_size
where 
    schema_name = 'MY_SCHEMA'
    and table_name = 'MY_TABLE'
group by server_timestamp,
    host,
    port
order by server_timestamp desc;

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials

Now the query contains all the information we need, we are ready to start composing the custom KPI definition file.

Custom KPI definition


Custom KPIs defined in YAML files located in the “sql” folder of RybaFish Charts. The definition consists of two main pats: “kpis” which provides KPIs description (metadata) and “sql” defining the query to extract data. Metadata includes information like KPI name, chart color, etc.

Custom KPI definition example:

# metadata part:
kpis: [
    {type: 'service',                   # is it host-level or service-level KPI
        name: 'table_size',             # unique KPI name
        group: 'mem',                   # scaling group
        sUnit: 'Byte',                  # source unit of measure
        dUnit: 'MB',                    # destination unit of measure
        sqlname: 'table_size',          # name of the column providing data
        color: '#0CC',                  # chart color, in this case cyan
        label: 'MY_TABLE Size'          # human-readable KPI name
    }
    ]


# SQL part, single query:
sql: >
    select 
        server_timestamp time,
        host,
        port,
        sum(memory_size_in_total) table_size
    from _SYS_STATISTICS.HOST_COLUMN_TABLES_PART_SIZE
    where
        schema_name = 'MY_SCHEMA'
        and table_name = 'MY_TABLE'
    group by 
        server_timestamp,
        host,
        port
    order by server_timestamp desc

Description


type
Can have one of values: ‘host’, ‘service’. ‘host’ type intended for the host-level KPIs and ‘service’ to the service-level KPIs.

name
Name of the KPI, It is used internally by RybaFish and must be unique, otherwise the KPI will be highlighted in red and won’t be available.

group
This field assigns the KPI to a scaling group. All the KPIs in a scaling group have the same scale on the chart so they can be directly compared on the chart. You can see the assigned scaling group in the KPIs table.

There are two pre-defined scaling groups ‘mem’ – related to all the memory KPIs and ‘thr’ – related to threads.

sUnit/dUnit
Those fields allow automatically translate huge numbers in bytes into megabytes or gigabytes which is very useful for memory-related KPIs. dUnit will be used as a unit of measure for this KPI in KPIs table and on the chart legend.

sqlname
Name of the column providing data for this KPI. The SQL query (defined below) must expose this column.

color
KPI color on the chart in HTML format. #00FF00 is bright green, #00AAAA – cyan, etc.

label
This is just a name of the KPI that will be displayed on the chart and in the KPI table.

sql
The SQL query has to provide the following columns:

◉ time: timestamp of the measurement in timestamp sql format
◉ host: corresponding hostname
◉ port: required for KPIs with type “service” type
◉ and one or several measurement themselves, in this case: table_size. All the measurements need to be integer, no floats or decimal types supported.

Note: be careful, according to YAML standard, no [tab] characters allowed in the yaml files, only spaces, so the SQL needs to be indented with the spaces only.

Putting everything together


When the file is ready it needs to be saved into something like table_size.yaml and placed in the “sql” folder of RybaFish Charts.

RybaFish comes with several pre-defined custom KPIs like 01_service_memory.yaml, etc. So you need to put yours next to them.

After the file created you can restart RybaFish or go menu Actions –> Reload Custom KPIs.

KPI named “Table Size” should appear in the list of service-related KPIs in the “Custom” section:

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials
New Custom KPI in the KPIs Table

Note – as the KPI has the ‘service’ type it will be available when a service selected in the hosts table (left), in this case – indexserver, port 30003.

This is it: when you select this KPI, RybaFish will plot table sizes measured every hour on the chart:

SAP HANA, SAP HANA Exam Prep, SAP HANA Exam Certification, SAP HANA Prep, SAP HANA Preparations, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials

In this case you can see how the table was purged (or unloaded) at ~19:30 and this resulted in both memory metrics drop down so you can immediately see if the change in overall memory consumption was a result of this particular table increase/decrease.

In the following posts I will show you how to make the custom KPI more flexible and use variables instead of “hard-coded” values, in this case for the table name.

Due to the complex nature of the Custom KPI description which combines data collection and representation, the definition file also has rather complex structure and, to be honest… I never create Custom KPIs from the scratch, I always make a copy based on one of existing definitions and just adjust it to my needs.

No comments:

Post a Comment