OB桌面版+MCP+大模型打造海量数据库机器资源助手

概述

在数字化浪潮下,零售SaaS面临机遇与挑战,海量数据7x24h写入数据库,机器资源管理成为了“高楼大厦”业务系统的重要“地基”,用OB桌面版+MCP+大模型打造海量数据库机器资源助手,通过AI聊天式简单办法运维管理上千台数据库机器资源。

方案

核心组件

1、数据库系统:OB 桌面版,简单安装即用,个人开发者,小项目利器

2、MCP Server+MCP Host:中间层服务

MCP 提供了一种标准化的方式(类似于typc/USB接口,连接众多设备),使 LLM 可以与外部数据库、API 和工具进行高效交互,从而打破“数据孤岛“ 核心特点 协议标准化:统一工具调用格式(请求/响应/错误处理) 生态兼容性:一次开发即可对接所有兼容MCP的模型 动态扩展:新增工具无需修改模型代码,即插即用 核心价值,解决三大问题 数据孤岛 → 打通本地/云端数据源 重复开发 → 工具开发者只需适配MCP协议 生态割裂 → 形成统一工具市场

3、Cursor大模型接口:Claude 3.7等大模型的连接接口

4、API网关:处理认证与请求路由

关键功能

自然语言转SQL

系统将用户的自然语言查询转换为精确的SQL语句:

1、大模型理解查询意图

2、参考数据库schema

3、生成优化的SQL

4、执行并返回结果

数据分析增强

结合大模型的推理能力,对查询结果进行深入分析:

1、数据趋势识别

2、异常检测

3、关联分析

4、可视化建议

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│                 │    │                 │    │                 │
│  Cursor/Claude/
|   阿里百炼        │◄───┤  MCP Server     │◄───┤  数据库系统      │ ◄─── 定时任务SaaS项目多环境资源数据写入
│  大模型接口       │    │  中间层          │    │  (OB 桌面版)  
│                 │    │                 │    │                 │
└─────────────────┘    └─────────────────┘    └─────────────────┘
       ▲                       ▲                       ▲
       │                       │                       │
       └───────────────────────┼───────────────────────┘
                               │
                       ┌───────────────┐
                       │               │
                       │  用户/开发者    │
                       │               │
                       └───────────────┘

MCP打破“数据孤岛”,连接能力杠杠!

OB桌面版

登录OB桌面版sys租户可详细查看1个observer,2个unit,2个租户信息,可见单个observer至少要6G才能最小化部署

obclient -h127.0.0.1 -P2881 -p -uroot@sys -A -Doceanbase
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3221660120
Server version: 5.7.25 OceanBase_CE 4.3.5.1 (r101000042025031818-b6d5706eb3d2c5f501c7fa646ddbf32f3dc87069) (Built Mar 18 2025 18:12:30)


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> select  svr_ip,  svr_port,  zone,  round((cpu_capacity_max - cpu_assigned_max), 2) 'cpu_free_num',  cpu_capacity_max 'cpu_total_num',  round((mem_capacity - mem_assigned) / 1024 / 1024 / 1024, 2) 'mem_free_GB',
  round(memory_limit / 1024 / 1024 / 1024, 2) 'mem_total_GB',  round(DATA_DISK_IN_USE / 1024 / 1024 / 1024, 2) as data_used_GB,  round(DATA_DISK_CAPACITY / 1024 / 1024 / 1024, 2) as data_total_GB,  round(LOG_DISK_CAPACITY / 1024 / 1024 / 1024, 2) as log_total_GB,  round(LOG_DISK_IN_USE / 1024 / 1024 / 1024, 2) as log_used_GB,round(LOG_DISK_ASSIGNED/1024/1024/1024,2) as  LOG_DISK_ASSIGNED_GB from gv$ob_servers;
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+------------------------+
| svr_ip    | svr_port | zone  | cpu_free_num | cpu_total_num | mem_free_GB | mem_total_GB | data_used_GB | data_total_GB | log_total_GB | log_used_GB |  LOG_DISK_ASSIGNED_GB  |
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+------------------------+
| 127.0.0.1 |     2882 | zone1 |         0.00 |             8 |        0.00 |         6.00 |         0.29 |          2.00 |        14.00 |        0.88 |                  14.00 |
+-----------+----------+-------+--------------+---------------+-------------+--------------+--------------+---------------+--------------+-------------+------------------------+
1 row in set (1.00 sec)


mysql> select  unit_config_id,name,  max_cpu,  round(memory_size / 1024 / 1024 / 1024, 2) 'memory_size_GB', round(log_disk_size / 1024 / 1024 / 1024, 2) as log_disk_size from   __all_unit_config;
+----------------+-----------------+---------+----------------+---------------+
| unit_config_id | name            | max_cpu | memory_size_GB | log_disk_size |
+----------------+-----------------+---------+----------------+---------------+
|              1 | sys_unit_config |       2 |           1.00 |          2.00 |
|           1001 | test_unit       |       6 |           4.00 |         12.00 |
+----------------+-----------------+---------+----------------+---------------+
2 rows in set (0.24 sec)


mysql> SELECT t1.name resource_pool_name,         t2.`name` unit_config_name,         t2.max_cpu,         t2.min_cpu,         t2.memory_size/1024/1024/1024 memory_size,                                       t3.unit_id,                                       t3.zone,                                       concat(t3.svr_ip,':',t3.`svr_port`) observer,                                       t4.tenant_id,
      t4.tenant_name  FROM __all_resource_pool t1  JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id)  JOIN __all_unit t3 ON (t1.`resource_pool_id` = t3.`resource_pool_id`)  LEFT JOIN __all_tenant t4 ON (t1.tenant_id=t4.tenant_id)  ORDER BY t1.`resource_pool_id`,           t2.`unit_config_id`,           t3.unit_id ;
+--------------------+------------------+---------+---------+----------------+---------+-------+----------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | memory_size    | unit_id | zone  | observer       | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+----------------+---------+-------+----------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       2 |       2 | 1.000000000000 |       1 | zone1 | 127.0.0.1:2882 |         1 | sys         |
| test_pool          | test_unit        |       6 |       6 | 4.000000000000 |    1001 | zone1 | 127.0.0.1:2882 |      1002 | test        |
+--------------------+------------------+---------+---------+----------------+---------+-------+----------------+-----------+-------------+
2 rows in set (0.15 sec)


mysql>

创建指定库mydb,和资源水位表(包括重要资源信息:ip、用途、磁盘+内存资源水位,可用磁盘,可用内存信息)

CREATE TABLE `global_dashboard_resource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `host` varchar(100) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `discription` varchar(200) DEFAULT NULL,
  `owner_id` varchar(40) DEFAULT NULL,
  `ava_memory` int(11) DEFAULT NULL,
  `memory_used_ratio` int(11) DEFAULT NULL,
  `ava_disk` int(11) DEFAULT NULL,
  `disk_used_ration` int(11) DEFAULT NULL,
  `memory_disk_level` int(11) DEFAULT NULL,
  `env` varchar(20) DEFAULT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_hour` datetime GENERATED ALWAYS AS (DATE_FORMAT(`update_time`,'%Y-%m-%d %H:00:00')) VIRTUAL,
  PRIMARY KEY (`id`));

落地

配合mcp的大模型型号目前验证试用最合适的是Claude 3.7 Sonnet,AI辅助编程工具cursor也是用的该模型型号

不同环境资源状态数据写入OB 桌面版

cat zone_config_local_ob.json
{
	"项目GZ":{"idb2_api":"http://idb2-api.xxxx},
	"项目GZ":{"idb2_api":"http://idb2-api.xxxx"},
	"项目03":{"idb2_api":"http://idb2-api.xxxx"},
	"项目04":{"idb2_api":"http://idb2-api.xxxx"},
	"项目05":{"idb2_api":"http://idb2-api.xxxx},
	"项目06":{"idb2_api":"http://idb2-api.xxxx},
	"项目xx":{"idb2_api":"http://idb2-api.xxxx"},
	"项目xx":{"idb2_api":"http://idb2-api.xxxx"},
	.....
}
if __name__ == '__main__':
    config_file="{}{}".format(config_file_path,'\zone_config_local_ob.json')
    with open(config_file, "r") as file:
        zone_idb2_api=json.load(file)
        for zone_key,value in zone_idb2_api.items():
            if 'idb2_api' not in value.keys():
                continue
            try:
                idb2_api=value['idb2_api']
                zone=zone_key

                conn_=mysql_conn()
                cur=conn_.cursor()
                cur.execute(str(get_machine_resource(idb2_api,zone)))
                conn_.commit()
                cur.close()
                conn_.close()

下载MCP并配置cursor

MCP+大模型分别测试对比过:

•云服务阿里百炼+自定义mcp(目前ob mcp不支持SSE方式,支持stdio进程间通信本地调用,无法远程调用)

•cherry+mcp(国内开源,页面做的丰富,支持配置多种模型平台,用的阿里百炼模型测试,中间结果较多)

•cursor+mcp(cursor是AI辅助编程利器,本身就是个基于VS Code 内核的IDE,公司有统一采购,目前能长期使用,模型也是基于claude3.7,但cursor的推理后数据可视化方面没有claude好)

•claude+mcp(目前最合适的是claude+mcp,响应速度、自然语言通过模型转SQL准确度、可视化的结果这方面是优势,而且mcp最开始就是claude搞出来的,比较成熟;好用的东西不会一直免费,有一定免费试用时间!)

下载github mcp-oceanbase到本地目录

oceanbase/mcp-oceanbase: MCP Server for OceanBase database and its tools

用powershell安装uv工具(Python 包管理和虚拟环境工具)

powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/0.7.2/install.ps1 | iex"

Installing to C:\Users\Administrator\.local\bin
  uv.exe
  uvx.exe
everything's installed!

To add C:\Users\Administrator\.local\bin to your PATH, either restart your shell or run:

    set Path=C:\Users\Administrator\.local\bin;%Path%   (cmd)
    $env:Path = "C:\Users\Administrator\.local\bin;$env:Path"   (powershell)
PS C:\Windows\system32> set Path=C:\Users\Administrator\.local\bin;%Path%cucurmcp server

{
  "mcpServers": {

    "desktop_ob": {
      "command": "python3",
      "args": [
        "C:\\xxx\\oceanbase_mcp_server\\server.py"
      ],
      "env": {
        "OB_USER": "mydb_rw",
        "OB_HOST": "127.0.0.1",
        "OB_PASSWORD": "xxxx",
        "OB_DATABASE": "mydb",
        "OB_PORT": "2881"
      }
    }
  }
}

开启AI助手对话

cursor claude-3.7模型分析执行sql准确度还是很高!每个执行的sql都是循环渐进

"query": "SELECT * FROM 全局资源水位表 WHERE dba = '杨家鑫';"

"query": "SHOW TABLES;"

"query": "DESCRIBE global_dashboard_resource;"

"query": "SELECT * FROM global_dashboard_resource WHERE owner_id = '杨家鑫';"

"query": "SELECT * FROM global_dashboard_resource WHERE discription LIKE '%杨家鑫%' OR discription LIKE '%DBA%杨家鑫%' OR discription LIKE '%杨家鑫%DBA%';"

"query": "SELECT * FROM global_dashboard_resource LIMIT 10;"

"query": "SELECT * FROM global_dashboard_resource WHERE owner_id LIKE '%杨%';"

"query": "SELECT DISTINCT owner_id FROM global_dashboard_resource;"

"query": "SELECT * FROM global_dashboard_resource WHERE owner_id = 'jiaxin.yang';"

image

image

2 个赞

:100: :100: :100: :100: :100: :100:

1 个赞

厉害

这个必须赞 :+1: :+1: :+1:

所以MCP今年很火啊