Database data sources

How Sondar collects metrics and logs from the databases it integrates with: MySQL, PostgreSQL, Elasticsearch, Memcached, MongoDB, OceanBase, Redis, TiDB and ClickHouse.

The shape of a database integration

Each database ships as an installable app. Installing it registers:

collect types — the selectable sources you configure a collection task with (mysql_tel, redis_tel, …). The _tel suffix names a metric reader; the others collect logs.
repositories — the indexes the collected data is written to (app_mysql_tel, app_mysql_log, …). One app writes several.
sourcetypes — the parse templates applied to the data.
dashboards — prebuilt monitoring views, installed with the app.

The agent does the collection. A collect type is just the configuration you hand to an agent collection task: a reader section that says where the data comes from, optional transformers that shape it, and a sender section that says where it goes. Each template below shows the reader and the sender keys; the sender block is the same for every type in an app and is elided after the first example.

The nine databases

app	metric collect type	log collect types	metric repo	log repo
`mysql`	`mysql_tel`	`mysql_log_error`, `mysql_log_gen`, `mysql_slow`	`app_mysql_tel`	`app_mysql_log`
`postgresql`	`postgresql_tel`	`postgresql_log`	`app_postgresql_tel`	`app_postgresql_log`
`elasticsearch`	`elasticsearch_tel`	`elasticsearch_log`, `elasticsearch_json_log`, `elasticsearch_slow_log`, `elasticsearch_gc_log`	`app_elasticsearch_tel`	`app_elasticsearch_log`
`memcached`	`memcached_tel`	—	`app_memcached_tel`	—
`mongodb`	`MongoDB_tel`	`Mongodb_log`	`app_mongodb_tel`	`app_mongodb_log`
`oceanbase`	`oceanbase_tel`	`oceanbase_server_log`, `oceanbase_trace_log`	`app_oceanbase_prom_tel`	`app_oceanbase_server_log`, `app_oceanbase_trace_log`
`redis`	`redis_tel`, `redis_sentinel_tel`	`redis_log`, `redis_slowlog`	`app_redis_tel`, `app_redis_sentinel_tel`	`redis_server_log`, `redis_slowlog`
`tidb`	`tidb_prom_tel`	—	`app_tidb_prom_tel`	—
`clickhouse`	`clickhouse_prom_tel`	—	`app_clickhouse_prom_tel`	—

Three notes that fall out of the table:

Redis is the odd one. Its log collect types send to repos named redis_server_log and redis_slowlog, where every other app names its repos app_<db>_<kind>. Search for the repo, not the pattern.
Three databases collect metrics over Prometheus, not over their native protocol: OceanBase, TiDB and ClickHouse scrape the Prometheus endpoint the database exposes. The _tel readers on the other six speak the database’s own protocol.
Elasticsearch logs split four ways: plain-text, JSON (7.0+, ECSJsonLayout), slow index/search and GC. One log repo, four sourcetypes.

MySQL

mysql_tel reads metrics over the MySQL protocol. The required variable is the server address:

readers:
  - mysql_tel:
      ## Collect interval
      interval: 30s
      ## Server address to connect to
      servers:  ["${Server}"]

      metric_version: 2

      ## gather thread state counts from INFORMATION_SCHEMA.PROCESSLIST
      # gather_process_list:  false

      ## gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS
      # gather_user_statistics:  false

      ## gather metrics from INFORMATION_SCHEMA.INNODB_METRICS
      # gather_innodb_metrics:  false

      ## gather metrics from all channels from SHOW SLAVE STATUS command output
      # gather_all_slave_channels:  false

      ## use SHOW ALL SLAVES STATUS command output for MariaDB
      # mariadb_dialect:  false

      ## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_TABLE
      # gather_table_io_waits:  false

      ## gather metrics from PERFORMANCE_SCHEMA.EVENT_WAITS
      # gather_event_waits:  false

      ## gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
      # gather_perf_events_statements:  false

The metric family is the classic Telegraf mysql input: global statuses and variables, slave status, binary log size and count, process list, user statistics, auto-increment columns, InnoDB metrics, table and index I/O waits, table lock waits, event waits, file event statistics, event statements and table schema. Every metric is tagged with the server it was gathered from; user, process and statement metrics add user, schema/table/column labels as appropriate.

The three log collect types each collect a single MySQL log file with a fileset reader:

mysql_log_error — the error log. Regex extraction produces tid, level, db_err_code and module fields.
mysql_log_gen — the general query log.
mysql_slow — the slow query log, matched with head_pattern: \#\s*Time and parsed into db_user, db_host, query_time, lock_time, event_time and sql.

MySQL writes its slow and general logs only when enabled in my.cnf (general_log = 1 / slow_query_log = 1, or the runtime SET GLOBAL equivalents — MySQL settings, not Sondar ones).

PostgreSQL

postgresql_tel reads from the pg_stat_database and pg_stat_bgwriter views, stable on PostgreSQL 8.1+. The required variable is the address:

readers:
  - postgresql_tel:
      ## Collect interval
      interval: 30s
      ## Address given as a URL pattern
      ##   postgres://[pqgotest[:password]]@localhost[/dbname]?sslmode: [disable|verify-ca|verify-full]
      ## or a simple string:
      ##   host: localhost user: pqgotest password: ... sslmode: ... dbname: app_production
      address: ${Url}

      ## Collection blocklist: databases to skip.
      # ignored_databases:  ["postgres", "template0", "template1"]

      ## Collection allowlist: databases to collect.
      ## Note: do not combine with the 'ignored_databases' setting.
      # databases:  ["app_production", "testing"]

      ## Whether to use prepared statements when talking to the database.
      ## If connections go through PgBouncer with pool_mode transaction mode, set this false
      prepared_statements:  true

postgresql_log collects the server log. Both types share the app_postgresql prefix on their repo and sourcetype names.

Elasticsearch

elasticsearch_tel reads node stats and cluster health. It needs a username and password in addition to the address, so its collect type has three variables:

readers:
  - elasticsearch_tel:
      ## Collect interval
      interval: 30s
      ## One or more Elasticsearch service addresses.
      ## The username and password can be embedded in the connection URL, as below
      ## servers:  ["http://user:pass@localhost:9200"]
      servers: ["${Server}"]

      ## Required when Security is on: HTTP basic auth username and password
      username:  ${UserName}
      password:  ${PassWord}

      ## When local is true, each service reads only its local node's statistics
      local: false

      ## Set cluster_health to true for cluster health information
      cluster_health: true

      ## For cluster statistics, set cluster_stats to true
      cluster_stats: false

      ## node_stats selects the subset of node metrics to collect. Options include:
      ## "indices", "os", "process", "jvm", "thread_pool", "fs", "transport", "http", "breaker"

The four log types are:

elasticsearch_log — plain-text server logs.
elasticsearch_json_log — JSON logs from Elasticsearch 7.0+, where the log4j2.properties layout is set to ECSJsonLayout.
elasticsearch_slow_log — slow query and slow index logs.
elasticsearch_gc_log — JVM GC logs.

All four send to app_elasticsearch_log and differ only in sourcetype.

Memcached

Metrics only, no logs. memcached_tel reads stats over the Memcached protocol:

readers:
  - memcached_tel:
      ## Collect interval
      interval: 30s

      # Services to fetch metrics from, addressed as hostname or ip + port
      # For example localhost, 10.0.0.1:11211
      servers:  ["${Server}"]
      # Memcached sockets to fetch statistics from
      # unix_sockets:  ["/var/run/memcached.sock"]

      ## Whether to send metrics one record at a time
      # single_sending: false

MongoDB

MongoDB_tel reads metrics from MongoDB 2.6+:

readers:
  - mongodb_tel:
      ## Collect interval
      interval: 30s

      ## URL format:
      ##   "mongodb://" [user ":" pass "@"] host [ ":" port]
      ## To connect to a cluster, the URL must include "?connect=direct" so the
      ## connection goes to the named node instead of being routed to the primary.
      servers: [ "${Server}" ]

      ## If true, collect cluster status.
      ## Note: querying jumbo chunks triggers a COLLSCAN, which can hurt performance
      # gather_cluster_status:  true

      ## If true, collect statistics for every database
      # gather_perdb_stats:  false

      ## If true, collect statistics for every collection
      # gather_col_stats:  false

      ## If true, collect per-collection operation usage statistics
      # gather_top_stat:  false

Mongodb_log collects the server log (its JSON entries parse into s, c, id, msg and friends). Repos are app_mongodb_tel and app_mongodb_log.

OceanBase

Metrics arrive over Prometheus rather than a native reader. OceanBase’s oceanbase_tel is three prometheus_tel readers, one per metric group, each with a distinct endpoint variable and a name_prefix that keeps the groups apart:

readers:
  ## OceanBase node environment metrics
  - prometheus_tel:
      interval: 60s
      urls: [ "${Node_Url}" ]
      name_prefix: ob_node_ # node metrics

  ## OceanBase basic metrics
  - prometheus_tel:
      interval: 60s
      urls: [ "${Basic_Url}" ]
      name_prefix: ob_basic_

  ## OceanBase advanced metrics
  - prometheus_tel:
      interval: 60s
      urls: [ "${Extra_Url}" ]
      name_prefix: ob_extra_

oceanbase_server_log and oceanbase_trace_log collect the two log streams. Note the metric repo is app_oceanbase_prom_tel, carrying the prom of its collection mechanism.

Redis

Three metric sources and two log sources:

redis_tel — Redis metrics (INFO), one or more servers.
redis_sentinel_tel — Sentinel monitoring metrics.
redis_log — the server log, collected with a fileset reader and parsed into pid, role and rlevel; the role letter is mapped to a readable replication_role (M → master, S → slave, X → sentinel).
redis_slowlog — slow queries, polled with the redis_fbt reader via the Redis SLOWLOG interface rather than read from a file:

readers:
  - redis_fbt:
      ## Redis services to connect to
      hosts: ["${Server}"]

      ## Password for the Redis connection
      password: ${PassWord}

      ## How often to poll Redis's slow query log
      ## 1s keeps scanning near-real-time without scanning too often
      ## Default 10s.
      # scan_frequency: 10s

      ## Timeout waiting for a response, default 1s
      # timeout: 1s

      ## Max concurrent connections. Default 10.
      # maxconn: 10

Redis is also where the repo naming differs: redis_log sends to redis_server_log and redis_slowlog to redis_slowlog — no app_ prefix.

TiDB and ClickHouse

Both collect metrics only, by scraping the database’s Prometheus endpoint — nothing native. TiDB’s tidb_prom_tel is four prometheus_tel readers scraping the TiDB, PD, TiKV and TiFlash endpoints, each tagged with its role and all written to app_tidb_prom_tel with a global cluster_name: tidb tag. ClickHouse’s clickhouse_prom_tel scrapes the ClickHouse metrics endpoint (default port 9363) with name_override: clickhouse:

readers:
  - prometheus_tel:
      ## Collect interval
      interval: 60s
      ## URLs to fetch metrics from; the default port is 9363
      url: [ "${Url}" ]
      ## Metric name prefix
      name_override: clickhouse
      metric_version: 2

Both rely on the database exposing /metrics (ClickHouse) or the individual component endpoints (TiDB); verify the endpoint returns Prometheus text before creating the collection task.