====== HANA delta storage monitoring ====== This monitor is dedicated to the delta store of the HANA column store tables, and to the delta merges that move it into the main store. It watches the largest delta stores and reports for each of them its size, its share of the table, its sustained growth with an estimate of the time left to the size threshold, and the time since the last merge. Each watched table also gets a diagnosis explaining why its delta is not shrinking. It can also raise an alarm when the automatic delta merge is switched off. **Note:** a delta store that keeps growing slows down every read of the table and holds memory until the next merge. The monitor reads ''M_CS_TABLES'' for the delta sizes, ''M_DELTA_MERGE_STATISTICS'' for the merges, ''M_SERVICE_MEMORY'' for the allocation limits and ''M_INIFILE_CONTENTS'' for the merge settings. Sizes are memory sizes: a table that is not loaded has no delta in memory and is not reported. ===== Prerequisites ===== * The monitoring user needs the ''CATALOG READ'' system privilege, included in the ''MONITORING'' role. ===== Configuration hints ===== * Use the surveillance table to adjust the monitoring settings: * Thresholds * Severity * Filters * The tables are read once per run and shared by all surveillance lines. Each line only filters them, so adding a line does not add any load on the database. * Each partition of a partitioned table is watched as a table of its own. ===== Global parameters ===== ^ Parameter ^ Default ^ Description ^ | Collection limit | ''50'' | Defines the maximum number of tables fetched and tracked per run, the largest delta first. \\ When **Send metrics** is enabled with **Top tables** above 0, the largest deltas are fetched whatever their size. Otherwise only the tables whose delta reaches the smallest of **Min delta size** and the absolute **Max delta size** of the active lines are fetched, and smaller deltas are never read. \\ The growth history is only kept for the tracked tables. A table that leaves the collection and comes back starts a new history. | | Growth period | ''30'' minutes | Defines the period over which the delta growth is measured, in minutes. \\ The growth is the current delta size minus the delta size one period ago, within 5 minutes. A growth is **sustained** when it is positive and the delta is still larger than half a period ago, so a delta that grew then started shrinking is not reported as growing. \\ The first growth of a table is known one period after the table entered the collection. Before that the growth checks cannot alarm on it. \\ Set it to at least twice the schedule of the monitor. With a shorter period no sample exists half a period ago, the growth is decided on the full period only, and the monitor log shows a warning. \\ The period also defines which merges are used for the diagnosis: the merges that ended during the period. | | Min delta size | ''100M'' | A size with unit, such as ''100M'' or ''2G''. \\ Tables with a smaller delta are ignored by the percent, growth and merge age checks: a small delta growing fast or never merged is not a problem. The size check is not affected. | | Merge configuration severity | ''Disabled'' | Defines the severity of the alarm raised when the automatic delta merge is switched off in ''indexserver.ini'', section ''mergedog'': ''active'' or ''smart_merge_enabled'' set to ''no'', ''false'', ''0'' or ''off''. \\ The effective value is the one of the most specific layer: HOST, then DATABASE, then SYSTEM, then DEFAULT. \\ One alarm is raised per setting switched off, independently of the surveillance table. \\ ''Disabled'' turns the check off. | | Send metadata | enabled | If enabled, the watched tables are sent as metadata to the configured plugins/integrations. It can usually be retrieved as logs or tabular data within the corresponding integrations. \\ The tables matched by an active surveillance line are sent, with their diagnosis, whether they breach a threshold or not. \\ Each row carries ''host'', ''port'', ''schema'', ''table'', ''partId'', ''deltaBytes'', ''deltaPercent'', ''growthBytes'', ''bytesPerMinute'', ''minutesToThreshold'', ''lastMergeTime'', ''mergeCount'' and ''diagnosis''. \\ ''growthBytes'' and ''bytesPerMinute'' are empty until the growth of the table is known, ''minutesToThreshold'' is empty when the delta is not growing or no size threshold is set. | | Send metrics | disabled | If enabled, the metrics described below are sent to the configured plugins/integrations. | | Top tables | ''20'' | Defines how many tables get their own metric series, tagged with the table and the partition: the largest deltas first, among the tables matched by an active line. \\ Set it to ''0'' to disable the per table metrics and keep only the host totals. \\ The top tables are sent even when their delta is below **Min delta size**, so a chart keeps the same tables as long as they hold the largest deltas. \\ The tables are taken from the fetched ones, so a value above the **Collection limit** sends at most **Collection limit** tables. \\ This setting caps the number of series sent to the integrations: a database holds thousands of column store tables. | ===== Surveillance table ===== ^ Parameter ^ Description ^ | Active | Use this field to activate or deactivate a line of configuration. | | Host | A filter to match only a given host. | | Schema | A filter to match only the tables of a given schema. | | Table | A filter to match only a given table. | | Max delta size | The threshold for the delta size. Either a size with unit, such as ''512M'' or ''2G'', or a percent of the indexserver effective allocation limit of the host, such as ''0.5%''. An alarm is sent when the delta reaches this size. Leave at ''0'' to disable this check. | | Max delta percent | The threshold for the share of the delta in main plus delta memory, in percent. An alarm is sent when the share reaches this value. Only checked on deltas above **Min delta size**. Leave at ''0'' to disable this check. | | Min growth | The threshold for the delta growth over the **Growth period**, a size with unit such as ''200M''. An alarm is sent when a sustained growth reaches this value. Only checked on deltas above **Min delta size**. Leave at ''0'' to disable this check. | | Max merge age (min) | The threshold for the time since the last merge, in minutes. An alarm is sent when a table with a sustained growth was not merged for that long. Only checked on deltas above **Min delta size**. Leave at ''0'' to disable this check. | | Aggregate | If checked, one alarm per check gives the number of tables reaching the threshold. If not checked, one alarm is sent per table and check. | | Severity | The severity of the alarm. ''Disabled'' excludes the matched tables from the lines below. | | Auto clear | If checked, the alarm is cleared automatically once its condition is no longer met. | | Alarm tag | A text used as a prefix of the alarm message of this line. | | Alarm | If checked, this line of surveillance will be used for alarm generation. | **Note:** the lines are evaluated from top to bottom, and a table can match several lines. A line with severity **Disabled** removes the tables it matches from the lines below it: put it first to exclude a schema or a table from a catch-all line. **Note:** filters match any value containing the text, case insensitive. ''*'' matches any value and a leading ''!'' excludes the values containing the text. To exclude several unrelated values, join them with ''+'': ''!ZSTAGING+!ZTEMP''. Do not use a comma for this: ''!ZSTAGING,!ZTEMP'' only excludes the first one. **Note:** a percent **Max delta size** needs the allocation limit of the host. When none can be read, the check is skipped and the monitor log shows a warning. **Note:** the merge age check only alarms on a table that is still growing. A large table that is rarely written is rarely merged too, and that is not a problem. A table never merged since the start of HANA has no merge age and cannot reach this threshold. ===== Diagnosis ===== * Each watched table gets one diagnosis, the first matching label of the list below. * It is sent in the metadata and helps to tell a merge that fails from a merge that is only late. ^ Diagnosis ^ Description ^ | FAILING | The delta grows and at least one merge of the period failed with an error that needs an action: smart merge disabled, out of memory or internal error. Check the indexserver traces and the OOM dumps. | | BACKLOG | The delta is over **Max delta size**, grows, and no merge succeeded during the period or the merge age is over **Max merge age**. The merges cannot keep up with the writes. | | SKIPPED | The delta grows and at least two merges of the period were skipped: a merge already running, a smart merge queued or an empty delta. | | RECLAIM_PENDING | The last merge succeeded but the old main store is still in use by open transactions, so its memory is not freed yet. Look for long running transactions. | | RISING | The delta grows with no sign of a merge problem. | | FALLING | The delta shrank over the period. | | STABLE | None of the above. | ===== Alarm content ===== * Without **Aggregate**, one alarm is raised per table, partition and check. The message contains the table, the host, the value and the threshold with their units: * ''Delta storage of SAPABAP1.ACDOCA on host hana01 is 3.2 GB (>= 2 GB)'' * ''Delta of SAPABAP1.ACDOCA on host hana01 is 42.5% of main plus delta (>= 30%)'' * ''Delta storage of SAPABAP1.ACDOCA on host hana01 grew by 600 MB in 30 min (>= 500 MB), now 1.4 GB, about 36 min to 2 GB at the current rate'' * ''No delta merge for SAPABAP1.ACDOCA on host hana01 since 180 min (>= 120 min), delta 1.4 GB and growing'' * A percent **Max delta size** shows both the percent and the size it stands for: ''(>= 0.5% of the allocation limit, 2.5 GB)''. * The growth alarm gives the time left to **Max delta size** at the current rate when that threshold is set and not yet reached. * With **Aggregate**, one alarm is raised per line and check, with the number of tables: * ''4 tables with delta storage over 2 GB'' * ''2 tables without delta merge for more than 120 min'' * A switched off merge setting raises its own alarm: ''Automatic delta merge setting smart_merge_enabled is no in indexserver.ini [mergedog]''. * In integrations that show the metric of an alarm, a size alarm is reported under ''DELTA_STORAGE_TOP_SIZE_MB'', a percent alarm under ''DELTA_STORAGE_TOP_PERCENT'', a growth alarm under ''DELTA_STORAGE_TOP_GROWTH_MB'' and a merge age alarm under ''DELTA_STORAGE_TOP_MERGE_AGE_MINUTES''. The merge setting alarm carries no metric. ===== Examples ===== ==== Example 1 ==== ^ Active ^ Host ^ Schema ^ Table ^ Max delta size ^ Max delta percent ^ Min growth ^ Max merge age (min) ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ | true | * | * | * | 2G | 0 | 500M | 120 | false | MAJOR | true | | true | **Effect**: Sends a MAJOR alarm for each table whose delta reaches 2 GB, for each table whose delta grew by 500 MB or more over the growth period, and for each growing table not merged for 2 hours. The growth and merge age checks ignore the deltas under the minimum delta size. ==== Example 2 ==== ^ Active ^ Host ^ Schema ^ Table ^ Max delta size ^ Max delta percent ^ Min growth ^ Max merge age (min) ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ | true | * | ZSTAGING | * | 0 | 0 | 0 | 0 | true | Disabled | true | | true | | true | * | * | * | 0.5% | 30 | 0 | 0 | true | WARNING | true | | true | **Effect**: The first line excludes the tables of schema ZSTAGING. The second line sends one WARNING alarm with the number of the other tables whose delta reaches 0.5% of the allocation limit, and one with the number of tables whose delta is 30% or more of the table. ===== Generated metrics ===== ^ metricId ^ metricUnit ^ metricTarget ^ metricDescription ^ | DELTA_STORAGE_TOP_SIZE_MB | MegaBytes | [HOST][TABLE][PARTITION] | Sends the delta size of a top table. | | DELTA_STORAGE_TOP_PERCENT | Percent | [HOST][TABLE][PARTITION] | Sends the share of the delta in main plus delta memory, rounded to one decimal. | | DELTA_STORAGE_TOP_GROWTH_MB | MegaBytes | [HOST][TABLE][PARTITION] | Sends the delta growth over the growth period. A shrinking delta gives a negative value. | | DELTA_STORAGE_TOP_MERGE_AGE_MINUTES | Minutes | [HOST][TABLE][PARTITION] | Sends the time since the last merge of a top table. | | DELTA_STORAGE_TOTAL_SIZE_MB | MegaBytes | [HOST] | Sends the delta size of all column store tables of the host, not limited by the collection limit. | **Note:** values in MB are rounded to whole megabytes. **Note:** the TABLE target holds ''SCHEMA.TABLE''. The PARTITION target is only present for a partition of a partitioned table. **Note:** the per table metrics are sent for the **Top tables** largest deltas matched by an active line, once per run even when several lines match the same table. A table excluded by a line with severity **Disabled** is not ranked. **Note:** a table with no value for a metric is not sent, rather than being sent as zero. This applies to the growth of a table during its first growth period and to the merge age of a table never merged.