database_observability

Setting Up Database Observability with Grafana Cloud

NOTE: while the components database_observability.mysql and database_observability.postgres are marked as experimental, it is recommended to refer to the "next" version of docs for a complete reference documentation.

MySQL

Setting up the MySQL database

1. Your MySQL DB should be above version 8.

2. Create a dedicated DB user and grant permissions.

CREATE USER 'db-o11y'@'%' IDENTIFIED by '<password>';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'db-o11y'@'%';
GRANT SELECT, SHOW VIEW ON *.* TO 'db-o11y'@'%'; /* see note */

Please note: Regarding GRANT SELECT, SHOW VIEW ON *.* TO 'db-o11y'@'%', it is possible to restrict permissions, if necessary. Instead, grant the db-o11y user privileges access only to the objects (schemas) for which you want information. For example, to restrict permissions only to a schema named payments:

CREATE USER 'db-o11y'@'%' IDENTIFIED by '<password>';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'db-o11y'@'%';
GRANT SELECT ON performance_schema.* TO 'db-o11y'@'%';   /* required */
GRANT SELECT, SHOW VIEW ON payments.* TO 'db-o11y'@'%';  /* limit grant to the `payments` schema */

1. Verify that the user has been properly created.

SHOW GRANTS FOR 'db-o11y'@'%';

+-------------------------------------------------------------------+
| Grants for db-o11y@%                                              |
+-------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO `db-o11y`@`%`         |
| GRANT SELECT, SHOW VIEW ON *.* TO `db-o11y`@`%`                   |
+-------------------------------------------------------------------+

1. Enable Performance Schema. To enable it explicitly, start the server with the performance_schema variable set to an appropriate value. Verify that Performance Schema has been enabled:

SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

1. Increase max_digest_length and performance_schema_max_digest_length to 4096. Verify that the changes have been applied:

SHOW VARIABLES LIKE 'max_digest_length';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| max_digest_length | 4096  |
+-------------------+-------+

and

SHOW VARIABLES LIKE 'performance_schema_max_digest_length';

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| performance_schema_max_digest_length | 4096  |
+--------------------------------------+-------+

1. [OPTIONAL] Increase performance_schema_max_sql_text_length to 4096 if you want to collect the actual, unredacted sql text from queries samples (this requires setting disable_query_redaction to true, see later). Verify that the changes have been applied:

SHOW VARIABLES LIKE 'performance_schema_max_sql_text_length';

+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| performance_schema_max_sql_text_length | 4096  |
+----------------------------------------+-------+

1. [OPTIONAL] Enable the events_statements_cpu consumer if you want to capture CPU activity and time on query samples. Verify the current setting with a sql query:

SELECT * FROM performance_schema.setup_consumers WHERE NAME = 'events_statements_cpu';

Use this statement to enable the consumer if it's disabled:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_cpu';

Note that the events_statements_cpu consumer will be disabled again when the database is restarted. If you prefer Alloy to verify and enable the consumer on your behalf then extend the grants of the db-o11y user:

GRANT UPDATE ON performance_schema.setup_consumers TO 'db-o11y'@'%';

and additionally enable these options:

database_observability.mysql "mysql_<your_DB_name>" {
  enable_collectors = ["query_samples"]

  // Global option to allow writing to performance_schema tables
  allow_update_performance_schema_settings = true

  // Option to allow the `query_samples` collector to
  // enable the 'events_statements_cpu' consumer
  query_samples {
    auto_enable_setup_consumers = true
  }
}

1. [OPTIONAL] Enable the events_waits_current and events_waits_history consumers if you want to collect wait events for each query sample. Verify the current settings with a sql query:

SELECT * FROM performance_schema.setup_consumers WHERE NAME IN ('events_waits_current', 'events_waits_history');

Use this statement to enable the consumers if they are disabled:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME IN ('events_waits_current', 'events_waits_history');

As noted in the step above, these consumers will be disabled again when the database is restarted. If you prefer Alloy to verify and enable the consumers on your behalf then follow the instructions from the step above.

Running and configuring Alloy

1. You need to run the latest Alloy version from the main branch. The latest tags are available here on Docker Hub (for example, grafana/alloy-dev:v1.10.0-devel-630bcbb or more recent) . Additionally, the --stability.level=experimental CLI flag is necessary for running the database_observability component.

2. Add the following configuration block to Alloy.

• Replace <your_DB_name>
• Create a local.file with your DB secrets. The content of the file should be the Data Source Name string, for example "user:password@(hostname:port)/".

1. Copy this block for each DB you'd like to monitor.

local.file "mysql_secret_<your_DB_name>" {
  filename  = "/var/lib/alloy/mysql_secret_<your_DB_name>"
  is_secret = true
}

prometheus.exporter.mysql "integrations_mysqld_exporter_<your_DB_name>" {
  data_source_name  = local.file.mysql_secret_<your_DB_name>.content
  enable_collectors = ["perf_schema.eventsstatements", "perf_schema.eventswaits"]
  perf_schema.eventsstatements {
    text_limit = 2048
  }
}

database_observability.mysql "mysql_<your_DB_name>" {
  data_source_name  = local.file.mysql_secret_<your_DB_name>.content
  forward_to        = [loki.relabel.database_observability_mysql_<your_DB_name>.receiver]

  // OPTIONAL: enable collecting samples of queries with their execution metrics. The sql text will be redacted to hide sensitive params.
  enable_collectors = ["query_samples"]

  // OPTIONAL: if `query_samples` collector is enabled, you can use
  // the following setting to disable sql text redaction (by default
  // query samples are redacted).
  query_samples {
    disable_query_redaction = true
  }

  // OPTIONAL: provide additional information specific to the Cloud Provider
  // that hosts the database to enable certain infrastructure observability features.
  cloud_provider {
    aws {
      arn = "your-rds-db-arn"
    }
  }
}

loki.relabel "database_observability_mysql_<your_DB_name>" {
  forward_to = [loki.write.logs_service.receiver]

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "discovery.relabel"
  rule {
    target_label = "instance"
    replacement  = "<instance_label>"
  }
  rule {
    target_label = "<custom_label_1>"
    replacement  = "<custom_value_1>"
  }
}

discovery.relabel "database_observability_mysql_<your_DB_name>" {
  targets = concat(prometheus.exporter.mysql.integrations_mysqld_exporter_<your_DB_name>.targets, database_observability.mysql.mysql_<your_DB_name>.targets)

  rule {
    target_label = "job"
    replacement  = "integrations/db-o11y"
  }

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "loki.relabel"
  rule {
    target_label = "instance"
    replacement  = "<instance_label>"
  }
  rule {
    target_label = "<custom_label_1>"
    replacement  = "<custom_value_1>"
  }
}

prometheus.scrape "database_observability_mysql_<your_DB_name>" {
  targets    = discovery.relabel.database_observability_mysql_<your_DB_name>.output
  job_name   = "integrations/db-o11y"
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

1. Add Prometheus remote_write and Loki write config, if not present already. From the Grafana Cloud home, click on your stack and view URLs under Prometheus and Loki details where API tokens may also be generated.

prometheus.remote_write "metrics_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_METRICS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_METRICS_ID")
    }
  }
}

loki.write "logs_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_LOGS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_LOGS_ID")
    }
  }
}

Configuring Alloy with the k8s-monitoring helm chart

When using the k8s-monitoring helm chart you might need to extend your values.yaml with:

alloy:
  image:
    repository: "grafana/alloy-dev"
    tag: <alloy-version> // e.g. "v1.10.0-devel-630bcbb"

  alloy:
    stabilityLevel: experimental

extraConfig: |
  // Add the config blocks for Database Observability
  prometheus.exporter.mysql "integrations_mysqld_exporter_<your_DB_name>" {
    ...
  }
  ...
  database_observability.mysql "mysql_<your_DB_name>" {
    ...
  }

Example Alloy configuration

This is a complete example of Alloy Database Observability configuration using two different databases:

prometheus.remote_write "metrics_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_METRICS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_METRICS_ID")
    }
  }
}

loki.write "logs_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_LOGS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_LOGS_ID")
    }
  }
}

local.file "mysql_secret_example_db_1" {
  filename  = "/var/lib/alloy/mysql_secret_example_db_1"
  is_secret = true
}

prometheus.exporter.mysql "integrations_mysqld_exporter_example_db_1" {
  data_source_name  = local.file.mysql_secret_example_db_1.content
  enable_collectors = ["perf_schema.eventsstatements", "perf_schema.eventswaits"]
  perf_schema.eventsstatements {
    text_limit = 2048
  }
}

database_observability.mysql "mysql_example_db_1" {
  data_source_name  = local.file.mysql_secret_example_db_1.content
  forward_to        = [loki.relabel.database_observability_mysql_example_db_1.receiver]
  enable_collectors = ["query_samples"]
}

loki.relabel "database_observability_mysql_example_db_1" {
  forward_to = [loki.write.logs_service.receiver]
}

discovery.relabel "database_observability_mysql_example_db_1" {
  targets = concat(prometheus.exporter.mysql.integrations_mysqld_exporter_example_db_1.targets, database_observability.mysql.mysql_example_db_1.targets)

  rule {
    target_label = "job"
    replacement  = "integrations/db-o11y"
  }
}

prometheus.scrape "database_observability_mysql_example_db_1" {
  targets    = discovery.relabel.database_observability_mysql_example_db_1.output
  job_name   = "integrations/db-o11y"
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

local.file "mysql_secret_example_db_2" {
  filename  = "/var/lib/alloy/mysql_secret_example_db_2"
  is_secret = true
}

prometheus.exporter.mysql "integrations_mysqld_exporter_example_db_2" {
  data_source_name  = local.file.mysql_secret_example_db_2.content
  enable_collectors = ["perf_schema.eventsstatements", "perf_schema.eventswaits"]
  perf_schema.eventsstatements {
    text_limit = 2048
  }
}

database_observability.mysql "mysql_example_db_2" {
  data_source_name  = local.file.mysql_secret_example_db_2.content
  forward_to        = [loki.relabel.database_observability_mysql_example_db_2.receiver]
  enable_collectors = ["query_samples"]
}

loki.relabel "database_observability_mysql_example_db_2" {
  forward_to = [loki.write.logs_service.receiver]
}

discovery.relabel "database_observability_mysql_example_db_2" {
  targets = concat(prometheus.exporter.mysql.integrations_mysqld_exporter_example_db_2.targets, database_observability.mysql.mysql_example_db_2.targets)

  rule {
    target_label = "job"
    replacement  = "integrations/db-o11y"
  }
}

prometheus.scrape "database_observability_mysql_example_db_2" {
  targets    = discovery.relabel.database_observability_mysql_example_db_2.targets
  job_name   = "integrations/db-o11y"
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

PostgreSQL

Setting up the Postgres database

1. Your Postgres DB should be at least version 16.

2. Add the pg_stat_statements module to shared_preload_libraries in postgresql.conf. This requires a restart of the Postgres DB.

3. Create the pg_stat_statements extension in every database you want to monitor.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1. Verify that the extension is enabled.

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

1. Increase track_activity_query_size to 4096. Verify that the change has been applied:

show track_activity_query_size;

 track_activity_query_size
---------------------------
 4kB

1. Create a dedicated DB user and grant permissions.

CREATE USER "db-o11y" WITH PASSWORD '<password>';
GRANT pg_monitor TO "db-o11y";
GRANT pg_read_all_stats TO "db-o11y";

1. Verify that the user has been properly created.

-- run with the `db-o11y` user
SELECT * FROM pg_stat_statements LIMIT 1;

Running and configuring Alloy

1. You need to run the latest Alloy version from the main branch. The latest tags are available here on Docker Hub (for example, grafana/alloy-dev:v1.10.0-devel-630bcbb or more recent) . Additionally, the --stability.level=experimental CLI flag is necessary for running the database_observability component.

2. Add the following configuration block to Alloy for each Postgres DB you'd like to monitor.

• Replace <your_DB_name>
• Create a local.file with your DB secrets. The content of the file should be the Data Source Name string, for example "postgresql://user:password@(hostname:port)/dbname?sslmode=require".

1. Copy this block for each DB you'd like to monitor.

local.file "postgres_secret_<your_DB_name>" {
  filename  = "/var/lib/alloy/postgres_secret_<your_DB_name>"
  is_secret = true
}

prometheus.exporter.postgres "integrations_postgres_exporter_<your_DB_name>" {
  data_source_name  = local.file.postgres_secret_<your_DB_name>.content
  enabled_collectors = ["stat_statements"]

  autodiscovery {
    enabled = true

    // If running on AWS RDS, exclude the `rdsadmin` database
    database_denylist = ["rdsadmin"]
  }
}

database_observability.postgres "postgres_<your_DB_name>" {
  data_source_name  = local.file.postgres_secret_<your_DB_name>.content
  forward_to        = [loki.relabel.database_observability_postgres_<your_DB_name>.receiver]

  // OPTIONAL: enable collecting samples of queries with their execution metrics. The sql text will be redacted to hide sensitive params.
  enable_collectors = ["query_samples", "query_details"]

  // OPTIONAL: if `query_samples` collector is enabled, you can use
  // the following setting to disable sql text redaction (by default
  // query samples are redacted).
  query_samples {
    disable_query_redaction = true
  }

  // OPTIONAL: provide additional information specific to the Cloud Provider
  // that hosts the database to enable certain infrastructure observability features.
  cloud_provider {
    aws {
      arn = "your-rds-db-arn"
    }
  }
}

loki.relabel "database_observability_postgres_<your_DB_name>" {
  forward_to = [loki.write.logs_service.receiver]

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "discovery.relabel"
  rule {
    target_label = "instance"
    replacement  = "<instance_label>"
  }
  rule {
    target_label = "<custom_label_1>"
    replacement  = "<custom_value_1>"
  }
}

discovery.relabel "database_observability_postgres_<your_DB_name>" {
  targets = concat(prometheus.exporter.postgres.integrations_postgres_exporter_<your_DB_name>.targets, database_observability.postgres.postgres_<your_DB_name>.targets)

  rule {
    target_label = "job"
    replacement  = "integrations/db-o11y"
  }

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "loki.relabel"
  rule {
    target_label = "instance"
    replacement  = "<instance_label>"
  }
  rule {
    target_label = "<custom_label_1>"
    replacement  = "<custom_value_1>"
  }
}

prometheus.scrape "database_observability_postgres_<your_DB_name>" {
  targets    = discovery.relabel.database_observability_postgres_<your_DB_name>.output
  job_name   = "integrations/db-o11y"
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

1. Add Prometheus remote_write and Loki write config, if not present already. From the Grafana Cloud home, click on your stack and view URLs under Prometheus and Loki details where API tokens may also be generated.

prometheus.remote_write "metrics_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_METRICS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_METRICS_ID")
    }
  }
}

loki.write "logs_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_LOGS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_LOGS_ID")
    }
  }
}