JinDaGe - pgtuner_mcp MCP Details

article

README

🚀 PostgreSQL Performance Tuning MCP

PostgreSQL Performance Tuning MCP 是一个基于 Model Context Protocol (MCP) 的服务器，借助人工智能为 PostgreSQL 提供性能调优能力。它能够识别慢查询、推荐最优索引、分析执行计划，还可利用 HypoPG 进行假设索引测试，有效提升数据库性能。

🚀 快速开始

本项目提供了多种安装方式，你可以根据自身需求选择合适的方法进行安装。安装完成后，还需要进行相应的配置，包括设置环境变量、配置 MCP 客户端等，以确保项目能够正常运行。

✨ 主要特性

查询分析

从 pg_stat_statements 中检索慢查询，并提供详细统计信息。
使用 EXPLAIN 和 EXPLAIN ANALYZE 分析查询执行计划。
通过自动计划分析识别性能瓶颈。
监控活动查询并检测长时间运行的事务。

索引调优

基于查询工作负载分析，提供由人工智能驱动的索引建议。
使用 HypoPG 扩展进行假设索引测试（不占用磁盘空间）。
查找未使用和重复的索引，以便进行清理。
在创建索引之前估算索引大小。
在实施之前，使用建议的索引测试查询计划。

数据库健康检查

通过多项检查进行全面的健康评分。
监控连接利用率。
分析缓存命中率（缓冲区和索引）。
检测锁争用情况。
监控真空操作健康状况和事务 ID 回绕情况。
监控复制延迟。
分析后台写入器和检查点的 I/O 情况。

真空操作监控

实时跟踪长时间运行的 VACUUM 和 VACUUM FULL 操作。
监控自动真空操作的进度和性能。
识别需要进行真空操作的表。
查看最近的真空操作活动历史记录。
分析自动真空配置的有效性。

I/O 性能分析

分析表和索引的磁盘读写模式。
识别 I/O 瓶颈和热点表。
监控缓冲区缓存命中率。
跟踪临时文件使用情况，以发现 work_mem 问题。
分析检查点和后台写入器的 I/O 情况。
支持 PostgreSQL 16+ 增强的 pg_stat_io 指标。

配置分析

按类别审查 PostgreSQL 设置。
获取有关内存、检查点、WAL、自动真空和连接设置的建议。
识别欠佳的配置。

MCP 提示与资源

为常见调优工作流提供预定义的提示模板。
提供表统计信息、索引信息和健康检查的动态资源。
提供全面的文档资源。

📦 安装指南

标准安装（适用于像 Claude Desktop 这样的 MCP 客户端）

pip install pgtuner_mcp

或者使用 uv：

uv pip install pgtuner_mcp

手动安装

git clone https://github.com/isdaniel/pgtuner_mcp.git
cd pgtuner_mcp
pip install -e .

💻 使用示例

基础用法

# 查找并分析慢查询
# Get top 10 slowest queries
slow_queries = await get_slow_queries(limit=10, order_by="total_time")

# Analyze a specific query's execution plan
analysis = await analyze_query(
    query="SELECT * FROM orders WHERE user_id = 123",
    analyze=True,
    buffers=True
)

高级用法

# 获取索引建议
# Analyze workload and get recommendations
recommendations = await get_index_recommendations(
    max_recommendations=5,
    min_improvement_percent=20,
    include_hypothetical_testing=True
)

# Recommendations include CREATE INDEX statements
for rec in recommendations["recommendations"]:
    print(rec["create_statement"])

📚 详细文档

配置

环境变量

| 变量 | 描述 | 是否必需 | |------|------|----------| | DATABASE_URI | PostgreSQL 连接字符串 | 是 | | PGTUNER_EXCLUDE_USERIDS | 以逗号分隔的要排除在监控之外的用户 ID（OIDs）列表 | 否 |

连接字符串格式：postgresql://user:password@host:port/database

最小用户权限

为了运行此 MCP 服务器，PostgreSQL 用户需要特定的权限来查询系统目录和扩展。以下是不同功能集所需的最小权限：

-- 完整设置脚本
-- 1. Create the monitoring user
CREATE USER pgtuner_monitor WITH PASSWORD 'secure_password';

-- 2. Grant connection and schema access
GRANT CONNECT ON DATABASE your_database TO pgtuner_monitor;
GRANT USAGE ON SCHEMA public TO pgtuner_monitor;

-- 3. Grant read access to user tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgtuner_monitor;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pgtuner_monitor;

-- 4. Grant system statistics access
GRANT pg_read_all_stats TO pgtuner_monitor;  -- PostgreSQL 10+

-- Grant access to pg_stat_statements views explicitly
GRANT SELECT ON pg_stat_statements TO pgtuner_monitor;
GRANT SELECT ON pg_stat_statements_info TO pgtuner_monitor;

-- 5. Install and grant access to extensions (as superuser)
-- pg_stat_statements (required)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- pgstattuple (for bloat detection)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
GRANT pg_stat_scan_tables TO pgtuner_monitor;  -- PostgreSQL 14+
-- OR grant individual functions:
-- GRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO pgtuner_monitor;
-- GRANT EXECUTE ON FUNCTION pgstattuple_approx(regclass) TO pgtuner_monitor;
-- GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO pgtuner_monitor;

-- hypopg (for hypothetical index testing)
CREATE EXTENSION IF NOT EXISTS hypopg;
GRANT SELECT ON hypopg_list_indexes TO pgtuner_monitor;
GRANT SELECT ON hypopg_hidden_indexes TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_create_index(text) TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_drop_index(oid) TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_reset() TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_hide_index(oid) TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_unhide_index(oid) TO pgtuner_monitor;
GRANT EXECUTE ON FUNCTION hypopg_relation_size(oid) TO pgtuner_monitor;

-- 6. Verify permissions
SET ROLE pgtuner_monitor;
SELECT * FROM pg_stat_statements LIMIT 1;
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();
SELECT * FROM pgstattuple('pg_class') LIMIT 1;
SELECT * FROM hypopg_list_indexes();
RESET ROLE;

排除特定用户的监控

你可以将特定的 PostgreSQL 用户排除在查询分析和监控结果之外。这对于过滤以下用户很有用：

监控或复制用户
系统账户
内部应用程序服务账户

设置 PGTUNER_EXCLUDE_USERIDS 环境变量，以逗号分隔的用户 OID 列表：

# Exclude user IDs 16384, 16385, and 16386
export PGTUNER_EXCLUDE_USERIDS="16384,16385,16386"

要查找特定 PostgreSQL 用户的 OID：

SELECT usesysid, usename FROM pg_user WHERE usename = 'monitoring_user';

配置后，以下查询将被过滤：

pg_stat_activity 查询（在 usesysid 列上过滤）
pg_stat_statements 查询（在 userid 列上过滤）

这会影响 get_slow_queries、get_active_queries、analyze_wait_events、check_database_health 和 get_index_recommendations 等工具。

MCP 客户端配置

添加到你的 cline_mcp_settings.json 或 Claude Desktop 配置中：

{
  "mcpServers": {
    "pgtuner_mcp": {
      "command": "python",
      "args": ["-m", "pgtuner_mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

或者使用可流式传输的 HTTP 模式：

{
  "mcpServers": {
    "pgtuner_mcp": {
      "type": "http",
      "url": "http://localhost:8080/mcp"
    }
  }
}

服务器模式

1. 标准 MCP 模式（默认）

# Default mode (stdio)
python -m pgtuner_mcp

# Explicitly specify stdio mode
python -m pgtuner_mcp --mode stdio

2. HTTP SSE 模式（适用于旧版 Web 应用程序）

SSE（服务器发送事件）模式为 MCP 通信提供基于 Web 的传输方式。这对于需要基于 HTTP 通信的 Web 应用程序和客户端很有用。

# Start SSE server on default host/port (0.0.0.0:8080)
python -m pgtuner_mcp --mode sse

# Specify custom host and port
python -m pgtuner_mcp --mode sse --host localhost --port 3000

# Enable debug mode
python -m pgtuner_mcp --mode sse --debug

SSE 端点： | 端点 | 方法 | 描述 | |------|------|------| | /sse | GET | SSE 连接端点 - 客户端在此连接以接收服务器事件 | | /messages | POST | 向服务器发送消息/请求 |

MCP 客户端 SSE 配置：对于支持 SSE 传输的 MCP 客户端（如 Claude Desktop 或自定义客户端）：

{
  "mcpServers": {
    "pgtuner_mcp": {
      "type": "sse",
      "url": "http://localhost:8080/sse"
    }
  }
}

3. 可流式传输的 HTTP 模式（推荐使用现代 MCP 协议）

可流式传输的 HTTP 模式通过单个 /mcp 端点实现现代 MCP 可流式传输的 HTTP 协议。它支持有状态（基于会话）和无状态模式。

# Start Streamable HTTP server in stateful mode (default)
python -m pgtuner_mcp --mode streamable-http

# Start in stateless mode (fresh transport per request)
python -m pgtuner_mcp --mode streamable-http --stateless

# Specify custom host and port
python -m pgtuner_mcp --mode streamable-http --host localhost --port 8080

# Enable debug mode
python -m pgtuner_mcp --mode streamable-http --debug

有状态与无状态：

有状态（默认）：使用 mcp-session-id 标头在请求之间保持会话状态。适用于长时间运行的交互。
无状态：为每个请求创建全新的传输，不进行会话跟踪。适用于无服务器部署或简单的请求/响应模式。

端点：http://{host}:{port}/mcp

可用工具

⚠️ 重要提示

所有工具仅专注于用户/应用程序表和索引。系统目录表（pg_catalog、information_schema、pg_toast）会自动排除在所有分析之外。

性能分析工具

| 工具 | 描述 | |------|------| | get_slow_queries | 从 pg_stat_statements 中检索慢查询，并提供详细统计信息（总时间、平均时间、调用次数、缓存命中率）。排除系统目录查询。 | | analyze_query | 使用 EXPLAIN ANALYZE 分析查询的执行计划，包括自动问题检测 | | get_table_stats | 获取详细的表统计信息，包括大小、行数、死元组和访问模式 | | analyze_disk_io_patterns | 分析磁盘 I/O 读写模式，识别热点表、缓冲区缓存效率和 I/O 瓶颈。支持按分析类型过滤（所有、缓冲池、表、索引、临时文件、检查点）。 |

索引调优工具

| 工具 | 描述 | |------|------| | get_index_recommendations | 基于查询工作负载分析，提供由人工智能驱动的索引建议 | | explain_with_indexes | 使用假设索引运行 EXPLAIN，以在不创建实际索引的情况下测试改进 | | manage_hypothetical_indexes | 创建、列出、删除或重置 HypoPG 假设索引。支持隐藏/取消隐藏现有索引。 | | find_unused_indexes | 查找可以安全删除的未使用和重复的索引 |

数据库健康检查工具

| 工具 | 描述 | |------|------| | check_database_health | 进行全面的健康检查并评分（连接、缓存、锁、复制、回绕、磁盘、检查点） | | get_active_queries | 监控活动查询，查找长时间运行的事务和阻塞的查询。默认排除系统进程。 | | analyze_wait_events | 分析等待事件，以识别 I/O、锁或 CPU 瓶颈。专注于客户端后端进程。 | | review_settings | 按类别审查 PostgreSQL 设置，并提供优化建议 |

膨胀检测工具（pgstattuple）

| 工具 | 描述 | |------|------| | analyze_table_bloat | 使用 pgstattuple 扩展分析表膨胀情况。显示死元组计数、空闲空间和浪费空间百分比。 | | analyze_index_bloat | 使用 pgstatindex 分析 B 树索引膨胀情况。显示叶子密度、碎片化和空/删除页面。也支持 GIN 和哈希索引。 | | get_bloat_summary | 获取数据库膨胀的全面概述，包括顶级膨胀表/索引、可回收的总空间和优先维护操作。 |

真空操作监控工具

| 工具 | 描述 | |------|------| | monitor_vacuum_progress | 跟踪手动 VACUUM、VACUUM FULL 和自动真空操作。监控进度百分比、收集的死元组、索引真空轮次和估计剩余时间。包括自动真空配置审查和需要维护的表。 |

工具参数

| 工具 | 参数 | |------|------| | get_slow_queries | limit：返回的最大查询数（默认：10）；min_calls：最小调用次数过滤（默认：1）；min_mean_time_ms：最小平均执行时间（毫秒）过滤；order_by：按 mean_time、calls 或 rows 排序 | | analyze_query | query（必需）：要分析的 SQL 查询；analyze：使用 EXPLAIN ANALYZE 执行查询（默认：true）；buffers：包括缓冲区统计信息（默认：true）；format：输出格式 - json、text、yaml、xml | | get_index_recommendations | workload_queries：可选的特定查询列表进行分析；max_recommendations：最大建议数（默认：10）；min_improvement_percent：最小改进阈值（默认：10%）；include_hypothetical_testing：使用 HypoPG 进行测试（默认：true）；target_tables：关注特定表 | | check_database_health | include_recommendations：包括可操作的建议（默认：true）；verbose：包括详细统计信息（默认：false） | | analyze_table_bloat | table_name：要分析的特定表的名称（可选）；schema_name：模式名称（默认：public）；use_approx：在大型表上使用 pgstattuple_approx 进行更快的分析（默认：false）；min_table_size_gb：在模式范围扫描中包含的最小表大小（GB）（默认：5）；include_toast：包括 TOAST 表分析（默认：false） | | analyze_index_bloat | index_name：要分析的特定索引的名称（可选）；table_name：分析此表上的所有索引（可选）；schema_name：模式名称（默认：public）；min_index_size_gb：包含的最小索引大小（GB）（默认：5）；min_bloat_percent：仅显示膨胀率高于此百分比的索引（默认：20） | | get_bloat_summary | schema_name：要分析的模式（默认：public）；top_n：显示的顶级膨胀对象数量（默认：10）；min_size_gb：包含的最小对象大小（GB）（默认：5） | | monitor_vacuum_progress | action：要执行的操作 - progress（监控活动的真空操作）、needs_vacuum（查找需要真空的表）、autovacuum_status（审查自动真空配置）或 recent_activity（查看最近的真空历史记录）；schema_name：要分析的模式（默认：public，与 needs_vacuum 操作一起使用）；top_n：返回的结果数量（默认：20） | | analyze_disk_io_patterns | analysis_type：I/O 分析类型 - all（全面）、buffer_pool（缓存命中率）、tables（表 I/O 模式）、indexes（索引 I/O 模式）、temp_files（临时文件使用情况）或 checkpoints（检查点 I/O 统计信息）；schema_name：要分析的模式（默认：public）；top_n：显示的顶级 I/O 密集型对象数量（默认：20）；min_size_gb：包含的最小对象大小（GB）（默认：1） |

MCP 提示

服务器包含预定义的提示模板，用于指导调优会话： | 提示 | 描述 | |------|------| | diagnose_slow_queries | 系统的慢查询调查工作流 | | index_optimization | 全面的索引分析和清理 | | health_check | 全面的数据库健康评估 | | query_tuning | 优化特定的 SQL 查询 | | performance_baseline | 生成用于比较的基线报告 |

MCP 资源

静态资源

pgtuner://docs/tools - 完整的工具文档
pgtuner://docs/workflows - 常见调优工作流指南
pgtuner://docs/prompts - 提示模板文档

动态资源模板

pgtuner://table/{schema}/{table_name}/stats - 表统计信息
pgtuner://table/{schema}/{table_name}/indexes - 表索引信息
pgtuner://query/{query_hash}/stats - 查询性能统计信息
pgtuner://settings/{category} - PostgreSQL 设置（内存、检查点、WAL、自动真空、连接、所有）
pgtuner://health/{check_type} - 健康检查（连接、缓存、锁、复制、膨胀、所有）

PostgreSQL 扩展设置

HypoPG 扩展

HypoPG 允许在不实际创建索引的情况下测试索引。这对于以下情况非常有用：

测试查询规划器是否会使用建议的索引
比较不同索引策略的执行计划
在提交之前估算存储需求

-- Create the extension
CREATE EXTENSION IF NOT EXISTS hypopg;

-- Verify installation
SELECT * FROM hypopg_list_indexes();

pg_stat_statements 扩展

pg_stat_statements 扩展是查询性能分析所必需的。它跟踪服务器执行的所有 SQL 语句的规划和执行统计信息。

步骤 1：在 postgresql.conf 中启用扩展

在 postgresql.conf 文件中添加以下内容：

# Required: Load pg_stat_statements module
shared_preload_libraries = 'pg_stat_statements'

# Required: Enable query identifier computation
compute_query_id = on

# Maximum number of statements tracked (default: 5000)
pg_stat_statements.max = 10000

# Track all statements including nested ones (default: top)
# Options: top, all, none
pg_stat_statements.track = top

# Track utility commands like CREATE, ALTER, DROP (default: on)
pg_stat_statements.track_utility = on

⚠️ 重要提示

修改 shared_preload_libraries 后，需要重启 PostgreSQL 服务器。

步骤 2：在数据库中创建扩展

-- Connect to your database and create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify installation
SELECT * FROM pg_stat_statements LIMIT 1;

pgstattuple 扩展

pgstattuple 扩展是膨胀检测工具（analyze_table_bloat、analyze_index_bloat、get_bloat_summary）所必需的。它提供获取表和索引元组级统计信息的函数。

-- Create the extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Verify installation
SELECT * FROM pgstattuple('pg_class') LIMIT 1;

性能影响考虑

| 设置 | 开销 | 建议 | |------|------|------| | pg_stat_statements | 低（~1 - 2%） | 始终启用 | | track_io_timing | 低 - 中（~2 - 5%） | 在生产环境中启用，先进行测试 | | track_functions = all | 低 | 对于函数密集型工作负载启用 | | pg_stat_statements.track_planning | 中 | 仅在调查规划问题时启用 | | log_min_duration_statement | 低 | 建议用于识别慢查询 |

💡 使用建议

在启用 track_io_timing 之前，使用 pg_test_timing 在你的特定系统上测量计时开销。

Docker

docker pull  dog830228/pgtuner_mcp

# Streamable HTTP mode (recommended for web applications)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode streamable-http

# Streamable HTTP stateless mode (for serverless)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode streamable-http --stateless

# SSE mode (legacy web applications)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode sse

# stdio mode (for MCP clients like Claude Desktop)
docker run -i \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode stdio

要求

Python：3.10+
PostgreSQL：12+（推荐：14+）
扩展：
- pg_stat_statements（查询分析必需）
- hypopg（可选，用于假设索引测试）

依赖项

核心依赖项

mcp[cli]>=1.12.0 - 模型上下文协议 SDK
psycopg[binary,pool]>=3.1.0 - 带有连接池的 PostgreSQL 适配器
pglast>=7.10 - PostgreSQL 查询解析器

可选依赖项（用于 HTTP 模式）

starlette>=0.27.0 - ASGI 框架
uvicorn>=0.23.0 - ASGI 服务器

🔧 技术细节

本项目基于 Model Context Protocol (MCP) 构建，使用 Python 语言开发，依赖于多个 PostgreSQL 扩展和 Python 库。通过对 PostgreSQL 系统目录和扩展的查询，实现了对数据库性能的监控和调优。利用 HypoPG 扩展进行假设索引测试，避免了实际创建索引带来的磁盘空间占用和性能开销。同时，通过对各种性能指标的分析和统计，为用户提供了全面的数据库健康检查和调优建议。

📄 许可证

文档中未提及相关内容，故跳过该章节。

贡献

欢迎贡献代码！请随时提交拉取请求。