PostgreSQL慢查询未在New Relic显示，请求配置指导

已在New Relic的PostgreSQL集成配置中开启慢查询收集，但重启后仪表板仍无数据，可按以下步骤排查修正：

一、修正New Relic集成配置语法错误 #

你的配置中labels字段存在YAML语法问题，键值对冒号后必须添加空格，修正后配置如下：

name: Copying Postgresql Configuration

copy:
  dest: /etc/newrelic-infra/integrations.d/postgresql-config.yml
  content: |
    integrations:
      - name: nri-postgresql
        config:
          username: {{ newrelic_username }}
          password: {{ newrelic_password }}
          hostname: {{ postgresql_hostname }}
          port: 5432
          ssl: false
          trust_server_certificate: false
          timeout: 10
          databases: ["postgres"]
          collect_db_lock_metrics: false
          collect_bloat_metrics: true
          collect_slow_queries: true
          query_monitoring:
            enabled: true
            response_time_threshold: 100
            count_threshold: 58
          custom_metrics_config: /etc/newrelic-infra/integrations.d/postgresql-custom-metrics.yml
          labels:
            env: {{ env }}
            role: postgr

二、启用PostgreSQL必要扩展 #

New Relic的PostgreSQL集成依赖pg_stat_statements扩展收集查询性能数据，需在PostgreSQL端完成以下配置：

1. 修改PostgreSQL配置文件（通常为postgresql.conf），添加或更新参数：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048

2. 重启PostgreSQL服务
3. 在目标数据库（如postgres）中创建扩展：

CREATE EXTENSION pg_stat_statements;

三、验证数据库用户权限 #

确保New Relic使用的数据库用户拥有访问性能视图的权限：

GRANT SELECT ON pg_stat_statements TO {{ newrelic_username }};
GRANT SELECT ON pg_stat_activity TO {{ newrelic_username }};

四、调整慢查询监控阈值（可选） #

当前配置的response_time_threshold: 100（毫秒）和count_threshold:58可能不符合实际场景：

• 若阈值过低，可能无足够慢查询触发采集；若过高，会过滤大部分查询
• 可先降低count_threshold至1测试数据采集，后续按需调整：

query_monitoring:
  enabled: true
  response_time_threshold: 500  # 调整为500毫秒（0.5秒）
  count_threshold: 1

五、重启New Relic基础设施代理 #

修改配置后，重启代理使设置生效：

sudo systemctl restart newrelic-infra

六、验证数据采集 #

等待数分钟后，在New Relic中验证：

• 导航至实体→选择PostgreSQL实例→查看慢查询标签页
• 或用NRQL查询验证：

FROM PostgreSqlSlowQuery SELECT * WHERE databaseName = 'postgres'

附：其他相关配置与查询 #

MongoDB性能指标NRQL查询（中文翻译版）

-- 索引命中率计算
FROM Metric 
SELECT latest(mongodb_metrics_queryExecutor_scannedObjects) / 
       latest(mongodb_metrics_queryExecutor_scanned) * 100 
AS '索引命中率%' 
WHERE mongodb_cluster_name IN ({{select_instance}})

-- 复制集网络与操作统计
FROM Metric 
SELECT latest(mongodb_repl_network_bytes) AS '字节数', 
       latest(mongodb_repl_apply_batches_num_ops) AS '已应用操作数' 
WHERE mongodb_cluster_name IN ({{select_instance}}) 
TIMESERIES

-- 操作速率统计
FROM Metric 
SELECT rate(mongodb_opcounters_insert, 1 second) AS '插入/秒',
       rate(mongodb_opcounters_update, 1 second) AS '更新/秒',
       rate(mongodb_opcounters_delete, 1 second) AS '删除/秒'
WHERE mongodb_cluster_name IN ({{select_instance}})
TIMESERIES

-- CPU使用率统计
FROM Metric 
SELECT average(cpuPercent) 
WHERE hostname IN ({{select_instance}}) 
TIMESERIES

JMX指标采集配置（中文翻译版）

version: 1
collections:
  - name: 垃圾回收
    object_name: java.lang:type=GarbageCollector,name=*
    attributes:
      - CollectionCount
      - CollectionTime

  - name: 内存
    object_name: java.lang:type=Memory
    attributes:
      - HeapMemoryUsage
      - NonHeapMemoryUsage

  - name: 线程
    object_name: java.lang:type=Threading
    attributes:
      - ThreadCount
      - PeakThreadCount

  - name: Cassandra读取延迟
    object_name: org.apache.cassandra.metrics:type=ClientRequest,scope=Read,name=Latency
    attributes:
      - Count
      - OneMinuteRate

  - name: Cassandra写入延迟
    object_name: org.apache.cassandra.metrics:type=ClientRequest,scope=Write,name=Latency
    attributes:
      - Count
      - OneMinuteRate

  - name: Cassandra存储负载
    object_name: org.apache.cassandra.metrics:type=Storage,name=Load
    attributes:
      - Count

内容的提问来源于stack exchange，提问作者shaik awez