Monitoring agent for SAP HANA V1.0 user guide

Google Cloud provides a custom monitoring agent that collects custom metrics from SAP HANA and sends them to Cloud Monitoring, a monitoring solution built into Google Cloud. With Cloud Monitoring, you can build out dashboards to visualize your SAP HANA metrics and set up alerts based on metric thresholds. For more information, see the Cloud Monitoring documentation.

For a description of the metrics that version 1 of the monitoring agent collects from SAP HANA, see Default metrics and queries in V1.0.

Prerequisites

This guide assumes that you have deployed SAP HANA on Google Cloud using the method described in the SAP HANA Deployment Guide.

Setting the required IAM roles

By default, the monitoring agent uses your Compute Engine virtual machine (VM) instance's default service account. This service account allows binaries running on the VM instance to write metrics to Monitoring.

If you choose to use a different service account, you must manually add the IAM role that provides these permissions to your service account.

To add the required IAM role to your service account:

  1. Go to the IAM & Admin page in the Cloud Console.

    Go to the IAM & Admin page

  2. Select your project and click Continue.

  3. Identify the service account to which you want to add a role.

    • If the service account isn't already on the principals list, it doesn't have any roles assigned to it. Click Add principal and enter the email address of the service account.
    • If the service account is already on the principals list, it has existing roles. Click the current role dropdown list for the service account that you want to edit.
  4. Select Monitoring > Monitoring Metric Writer from the list of available roles.

  5. Click Add or Save to apply the roles to the service account.

Installing the agent

Choosing a deployment location

The monitoring agent can be run directly on one or more nodes in your SAP HANA installation, or indirectly as a service on another VM instance. Choose your deployment location based on your use case.

Downloading and installing the agent

To download and install the agent:

  1. Establish an SSH connection with your SAP HANA VM instance.
  2. Download and run the installation script:

    sudo curl https://storage.googleapis.com/gcm-ext/gcm_install.sh | bash -x
    When you run the preceding command, the installation script performs the following operations:

  3. Creates the following directories:

    • /usr/local/lib/gcm, which will contain the agent binary.
    • /etc/gcm.d, which will contain the queries you want the agent to run.
    • /var/spool/gcm, which contains the metrics that have recently been gathered by the agent. The agent eventually sends these metrics to Cloud Monitoring. After metrics are sent, they are deleted from /var/spool/gcm.
  4. Downloads the latest release of the agent and saves it to /usr/local/lib/gcm/.

  5. Creates a symlink to the binary in /usr/local/bin/gcm.

  6. Creates a basic configuration template, /etc/default/gcm.

  7. Creates a daemon configuration file, /etc/systemd/system/gcm.service.

  8. Reloads the systemd service definitions.

  9. Enables the monitoring agent as a daemon.

Updating the agent

Checking for a new version of the agent

  1. Check the version of your agent:

    gcm --version
  2. Check if an update is available:

    curl https://storage.googleapis.com/gcm-ext/LATEST

Downloading and updating the agent

To update the agent when a new version is available, follow these steps:

  1. Stop the agent process:

    sudo systemctl stop gcm
  2. Update the agent with the following command. The -C and -U options skip the creation of the default configuration file and Linux systemd service definition for the agent:

    sudo curl https://storage.googleapis.com/gcm-ext/gcm_install.sh | bash -s -- -C -U

  3. Start the agent:

    sudo systemctl start gcm

Configuring the agent

After running the install script, configure the VM instances that the agent will monitor and, optionally, the queries that the agent will perform on your database.

Defining the configuration file

When you ran the installation script, it created a configuration template file at the following location:

/etc/default/gcm

Using the configuration file, you can configure the agent to query SAP HANA on one or more VM instances. For each VM instance you want to monitor:

  1. Add a VM instance definition in the configuration file under instances. For each instance, define the name, host, port, user, and password.
  2. To enable the agent to collect metrics from that VM instance, add the VM instance name under enabled_instances.

Define the attributes in the configuration by using the following format:

---
config:
  timestamps_in_output: no
  debug_messages: yes
  skip_default_queries: yes
  queries_directory: /etc/gcm.d
  spool_directory: /var/spool/gcm
  enabled_instances:
    - [INSTANCE_NAME]
    - [INSTANCE_NAME_2]
  project_id: [PROJECT_ID]
  instances:
    - name: [INSTANCE_NAME]
      host: [INSTANCE_IP]
      port: [PORT_NUMBER]
      user: [DB_USERNAME]
      password: [YOUR_PASSWORD]
      type: [INSTANCE_TYPE]
    - name: [INSTANCE_NAME_2]
      host: [INSTANCE_IP_2]
      ...

The following attributes are required in the configuration file:

  • [PROJECT_ID]: The ID of the Google Cloud project that contains the VM instances to be monitored. Specify only one project ID.
  • [INSTANCE_NAME]: The name of the Compute Engine VM instance you want to monitor.
  • [INSTANCE_IP]: The internal or external IP of the Compute Engine VM instance you want to monitor. We recommend using internal IPs if possible, because they require less setup than external IPs and are private and secure by default.
  • [DB_USERNAME]: The SAP HANA database user that you want to use.
  • [PORT_NUMBER]: The port number of your SAP HANA database. Usually, the port number is 39015 for SAP HANA Express or 30015 for SAP HANA Platform Edition.
  • [YOUR_PASSWORD]: The user password for your database user.
  • [INSTANCE_TYPE]: Enter sap_hana for this field.

Optionally, you can configure the agent using the following flags. These parameters flags must be set before the config item:

  • timestamps_in_output (boolean). If you're running the agent as a daemon under systemd, set this to no, as systemd provides timestamps. Default value is no.
  • debug_messages (boolean): If set, show debug messages. Default value is yes.
  • skip_default_queries (boolean): Skips the default set of queries. Does not affect user-defined queries. Default value is no.
  • queries_directory (string): The directory in which to look for user-defined queries. Default value is /etc/gcm.d.
  • spool_directory (string): The directory in which to collect spooled metrics. Default value is /var/spool/gcm.

Defining custom queries

By default, the agent gathers a default set of metrics from your SAP HANA database. For descriptions of these metrics, as well as the queries that generate the metrics, see Default metrics and queries in V1.0.

You can add additional queries by creating one or more custom YAML files in the following directory:

/etc/gcm.d

The following is an example query file:

- root: by_component
  type: sap_hana
  description: |
      Amount of memory (in MiB) used by service components
  query: |
    SELECT
             HOST AS "host",
             COMPONENT AS "component",
             SUM(USED_MEMORY_SIZE)/1024/1024 AS "mem_used_mb"
        FROM M_SERVICE_COMPONENT_MEMORY
    GROUP BY HOST, COMPONENT;
  columns:
    - type: LABEL
      value_type: STRING
      name: host
    - type: LABEL
      value_type: STRING
      name: component
    - type: GAUGE
      description: Amount of memory (in MiB) used by the service component
      value_type: DOUBLE
      name: mem_used_mb

Each YAML file comprises one or more items that represent the results of a given SQL query into your SAP HANA database's metrics. Each item has the following attributes:

  • root: A descriptive namespace for a given group of metrics.
  • query: The SQL query.
  • columns: The data type, value type, and column name for each field in the query.

    • type: Describes how the data is reported to Monitoring. You can set type to LABEL, which indicates that the type is a column head, or toGAUGE, one of the metric types defined in the Monitoring API's MetricKind enum. Currently, GAUGE is the only metric type supported by the agent.
    • value_type: The value type of the metric. The value of this parameter can be any of the value types defined in the Monitoring API's ValueType enum.
    • name: The name of the column.

For a full list of the system views that SAP HANA makes available for querying, see the SAP HANA SQL and System Views Reference.

Query results are limited to 1,000 records. Use SQL aggregate functions like SUM, AVG, COUNT, MIN and MAX to limit the number of records returned.

Performing basic operations

This section describes how to perform basic operations with the SAP HANA monitoring agent. For a full list of configurable options, run gcm --help.

Managing the monitoring agent daemon

When you ran the installation script, the installer created a systemd unit file for the agent, allowing you to manage the agent using standard systemctl commands. The following commands start, stop, and poll the status of the agent, respectively:

sudo systemctl start gcm
sudo systemctl stop gcm
sudo systemctl status gcm

To read the logs generated by systemctl, query the contents of the systemd journal by using the following command:

sudo journalctl -u gcm

To see the last few lines that were logged, you can add the -f flag, which functions like a pipe to tail -f:

sudo journalctl -u gcm -f

Suppressing default queries

By default, the agent runs its default queries in addition to any custom queries you've defined. You can disable this default set of queries by setting the skip_default_queries flag to yes in your config file, or by setting the --no-defaults flag when running the tool manually. For more information, see Defining the configuration file.

Manually collecting and sending metrics

To verify that the agent works as expected, you can manually run commands to run your queries, collect the resulting metrics, and then send them to Cloud Monitoring. To run your queries and collect the resulting metrics exactly once, run the following command:

sudo gcm gather

This command also sends your collected metrics to Cloud Monitoring.

Viewing your metrics in Cloud Monitoring

To display the metrics collected by Cloud Monitoring in your own charts and dashboards:

  1. In the Cloud Console, go to the Monitoring page.

    Go to Monitoring

  2. Select Dashboards > Create Dashboard.

  3. Click Add Chart.

  4. In the Resource Type menu, select Custom Metrics.

  5. In the Metric menu, select by_component/mem_used_mb. Leave the other fields with their default values. You see the chart data in the Preview section of the panel.

  6. Click Save.

You now have a simple dashboard displaying live metrics from your SAP HANA VM instance or instances.

Troubleshooting

Default queries are failing

Make sure that your SAP HANA database user has access to the following SAP HANA system views:

  • M_CS_ALL_COLUMNS
  • M_CS_TABLES
  • M_EXPENSIVE_STATEMENTS
  • M_HOST_RESOURCE_UTILIZATION
  • M_SERVICE_COMPONENT_MEMORY
  • M_SERVICE_MEMORY

Metrics are not being sent to Cloud Monitoring

Make sure that your Google Cloud service account has permission to write metrics to Cloud Monitoring. For details, see Setting the required IAM roles.

Support

For issues with Google Cloud infrastructure or services, contact Customer Care. You can find contact information on the Support Overview page in the Google Cloud Console. If Customer Care determines that a problem resides in your SAP systems, you are referred to SAP Support.

For SAP product-related issues, log your support request with SAP support. SAP evaluates the support ticket and, if it appears to be a Google Cloud infrastructure issue, transfers the ticket to the Google Cloud component BC-OP-LNX-GOOGLE or BC-OP-NT-GOOGLE.

Support requirements

Before you can receive support for SAP systems and the Google Cloud infrastructure and services that they use, you must meet the minimum support plan requirements.

For more information about the minimum support requirements for SAP on Google Cloud, see:

Default metrics and queries in V1.0

This section describes the metrics that version 1.0 of the monitoring agent for SAP HANA collects by default and the queries that the agent uses to collect the metrics.

Total memory utilization by services

Metric Description
instance/mem_used_mb Amount of memory from the memory pool (in MiB) currently in use by all services
instance/resident_mem_used_mb Amount of memory (in MiB) used in total by all the services

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     SUM(TOTAL_MEMORY_USED_SIZE)/1024/1024 AS "mem_used_mb",
     SUM(PHYSICAL_MEMORY_SIZE)/1024/1024 AS "resident_mem_used_mb"
FROM M_SERVICE_MEMORY;

Total amount of memory used by all column tables

Metric in Cloud Monitoring Description
instance/table_mem_used_mb Amount of memory (in MiB) used in total (sum of memory size in the main, delta, and history parts) by all column-tables

The Google monitoring agent for SAP HANA uses the following query to collect the above metric from SAP HANA:

SELECT
     SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024 AS "table_mem_used_mb"
FROM M_CS_TABLES;

Resource utilization by host

Metric Description
by_server/mem_available_percent Amount of memory available to processes (in MiB)
by_server/mem_total_mb Total amount of memory (in MiB) on the server
by_server/mem_available_mb Free physical memory (in MiB) on the host
by_server/mem_used_mb Used physical memory (in MiB) on the host
by_server/swap_avail_mb Free swap memory (in MiB) on the host
by_server/swap_used_mb Used swap memory (in MiB) on the host
by_server/instance_mem_used_mb Amount from the memory pool (in MiB) that is used by instance processes
by_server/peak_instance_mem_used_mb Peak amount from the memory pool (in MiB) that has been used by instance processes
by_server/instance_mem_pool_size_mb Size of the memory pool (in MiB) for all instance processes
by_server/instance_code_size_mb Code size (in MiB), including shared libraries of instance processes
by_server/instance_shared_size_mb Shared memory size of instance processes
by_server/cpu_user_time_msec CPU time spent (in ms) in user mode
by_server/cpu_sys_time_msec CPU time spent (in ms) in kernel mode
by_server/cpu_wait_io_time_msec CPU time spent (in ms) in wait IO
by_server/cpu_idle_time_msec CPU idle time (in ms)

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     HOST AS "host",
     FREE_PHYSICAL_MEMORY/(FREE_PHYSICAL_MEMORY + USED_PHYSICAL_MEMORY)*100 AS "mem_available_percent",
     (FREE_PHYSICAL_MEMORY + USED_PHYSICAL_MEMORY)/1024/1024 AS "mem_total_mb",
     FREE_PHYSICAL_MEMORY/1024/1024 AS "mem_available_mb",
     USED_PHYSICAL_MEMORY/1024/1024 AS "mem_used_mb",
     FREE_SWAP_SPACE/1024/1024 AS "swap_avail_mb",
     USED_SWAP_SPACE/1024/1024 AS "swap_used_mb",
     INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024 AS "instance_mem_used_mb",
     INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE/1024/1024 AS "peak_instance_mem_used_mb",
     INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE/1024/1024 AS "instance_mem_pool_size_mb",
     INSTANCE_CODE_SIZE/1024/1024 AS "instance_code_size_mb",
     INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE/1024/1024 AS "instance_shared_size_mb",
     TOTAL_CPU_USER_TIME AS "cpu_user_time_msec",
     TOTAL_CPU_SYSTEM_TIME AS "cpu_sys_time_msec",
     TOTAL_CPU_WIO_TIME AS "cpu_wait_io_time_msec",
     TOTAL_CPU_IDLE_TIME AS "cpu_idle_time_msec"
FROM M_HOST_RESOURCE_UTILIZATION;

Amount of memory used by service components

Metric Description
by_component/mem_used_mb Amount of memory from the memory pool (in MiB) currently in use

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     HOST AS "host",
     COMPONENT AS "component",
     SUM(USED_MEMORY_SIZE)/1024/1024 AS "mem_used_mb"
FROM M_SERVICE_COMPONENT_MEMORY
GROUP BY HOST, COMPONENT;

Memory utilization by services

Metric Description
by_service/mem_used_mb Virtual memory size (in MiB)
by_service/virtual_mem_used_mb Physical memory size (in MiB)
by_service/resident_mem_used_mb Code size (in MiB), including shared libraries
by_service/code_size_mb Stack size (in MiB)
by_service/stack_size_mb Heap part of memory pool (in MiB)
by_service/heap_mem_allocated_mb Amount of pool heap memory in use (in MiB)
by_service/shared_mem_allocated_mb Shared memory part of memory pool (in MiB)
by_service/shared_mem_used Amount of pool shared memory in use (in MiB)
by_service/compactors_allocated_mb Part of the memory pool (in MiB) that can potentially be freed on memory shortage
by_service/compactors_freeable_mb Memory which can actually be freed (in MiB) on memory shortage
by_service/max_mem_pool_size_mb Maximum configured memory pool size (in MiB)
by_service/effective_max_mem_pool_size_mb Effective maximum memory pool size (in MiB)

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     HOST AS "host",
     PORT AS "port",
     SERVICE_NAME AS "service",
     TOTAL_MEMORY_USED_SIZE/1024/1024 AS "mem_used_mb",
     LOGICAL_MEMORY_SIZE/1024/1024 AS "virtual_mem_used_mb",
     PHYSICAL_MEMORY_SIZE/1024/1024 AS "resident_mem_used_mb",
     CODE_SIZE/1024/1024 AS "code_size_mb",
     STACK_SIZE/1024/1024 AS "stack_size_mb",
     HEAP_MEMORY_ALLOCATED_SIZE/1024/1024 AS "heap_mem_allocated_mb",
     HEAP_MEMORY_USED_SIZE/1024/1024 AS "heap_mem_used_mb",
     SHARED_MEMORY_ALLOCATED_SIZE/1024/1024 AS "shared_mem_allocated_mb",
     SHARED_MEMORY_USED_SIZE/1024/1024 AS "shared_mem_used",
     COMPACTORS_ALLOCATED_SIZE/1024/1024 AS "compactors_allocated_mb",
     COMPACTORS_FREEABLE_SIZE/1024/1024 AS "compactors_freeable_mb",
     ALLOCATION_LIMIT/1024/1024 AS "max_mem_pool_size_mb",
     EFFECTIVE_ALLOCATION_LIMIT/1024/1024 AS "effective_max_mem_pool_size_mb"
FROM M_SERVICE_MEMORY;

Runtime data for column tables by schema

Metric Description
by_schema/mem_total_mb Total memory used (in MiB) by main, delta, and history parts
by_schema/mem_main_mb Current memory consumption (in MiB) in main
by_schema/mem_delta_mb Current memory consumption (in MiB) in delta
by_schema/mem_hist_main_mb Current memory consumption (in MiB) in history-main
by_schema/mem_hist_detla_mb Current memory consumption (in MiB) in history-delta
by_schema/est_max_mem_total_mb Estimated maximum memory consumption (in MiB)
by_schema/records Record count
by_schema/records_main Number of records in the main part of the tables in the schema
by_schema/records_delta Number of records in the delta part of the tables in the schema
by_schema/records_hist_main Number of records in the history-main part of the tables in the schema
by_schema/records_hist_delta Number of records in the history-delta part of the tables in the schema
by_schema/last_compressed_record_count Number of entries in main during the last optimize compression run
by_schema/reads Number of read accesses
by_schema/writes Number of write accesses
by_schema/merges Number of delta merges

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     HOST AS "host",
     PORT AS "port",
     SCHEMA_NAME AS "schema",
     SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024 AS "mem_total_mb",
     SUM(MEMORY_SIZE_IN_MAIN)/1024/1024 AS "mem_main_mb",
     SUM(MEMORY_SIZE_IN_DELTA)/1024/1024 AS "mem_delta_mb",
     SUM(MEMORY_SIZE_IN_HISTORY_MAIN)/1024/1024 AS "mem_hist_main_mb",
     SUM(MEMORY_SIZE_IN_HISTORY_DELTA)/1024/1024 AS "mem_hist_delta_mb",
     SUM(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL)/1024/1024 AS "est_max_mem_total_mb",
     SUM(RECORD_COUNT) AS "records",
     SUM(RAW_RECORD_COUNT_IN_MAIN) AS "records_main",
     SUM(RAW_RECORD_COUNT_IN_DELTA) AS "records_delta",
     SUM(RAW_RECORD_COUNT_IN_HISTORY_MAIN) AS "records_hist_main",
     SUM(RAW_RECORD_COUNT_IN_HISTORY_DELTA) AS "records_hist_delta",
     SUM(LAST_COMPRESSED_RECORD_COUNT) AS "last_compressed_record_count",
     SUM(READ_COUNT) AS "reads",
     SUM(WRITE_COUNT) AS "writes",
     SUM(MERGE_COUNT) AS "merges"
FROM M_CS_TABLES
GROUP BY HOST, PORT, SCHEMA_NAME;

Statements with a duration longer than instance-configured threshold

Metric Description
expensive_statements/duration_msec Time elapsed (in ms) during execution of the statement
expensive_statements/records Number of records
expensive_statements/lock_waits Accumulated lock wait count
expensive_statements/lock_duration_msec Accumulated lock wait duration (in ms)
expensive_statements/cpu_time_msec CPU time (in ms) consumed to compute the statement

The Google monitoring agent for SAP HANA uses the following query to collect the above metrics from SAP HANA:

SELECT
     HOST AS "host",
     PORT AS "port",
     CONNECTION_ID AS "connection_id",
     TRANSACTION_ID AS "transaction_id",
     STATEMENT_HASH AS "statement_hash",
     DB_USER AS "db_user",
     SCHEMA_NAME AS "schema",
     APP_USER AS "app_user",
     ERROR_CODE AS "error_code",
     DURATION_MICROSEC/1000 AS "duration_msec",
     RECORDS AS "records",
     LOCK_WAIT_COUNT AS "lock_waits",
     LOCK_WAIT_DURATION/1000 AS "lock_duration_msec",
     CPU_TIME/1000 AS "cpu_time_msec"
FROM M_EXPENSIVE_STATEMENTS;

What's next