Archery数据库运维与性能优化功能
项目地址: https://gitcode.com/gh_mirrors/ar/Archery Archery作为专业的SQL审核查询平台，提供了全面的数据库运维与性能优化功能，包括慢查询分析与性能诊断、数据库参数管理与优化建议、会话管理与连接控制、表空间监控与存储优化等核心模块。通过完善的数据采集、存储架构和可视化分析，帮助DBA和开发人员快速定位和解决数据库性能问题，实现数据库参数的规范化管理和安全变更，有效监控和管理数据库连接状态，以及优化数据库存储空间使用。
慢查询分析与性能诊断
在现代数据库运维中，慢查询分析和性能诊断是保障数据库稳定运行的关键环节。Archery作为专业的SQL审核查询平台，提供了完善的慢查询分析功能，帮助DBA和开发人员快速定位和解决数据库性能问题。
慢查询数据采集与存储
Archery通过精心设计的数据库表结构来存储慢查询数据，主要包含两个核心表：
mysql_slow_query_review（慢查询统计表）
CREATE TABLE `mysql_slow_query_review` (
`checksum` CHAR(32) NOT NULL, -- SQL指纹的MD5校验和
`fingerprint` longtext NOT NULL, -- SQL指纹（规范化后的SQL）
`sample` longtext NOT NULL, -- 原始SQL样例
`first_seen` datetime(6) DEFAULT NULL, -- 首次出现时间
`last_seen` datetime(6) DEFAULT NULL, -- 最后出现时间
`reviewed_by` varchar(20) DEFAULT NULL, -- 审核人
`reviewed_on` datetime(6) DEFAULT NULL, -- 审核时间
`comments` longtext, -- 审核意见
`reviewed_status` varchar(24) DEFAULT NULL, -- 审核状态
PRIMARY KEY (`checksum`),
KEY `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql_slow_query_review_history（慢查询明细表）
CREATE TABLE `mysql_slow_query_review_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname_max` varchar(64) NOT NULL, -- 主机名
`client_max` varchar(64) DEFAULT NULL, -- 客户端地址
`user_max` varchar(64) NOT NULL, -- 用户名
`db_max` varchar(64) DEFAULT NULL, -- 数据库名
`checksum` CHAR(32) NOT NULL, -- 关联的SQL指纹
`sample` longtext NOT NULL, -- SQL样例
`ts_min` datetime(6) NOT NULL, -- 统计开始时间
`ts_max` datetime(6) NOT NULL, -- 统计结束时间
-- 丰富的性能指标字段...
PRIMARY KEY (`id`),
UNIQUE KEY (checksum, ts_min, ts_max),
KEY `idx_hostname_max_ts_min` (`hostname_max`,`ts_min`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
慢查询分析功能架构
Archery的慢查询分析功能采用分层架构设计，确保高效的数据处理和可视化展示：
核心功能特性
1. 多维度的慢查询统计
Archery提供全面的慢查询统计分析，包括：
| 统计维度 | 说明 | 技术实现 |
|---|---|---|
| 执行次数统计 | 统计SQL语句的执行频率 | MySQLTotalExecutionCounts=Sum("slowqueryhistory__ts_cnt") |
| 执行时间分析 | 分析SQL的平均执行时间和总耗时 | QueryTimeAvg=Sum("query_time_sum")/Sum("ts_cnt") |
| 扫描行数统计 | 统计SQL扫描的数据行数 | ParseTotalRowCounts=Sum("rows_examined_sum") |
| 返回行数分析 | 分析SQL返回的数据行数 | ReturnTotalRowCounts=Sum("rows_sent_sum") |
| 锁等待时间 | 统计SQL的锁等待时间 | LockTimes=Sum("lock_time_sum") |
2. 灵活的查询条件过滤
支持多种过滤条件组合查询：
- 时间范围筛选（开始时间、结束时间）
- 数据库名称过滤
- SQL指纹模糊搜索
- 多字段排序支持
# 慢查询统计查询示例
slowsql_obj = SlowQuery.objects.filter(
slowqueryhistory__hostname_max=instance_info.host + ":" + str(instance_info.port),
slowqueryhistory__ts_min__range=(start_time, end_time),
fingerprint__icontains=search,
**filter_kwargs
).annotate(
SQLText=Max("fingerprint"),
SQLId=F("checksum")
).values("SQLText", "SQLId")
3. 可视化趋势分析
Archery集成pyecharts库，提供丰富的可视化图表：
# 趋势图表生成代码示例
line = Line(init_opts=opts.InitOpts(width="800", height="380px"))
line.add_xaxis(cnt_x_data)
line.add_yaxis(
"慢查次数", cnt_y_data, is_smooth=True,
markline_opts=opts.MarkLineOpts(data=[
opts.MarkLineItem(type_="max", name="最大值"),
opts.MarkLineItem(type_="average", name="平均值")
])
)
line.add_yaxis("慢查时长(95%)", pct_y_data, is_smooth=True, is_symbol_show=False)
4. 详细的执行信息展示
对于每条慢查询，Archery提供完整的执行上下文信息：
| 信息类别 | 包含内容 | 重要性 |
|---|---|---|
| 基础信息 | 数据库、用户、客户端地址 | 定位问题来源 |
| 时间指标 | 执行时间、锁时间、95%分位值 | 评估性能影响 |
| 资源消耗 | 扫描行数、返回行数、临时表使用 | 分析资源使用效率 |
| 执行特征 | 全表扫描、文件排序、连接方式 | 识别优化点 |
性能诊断最佳实践
1. 定期慢查询巡检
建立定期的慢查询巡检机制，重点关注：
- 执行频率高的慢查询
- 执行时间长的SQL语句
- 扫描行数过多的查询
- 锁等待时间长的操作
2. 优先级排序策略
按照影响程度对慢查询进行优先级排序：
3. 优化建议生成
基于慢查询分析结果，Archery可以生成针对性的优化建议：
- 索引优化：为缺少合适索引的查询添加索引
- SQL重写：优化查询逻辑，减少不必要的连接和子查询
- 架构调整：考虑分表分库、读写分离等架构优化
- 参数调优：调整数据库配置参数，如缓冲池大小、连接数等
技术实现细节
数据聚合算法
Archery采用高效的聚合算法处理慢查询数据：
# 数据聚合示例
.annotate(
QueryTimeAvg=Sum("slowqueryhistory__query_time_sum") /
Sum("slowqueryhistory__ts_cnt"), # 平均执行时长
MySQLTotalExecutionCounts=Sum("slowqueryhistory__ts_cnt"), # 执行总次数
ParseRowAvg=Sum("slowqueryhistory__rows_examined_sum") /
Sum("slowqueryhistory__ts_cnt"), # 平均扫描行数
ReturnRowAvg=Sum("slowqueryhistory__rows_sent_sum") /
Sum("slowqueryhistory__ts_cnt") # 平均返回行数
)
性能指标计算
支持多种性能指标的计算和展示：
| 指标名称 | 计算公式 | 说明 |
|---|---|---|
| 95%分位响应时间 | query_time_pct_95 | 95%的请求在此时间内完成 |
| 平均响应时间 | Query_time_sum / ts_cnt | 平均每次执行的耗时 |
| 锁等待比率 | Lock_time_sum / Query_time_sum | 锁等待时间占比 |
| 行处理效率 | Rows_sent_sum / Rows_examined_sum | 有效数据返回比例 |
集成与扩展
Archery的慢查询分析功能支持多种数据源集成：
- 自建MySQL实例：通过pt-query-digest解析慢日志
- 阿里云RDS：直接调用云API获取慢日志数据
- 其他云数据库：支持扩展其他云厂商的API集成
这种灵活的架构设计使得Archery能够适应不同的运维环境，为各类MySQL实例提供统一的慢查询分析体验。
通过Archery的慢查询分析与性能诊断功能，运维团队可以快速定位数据库性能瓶颈，制定有效的优化策略，从而保障数据库系统的稳定高效运行。
数据库参数管理与优化建议
Archery作为一款专业的SQL审核查询平台，提供了完善的数据库参数管理功能，帮助DBA和开发人员更好地管理和优化数据库实例配置。通过参数模板配置、在线参数修改、历史记录追踪等功能，Archery实现了数据库参数的规范化管理和安全变更。
参数模板配置管理
Archery通过ParamTemplate模型实现了参数模板的统一管理，支持多种数据库类型的参数配置：
class ParamTemplate(models.Model):
"""实例参数模板配置"""
db_type = models.CharField("数据库类型", max_length=20, choices=DB_TYPE_CHOICES)
variable_name = models.CharField("参数名", max_length=64)
default_value = models.CharField("默认参数值", max_length=1024)
editable = models.BooleanField("是否支持修改", default=False)
valid_values = models.CharField(
"有效参数值，范围参数[1-65535]，值参数[ON|OFF]", max_length=1024, blank=True
)
description = models.CharField("参数描述", max_length=1024, blank=True)
参数模板的主要特性包括：
| 字段名 | 数据类型 | 说明 | 示例值 |
|---|---|---|---|
| db_type | CharField | 数据库类型 | mysql, oracle, pgsql |
| variable_name | CharField | 参数名称 | max_connections, innodb_buffer_pool_size |
| default_value | CharField | 默认参数值 | 151, 128M |
| editable | BooleanField | 是否支持在线修改 | True/False |
| valid_values | CharField | 有效参数值范围 | [1-65535], [ON|OFF] |
| description | CharField | 参数详细描述 | 最大连接数配置 |
在线参数修改流程
Archery提供了安全的在线参数修改功能，通过param_edit视图函数实现：
@permission_required("sql.param_edit", raise_exception=True)
def param_edit(request):
user = request.user
instance_id = request.POST.get("instance_id")
variable_name = request.POST.get("variable_name")
variable_value = request.POST.get("runtime_value")
# 参数校验和修改逻辑
engine = get_engine(instance=ins)
variable_name = engine.escape_string(variable_name)
variable_value = engine.escape_string(variable_value)
# 校验参数模板配置
if not ParamTemplate.objects.filter(variable_name=variable_name).exists():
return error_response("请先在参数模板中配置该参数！")
# 获取当前运行值并比较
runtime_value = engine.get_variables(variables=[variable_name]).rows[0][1]
if variable_value == runtime_value:
return error_response("参数值与实际运行值一致，未调整！")
# 执行参数修改
set_result = engine.set_variable(
variable_name=variable_name, variable_value=variable_value
)
参数修改的安全机制：
- 权限控制：需要
sql.param_edit权限才能执行参数修改 - 模板校验：必须先在参数模板中配置才能修改
- 值校验：避免重复设置相同的参数值
- SQL注入防护：使用
escape_string进行参数转义
参数历史记录追踪
所有参数修改操作都会被记录到ParamHistory模型中，便于审计和追踪：
class ParamHistory(models.Model):
"""可在线修改的动态参数配置"""
instance = models.ForeignKey(Instance, on_delete=models.CASCADE)
variable_name = models.CharField("参数名", max_length=64)
old_var = models.CharField("修改前参数值", max_length=1024)
new_var = models.CharField("修改后参数值", max_length=1024)
set_sql = models.CharField("在线变更配置执行的SQL语句", max_length=1024)
user_name = models.CharField("修改人", max_length=30)
user_display = models.CharField("修改人中文名", max_length=50)
create_time = models.DateTimeField("参数被修改时间点", auto_now_add=True)
历史记录包含的完整信息：
- 实例信息：哪个数据库实例的参数被修改
- 参数详情：参数名称、修改前后的值
- 执行SQL：实际执行的SET语句
- 操作人信息：修改人和显示名称
- 时间戳：精确的修改时间
参数查询与展示
Archery提供参数列表查询功能，支持搜索和过滤：
参数查询支持的功能：
- 实例筛选：按数据库实例查看参数
- 可编辑过滤：只显示支持在线修改的参数
- 搜索功能：按参数名称搜索
- 模板对比：显示默认值和当前运行值的对比
最佳实践与优化建议
1. 关键性能参数优化
对于MySQL数据库，建议重点关注以下性能相关参数：
| 参数名称 | 推荐值 | 说明 |
|---|---|---|
| innodb_buffer_pool_size | 物理内存的70-80% | InnoDB缓冲池大小 |
| innodb_log_file_size | 1-2GB | Redo日志文件大小 |
| max_connections | 根据业务需求调整 | 最大连接数 |
| query_cache_size | 0(MySQL 8.0已移除) | 查询缓存大小 |
| tmp_table_size | 64M-256M | 临时表大小 |
2. 安全参数配置
-- 安全相关的参数配置
SET GLOBAL validate_password_policy = STRONG;
SET GLOBAL max_user_connections = 30;
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;
3. 监控与告警参数
建议设置监控告警的关键参数阈值：
4. 参数修改工作流
常见问题处理
参数修改不生效
- 检查参数是否需要重启生效
- 验证参数名称是否正确
- 确认参数值格式是否符合要求
性能参数调优
- 使用Archery的慢查询分析功能定位瓶颈
- 结合数据库诊断工具进行分析
- 采用渐进式调整策略，每次只调整一个参数
参数模板管理
- 定期更新参数模板，添加新的参数说明
- 为不同数据库版本维护不同的模板
- 设置参数值的合理范围，避免错误配置
通过Archery的参数管理功能，可以实现数据库参数的规范化、可视化和安全化管理，大大提高了数据库运维的效率和安全性。
会话管理与连接控制
Archery作为一款专业的数据库运维平台，提供了完善的会话管理与连接控制功能，帮助DBA高效地监控和管理数据库连接状态。通过直观的Web界面，用户可以实时查看数据库进程列表、诊断连接问题，并在必要时安全地终止异常会话。
进程列表监控
Archery支持多种数据库类型的进程列表查询，包括MySQL、Oracle、MongoDB等。通过统一的接口，用户可以查看当前数据库实例的所有活跃连接信息：
# 进程列表查询核心代码
@permission_required("sql.process_view", raise_exception=True)
def process(request):
instance_name = request.POST.get("instance_name")
command_type = request.POST.get("command_type")
# 获取用户有权限的实例
instance = user_instances(request.user).get(instance_name=instance_name)
# 获取数据库引擎并执行processlist查询
query_engine = get_engine(instance=instance)
query_result = query_engine.processlist(command_type=command_type, **request_kwargs)
# 返回格式化结果
if not query_result.error:
processlist = query_result.to_dict()
result = {"status": 0创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
