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:
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:
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 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.
No comments:
Post a Comment