oap-server/server-starter/src/main/resources/otel-rules/postgresql/postgresql-service.yaml

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This will parse a textual representation of a duration. The formats
# accepted are based on the ISO-8601 duration format {@code PnDTnHnMn.nS}
# with days considered to be exactly 24 hours.
# <p>
# Examples:
# <pre>
#    "PT20.345S" -- parses as "20.345 seconds"
#    "PT15M"     -- parses as "15 minutes" (where a minute is 60 seconds)
#    "PT10H"     -- parses as "10 hours" (where an hour is 3600 seconds)
#    "P2D"       -- parses as "2 days" (where a day is 24 hours or 86400 seconds)
#    "P2DT3H4M"  -- parses as "2 days, 3 hours and 4 minutes"
#    "P-6H3M"    -- parses as "-6 hours and +3 minutes"
#    "-P6H3M"    -- parses as "-6 hours and -3 minutes"
#    "-P-6H+3M"  -- parses as "+6 hours and -3 minutes"
# </pre>
filter: "{ tags -> tags.job_name == 'postgresql-monitoring' }" # The OpenTelemetry job name
expSuffix:  tag({tags -> tags.host_name = 'postgresql::' + tags.host_name}).service(['host_name'] , Layer.POSTGRESQL)
metricPrefix: meter_pg
metricsRules:
  # dashboards
  ## rows
  - name: fetched_rows_rate
    exp: pg_stat_database_tup_fetched.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: deleted_rows_rate
    exp: pg_stat_database_tup_deleted.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: inserted_rows_rate
    exp: pg_stat_database_tup_inserted.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: updated_rows_rate
    exp: pg_stat_database_tup_updated.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: returned_rows_rate
    exp: pg_stat_database_tup_returned.sum(['service_instance_id','host_name']).rate('PT1M')
  ## locks
  - name: locks_count
    exp: pg_locks_count.sum(['service_instance_id','host_name'])

  ## sessions
  - name: active_sessions
    exp: pg_stat_activity_count.tagEqual('state','active').sum(['service_instance_id' , 'host_name'])
  - name: idle_sessions
    exp: pg_stat_activity_count.tagMatch('state','idle|idle in transaction|idle in transaction (aborted)').sum(['service_instance_id' , 'host_name'])

  ## transactions
  - name: committed_transactions_rate
    exp: pg_stat_database_xact_commit.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: rolled_back_transactions_rate
    exp: pg_stat_database_xact_rollback.sum(['service_instance_id','host_name']).rate('PT1M')

  ## cache and temporary file
  - name: cache_hit_rate
    exp: (pg_stat_database_blks_hit*100 / (pg_stat_database_blks_read + pg_stat_database_blks_hit)).avg(['service_instance_id','host_name'])
  - name: temporary_files_rate
    exp: pg_stat_database_temp_bytes.sum(['service_instance_id','host_name']).rate('PT1M')

  ## checkpoint
  - name: checkpoint_write_time_rate
    exp: pg_stat_bgwriter_checkpoint_write_time_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: checkpoint_sync_time_rate
    exp: pg_stat_bgwriter_checkpoint_sync_time_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: checkpoint_req_rate
    exp: pg_stat_bgwriter_checkpoints_req_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: checkpoints_timed_rate
    exp: pg_stat_bgwriter_checkpoints_timed_total.rate('PT1M').sum(['service_instance_id','host_name'])

  ## conflicts and deadlocks
  - name: conflicts_rate
    exp: pg_stat_database_conflicts.sum(['service_instance_id','host_name']).rate('PT1M')
  - name: deadlocks_rate
    exp: pg_stat_database_deadlocks.sum(['service_instance_id','host_name']).rate('PT1M')

  ## buffers
  - name: buffers_checkpoint
    exp: pg_stat_bgwriter_buffers_checkpoint_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: buffers_clean
    exp: pg_stat_bgwriter_buffers_clean_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: buffers_backend_fsync
    exp: pg_stat_bgwriter_buffers_backend_fsync_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: buffers_alloc
    exp: pg_stat_bgwriter_buffers_alloc_total.rate('PT1M').sum(['service_instance_id','host_name'])
  - name: buffers_backend
    exp: pg_stat_bgwriter_buffers_backend_total.rate('PT1M').sum(['service_instance_id','host_name'])