oceanbase qps计算优化

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】
【 使用版本 】
【问题描述】清晰明确描述问题
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

def get_server_metrics():
#local_ip = get_local_ip()
#if local_ip is None:
# return (0, 0, 0, 0, 0)
try:
conn = pymysql.connect(host=‘127.0.0.1’, user=‘monitor’, port=2881, password=’*****’, database=‘oceanbase’)
cursor = conn.cursor()
cursor.execute(f"SELECT /* MONITOR_AGENT */ con_id AS tenant_id, SUM(value) AS initial_value FROM v$sysstat WHERE stat_id IN (40000, 40002, 40004, 40006, 40008, 40018) AND class < 1000 AND con_id = 1;")
result = cursor.fetchone()
print(“result:”,result)

metrics1 = get_server_metrics()
time.sleep(60)
metrics2 = get_server_metrics()
sys_qpsdiff = (metrics2[1] - metrics1[1])/60

这种算法算起来的qps监控数据比官方prometheus+grafana模板获取的数据偏高，有什么好的优化方法吗

#3

根据您提供的信息，您正在使用自定义脚本从 v$sysstat 视图中获取 QPS 数据，并发现该数据与通过官方 Prometheus + Grafana 模板获取的数据存在差异。这种差异可能是由多种因素引起的，下面是一些可能的原因及优化建议：

1. 统计口径不一致

  • 官方 Prometheus + Grafana 模板：通常会使用更精确的统计口径，例如区分不同类型的查询（如 SELECT、INSERT、UPDATE、DELETE 等），并且可能会排除一些内部查询。
  • 自定义脚本：您当前的脚本可能没有区分不同的查询类型，或者没有排除一些内部查询，导致统计结果偏高。

优化建议：

  • 细化统计口径：您可以尝试细化统计口径，分别统计不同类型的查询。例如，可以使用以下 SQL 语句来分别统计 SELECT、INSERT、UPDATE 和 DELETE 的 QPS：
    SELECT 
    SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
    SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
FROM v$sysstat
WHERE class < 1000 AND con_id = 1;

2. 时间间隔的影响

  • 自定义脚本：您当前的脚本每隔 60 秒获取一次数据，然后计算 QPS。如果在这 60 秒内有一些突发的查询，可能会导致 QPS 偏高。
  • 官方 Prometheus + Grafana 模板：通常会使用更细粒度的时间间隔（如 15 秒或 30 秒），并且会进行平滑处理，以减少突发查询的影响。

优化建议：

  • 缩短时间间隔：您可以尝试缩短时间间隔，例如每 15 秒或 30 秒获取一次数据，然后计算 QPS。这样可以更好地反映实际的 QPS 情况。
    import time

def get_server_metrics():
    try:
        conn = pymysql.connect(host='127.0.0.1', user='monitor', port=2881, password='*****', database='oceanbase')
        cursor = conn.cursor()
        cursor.execute(f"""
            SELECT 
                SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
                SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
            FROM v$sysstat
            WHERE class < 1000 AND con_id = 1;
        """)
        result = cursor.fetchone()
        print("result:", result)
        return result
    except Exception as e:
        print("Error:", e)
        return (0, 0)

metrics1 = get_server_metrics()
time.sleep(15)  # 缩短时间间隔
metrics2 = get_server_metrics()
sys_qpsdiff = (metrics2[1] - metrics1[1]) / 15  # 调整时间间隔

3. 内部查询的影响

  • 自定义脚本：您当前的脚本可能没有排除内部查询，这些查询可能会增加 QPS 的统计值。
  • 官方 Prometheus + Grafana 模板：通常会排除内部查询，以确保统计结果的准确性。

优化建议：

  • 排除内部查询：您可以尝试排除内部查询，例如使用以下 SQL 语句：
    SELECT 
    SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
    SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
FROM v$sysstat
WHERE class < 1000 AND con_id = 1 AND name NOT LIKE '%internal%';

4. 数据平滑处理

  • 自定义脚本：您当前的脚本没有进行数据平滑处理，可能会受到突发查询的影响。
  • 官方 Prometheus + Grafana 模板：通常会进行数据平滑处理，以减少突发查询的影响。

优化建议：

  • 数据平滑处理：您可以尝试对数据进行平滑处理，例如使用移动平均法。以下是一个简单的示例：
    import time
from collections import deque

window_size = 5  # 平滑窗口大小
qps_values = deque(maxlen=window_size)

def get_server_metrics():
    try:
        conn = pymysql.connect(host='127.0.0.1', user='monitor', port=2881, password='*****', database='oceanbase')
        cursor = conn.cursor()
        cursor.execute(f"""
            SELECT 
                SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
                SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
            FROM v$sysstat
            WHERE class < 1000 AND con_id = 1 AND name NOT LIKE '%internal%';
        """)
        result = cursor.fetchone()
        print("result:", result)
        return result
    except Exception as e:
        print("Error:", e)
        return (0, 0)

while True:
    metrics1 = get_server_metrics()
    time.sleep(15)  # 缩短时间间隔
    metrics2 = get_server_metrics()
    qps_diff = (metrics2[1] - metrics1[1]) / 15  # 调整时间间隔
    qps_values.append(qps_diff)
    smoothed_qps = sum(qps_values) / len(qps_values)
    print("Smoothed QPS:", smoothed_qps)

总结

通过以上优化建议，您可以尝试细化统计口径、缩短时间间隔、排除内部查询以及进行数据平滑处理，以使自定义脚本获取的 QPS 数据更加接近官方 Prometheus + Grafana 模板的数据。希望这些建议对您有所帮助！

具体信息可参考以下文档:

  1. 检查 NTP 偏移量
  2. 手动收集统计信息
  3. 并行执行调优技巧
  4. 4000 ~ 4499
  5. 统计信息和估行机制的使用
  6. 手动统计信息收集
  7. 性能调优概述
#4

和阿里提供的脚本看,qps
python2.7 dooba.py -h 10.104.102.17 -u root -P 2881 -p’****’ 相关数据也不是完全一致

#5

您提到的阿里提供的脚本 dooba.py 与您自定义的脚本在 QPS 计算上存在差异。这种情况可能是由于以下几个原因造成的：

1. 统计口径不一致

  • 阿里脚本：可能使用了更精细的统计口径，例如区分不同类型的查询（如 SELECT、INSERT、UPDATE、DELETE 等），并且可能排除了一些内部查询。
  • 自定义脚本：您当前的脚本可能没有区分不同的查询类型，或者没有排除一些内部查询，导致统计结果偏高。

优化建议：

  • 细化统计口径：您可以尝试细化统计口径，分别统计不同类型的查询。例如，可以使用以下 SQL 语句来分别统计 SELECT、INSERT、UPDATE 和 DELETE 的 QPS：
    SELECT 
    SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
    SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
FROM v$sysstat
WHERE class < 1000 AND con_id = 1;

2. 时间间隔的影响

  • 阿里脚本：可能使用了更细粒度的时间间隔（如 15 秒或 30 秒），并且可能进行了平滑处理，以减少突发查询的影响。
  • 自定义脚本：您当前的脚本每隔 60 秒获取一次数据，然后计算 QPS。如果在这 60 秒内有一些突发的查询，可能会导致 QPS 偏高。

优化建议：

  • 缩短时间间隔：您可以尝试缩短时间间隔，例如每 15 秒或 30 秒获取一次数据，然后计算 QPS。这样可以更好地反映实际的 QPS 情况。
    import time

def get_server_metrics():
    try:
        conn = pymysql.connect(host='127.0.0.1', user='monitor', port=2881, password='*****', database='oceanbase')
        cursor = conn.cursor()
        cursor.execute(f"""
            SELECT 
                SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
                SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
            FROM v$sysstat
            WHERE class < 1000 AND con_id = 1;
        """)
        result = cursor.fetchone()
        print("result:", result)
        return result
    except Exception as e:
        print("Error:", e)
        return (0, 0)

metrics1 = get_server_metrics()
time.sleep(15)  # 缩短时间间隔
metrics2 = get_server_metrics()
sys_qpsdiff = (metrics2[1] - metrics1[1]) / 15  # 调整时间间隔

3. 内部查询的影响

  • 阿里脚本：可能排除了内部查询，以确保统计结果的准确性。
  • 自定义脚本：您当前的脚本可能没有排除内部查询，这些查询可能会增加 QPS 的统计值。

优化建议：

  • 排除内部查询：您可以尝试排除内部查询，例如使用以下 SQL 语句：
    SELECT 
    SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
    SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
FROM v$sysstat
WHERE class < 1000 AND con_id = 1 AND name NOT LIKE '%internal%';

4. 数据平滑处理

  • 阿里脚本：可能进行了数据平滑处理，以减少突发查询的影响。
  • 自定义脚本：您当前的脚本没有进行数据平滑处理，可能会受到突发查询的影响。

优化建议：

  • 数据平滑处理：您可以尝试对数据进行平滑处理，例如使用移动平均法。以下是一个简单的示例：
    import time
from collections import deque

window_size = 5  # 平滑窗口大小
qps_values = deque(maxlen=window_size)

def get_server_metrics():
    try:
        conn = pymysql.connect(host='127.0.0.1', user='monitor', port=2881, password='*****', database='oceanbase')
        cursor = conn.cursor()
        cursor.execute(f"""
            SELECT 
                SUM(CASE WHEN name = 'queries on user tables' THEN value ELSE 0 END) AS select_qps,
                SUM(CASE WHEN name = 'DML statements executed' THEN value ELSE 0 END) AS dml_qps
            FROM v$sysstat
            WHERE class < 1000 AND con_id = 1 AND name NOT LIKE '%internal%';
        """)
        result = cursor.fetchone()
        print("result:", result)
        return result
    except Exception as e:
        print("Error:", e)
        return (0, 0)

while True:
    metrics1 = get_server_metrics()
    time.sleep(15)  # 缩短时间间隔
    metrics2 = get_server_metrics()
    qps_diff = (metrics2[1] - metrics1[1]) / 15  # 调整时间间隔
    qps_values.append(qps_diff)
    smoothed_qps = sum(qps_values) / len(qps_values)
    print("Smoothed QPS:", smoothed_qps)

5. 脚本实现细节

  • 阿里脚本：可能使用了更高效的实现方式，例如多线程、异步 I/O 等，以提高性能和准确性。
  • 自定义脚本：您当前的脚本可能没有使用这些高级技术，导致统计结果不够准确。

优化建议：

  • 参考阿里脚本：您可以参考阿里提供的 dooba.py 脚本，了解其具体的实现细节，特别是如何处理统计口径、时间间隔、内部查询和数据平滑等方面。

总结

通过以上优化建议，您可以尝试细化统计口径、缩短时间间隔、排除内部查询以及进行数据平滑处理，以使自定义脚本获取的 QPS 数据更加接近阿里提供的脚本的数据。希望这些建议对您有所帮助！

如果您需要进一步的帮助，可以参考以下文档：

  1. OceanBase 性能调优指南
  2. 手动收集统计信息
  3. 并行执行调优技巧

检索到的文档如下:

  1. MySQL-connector-python 连接 OceanBase 数据库示例程序
  2. PyMySQL 连接 OceanBase 数据库示例程序
  3. ODP 配置管理
  4. 修改日志流的法定成员数
  5. mysqlclient 连接 OceanBase 数据库示例程序
  6. SQL FAQ
  7. 并行执行入门实践
  8. 发起租户级备份

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)