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:

Table%20Size%20Monitoring

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:

Table%20size

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;

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;

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:

New%20Custom%20KPI%20in%20the%20KPIs%20Table

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:

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.

Summary

This is just one of examples how Custom KPIs can be used. Of course, you can extend this idea and adopt to your tasks. For example, you can adjust the SQL and remove the table name filter and monitor the size of the whole schema, or even all column store tables in all schemas.

Another cool idea is to slightly modify the KPI and track only the memory consumption by delta storage in the system.

One more Custom KPI I use very often to track certain memory allocator contribution is 02_heap_allocator.yaml (included with RybaFish). It implements the same idea but instead of table size we track the memory consumption by so-called allocator, based on the other statistics service table: _sys_statistics.host_heap_allocators.

Of course you are not limited by memory analysis, you can apply similar technique to monitor disk usage, or even build trends on some business-tables and so on.