概述
在数字化浪潮下,零售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';"