tidb教程FG114-TiDB性能调优与诊断

本文档详细介绍TiDB性能调优与诊断的方法和实践，包括性能调优概述、诊断方法、调优方案等内容。风哥教程参考TiDB官方文档性能调优指南、诊断指南等内容，适合DBA和运维人员进行TiDB集群性能优化。

Part01-基础概念与理论知识

1.1 性能调优概述

性能调优是指通过各种手段提高系统的性能，包括响应速度、吞吐量、资源利用率等。TiDB的性能调优涉及多个层面，包括硬件、操作系统、数据库配置、SQL语句等。

TiDB性能调优层次：

硬件层：服务器、存储、网络等
操作系统层：内核参数、文件系统等
数据库配置层：TiDB、TiKV、PD等组件的配置
SQL层：SQL语句优化、索引设计等
应用层：连接池、缓存等

1.2 性能诊断方法

性能诊断是指通过各种工具和方法，找出系统性能瓶颈的过程。TiDB提供了多种性能诊断工具，包括TiUP、Prometheus、Grafana、TiDB Dashboard等。

# 性能诊断方法

# 1. 监控工具
# – Prometheus + Grafana：监控系统指标
# – TiDB Dashboard：Web界面监控
# – tikv-ctl：TiKV诊断工具
# – pd-ctl：PD诊断工具

# 2. 日志分析
# – TiDB日志：分析SQL执行情况
# – TiKV日志：分析存储层性能
# – PD日志：分析调度情况

# 3. 性能分析
# – EXPLAIN：分析SQL执行计划
# – PROFILE：分析SQL执行细节
# – ANALYZE：更新统计信息
# – TRACE：跟踪SQL执行过程

# 4. 系统工具
# – top：查看系统负载
# – iostat：查看I/O性能
# – vmstat：查看虚拟内存状态
# – netstat：查看网络状态

# 5. 压力测试
# – Sysbench：基准测试
# – TPCC：OLTP测试
# – TPC-H：OLAP测试

# 6. 诊断流程
# – 收集信息：监控数据、日志、系统状态
# – 分析瓶颈：找出性能瓶颈
# – 制定方案：根据瓶颈制定优化方案
# – 实施优化：执行优化措施
# – 验证效果：验证优化效果

# 7. 常见瓶颈
# – CPU瓶颈：SQL执行、压缩/解压等
# – I/O瓶颈：磁盘读写、网络传输等
# – 内存瓶颈：内存不足、内存泄漏等
# – 网络瓶颈：网络延迟、带宽不足等
# – 锁瓶颈：事务冲突、死锁等
# – 配置瓶颈：参数设置不合理等

1.3 性能指标体系

性能指标是衡量系统性能的重要依据，TiDB提供了丰富的性能指标，包括系统层面、数据库层面、SQL层面等。

风哥提示：性能调优是一个持续的过程，需要定期监控和分析系统性能，及时发现并解决性能问题。更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 性能规划

# 性能规划

# 1. 业务需求分析
# – QPS需求：每秒查询数
# – TPS需求：每秒事务数
# – 响应时间要求：平均响应时间、P99响应时间
# – 数据量：当前数据量、增长趋势
# – 并发连接数：最大并发连接数

# 2. 容量规划
# – 计算容量：CPU、内存需求
# – 存储容量：数据存储空间
# – 网络容量：网络带宽需求
# – 备份容量：备份存储空间

# 3. 性能目标
# – 响应时间：< 100ms # - QPS：满足业务需求 # - TPS：满足业务需求 # - 可用性：99.99% # - 扩展性：支持水平扩展 # 4. 规划方法 # - 基准测试：使用Sysbench等工具进行基准测试 # - 压力测试：模拟真实业务负载 # - 容量估算：根据测试结果估算容量 # - 预留冗余：预留30-50%的冗余容量 # 5. 规划示例 # 假设业务需求： # - QPS：10000 # - TPS：5000 # - 响应时间：< 50ms # - 数据量：1TB，年增长率30% # 容量估算： # - TiDB节点：4核8GB * 4节点 # - TiKV节点：8核16GB * 6节点，每个节点1TB SSD # - PD节点：4核8GB * 3节点 # - 网络：10Gbps # 6. 规划验证 # - 测试环境验证：在测试环境中验证规划 # - 上线前验证：上线前进行压力测试 # - 上线后监控：上线后持续监控性能 # 7. 动态调整 # - 根据业务增长调整容量 # - 根据性能瓶颈调整配置 # - 定期进行性能评估和规划调整

2.2 硬件选型建议

# 硬件选型建议

# 1. TiDB节点
# – CPU：Intel Xeon E5-2670 v3或更高，核心数8-16核
# – 内存：32GB-64GB
# – 存储：SSD，500GB-1TB
# – 网络：10Gbps
# – 建议：适合部署在计算密集型服务器

# 2. TiKV节点
# – CPU：Intel Xeon E5-2670 v3或更高，核心数8-16核
# – 内存：64GB-128GB风哥提示：
# – 存储：NVMe SSD，1TB-4TB
# – 网络：10Gbps
# – 建议：适合部署在存储密集型服务器

# 3. PD节点
# – CPU：Intel Xeon E5-2670 v3或更高，核心数4-8核
# – 内存：16GB-32GB
# – 存储：SSD，200GB-500GB
# – 网络：10Gbps
# – 建议：适合部署在高可靠性服务器

# 4. 监控节点
# – CPU：Intel Xeon E5-2670 v3或更高，核心数4-8核
# – 内存：16GB-32GB
# – 存储：HDD，1TB-2TB
# – 网络：1Gbps
# – 建议：适合部署在通用服务器

# 5. 硬件配置建议
# – 生产环境：至少3个TiDB节点，3个TiKV节点，3个PD节点
# – 测试环境：1个TiDB节点，1个TiKV节点，1个PD节点
# – 开发环境：1个TiDB节点，1个TiKV节点，1个PD节点

# 6. 存储选择
# – 推荐：NVMe SSD，性能最佳
# – 次选：SATA SSD，性能较好
# – 不推荐：HDD，性能较差

# 7. 网络配置
# – 推荐：10Gbps网络，低延迟
# – 次选：1Gbps网络，适合小规模集群
# – 要求：低延迟、高带宽、稳定可靠

# 8. 硬件调优
# – 关闭NUMA：echo 0 > /proc/sys/kernel/numa_balancing
# – 调整CPU频率：设置为性能模式
# – 调整内存配置：关闭swap，增大vm.swappiness
# – 调整磁盘调度：使用deadline或none调度器

# 9. 硬件监控
# – 监控CPU使用率
# – 监控内存使用率
# – 监控磁盘I/O
# – 监控网络流量
# – 监控硬件温度

2.3 配置优化建议

# 配置优化建议

# 1. TiDB配置优化
# – 内存配置
# – performance.max-procs：设置为CPU核心数
# – performance.tcp-keep-alive：设置为true
# – memory.oom-action：设置为”log”

# – SQL优化
# – prepared-plan-cache.enabled：设置为true
# – txn.txn-local-latches：设置为true
# – tikv-client.max-batch-size：设置为1024

# – 并发控制
# – oom-use-tmp-storage：设置为true
# – tmp-storage-path：设置为SSD路径
# – tmp-storage-quota：设置为适当大小

# 2. TiKV配置优化
# – 内存配置
# – memory.limit：设置为物理内存的80%
# – memory.txn-memory-quota：设置为适当大小
# – storage.block-cache.capacity：设置为物理内存的40%

# – I/O优化
# – storage.io-threads：设置为CPU核心数的一半
# – storage.raft-store.io-thread-count：设置为CPU核心数的一半
# – storage.scheduler-worker-pool-size：设置为CPU核心数

# – 调度优化
# – raftdb.max-open-files：设置为4096
# – raftdb.keep-log-file-num：设置为10
# – server.grpc-concurrency：设置为CPU核心数

# 3. PD配置优化
# – 调度优化
# – schedule.leader-schedule-limit：设置为4
# – schedule.region-schedule-limit：设置为2048
# – schedule.replica-schedule-limit：设置为64

# – 存储优化
# – storage.schedule-policy：设置为”balance”
# – replication.location-labels：设置为”zone,rack,host”
# – replication.max-replicas：设置为3

# 4. 操作系统配置优化
# – 网络优化
# – net.core.somaxconn：设置为65535
# – net.ipv4.tcp_max_syn_backlog：设置为65535
# – net.ipv4.tcp_fin_timeout：设置为30
# – net.ipv4.tcp_keepalive_time：设置为600

# – 内存优化
# – vm.swappiness：设置为0学习交流加群风哥QQ113257174
# – vm.overcommit_memory：设置为1
# – vm.max_map_count：设置为655360

# – 文件系统优化
# – 挂载选项：noatime,nodiratime,barrier=0
# – 文件系统类型：ext4或xfs

# 5. 配置最佳实践
# – 根据硬件配置调整参数
# – 根据业务负载调整参数
# – 定期检查和调整配置
# – 记录配置变更历史
# – 测试配置变更效果

生产环境建议：根据硬件配置和业务需求，合理调整TiDB、TiKV、PD的配置参数。建议在测试环境中验证配置变更效果后，再应用到生产环境。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 性能调优方案

3.1.1 SQL调优

# SQL调优

# 1. SQL语句优化
# – 避免全表扫描：使用索引
# – 避免SELECT *：只选择需要的列
# – 避免子查询：使用JOIN代替
# – 避免复杂表达式：简化WHERE条件
# – 避免ORDER BY RAND()：性能极差

# 2. 索引优化
# – 为频繁查询的列创建索引
# – 为WHERE条件中的列创建索引
# – 为JOIN条件中的列创建索引
# – 为ORDER BY和GROUP BY的列创建索引
# – 避免创建过多索引：影响写入性能

# 3. 执行计划分析
# – 使用EXPLAIN查看执行计划
# – 分析扫描行数、索引使用情况
# – 优化执行计划中的瓶颈
# – 强制使用合适的索引：USE INDEX

# 4. 统计信息更新
# – 定期执行ANALYZE TABLE
# – 当数据分布发生变化时执行ANALYZE TABLE
# – 调整统计信息收集参数

# 5. 事务优化
# – 减少事务大小：拆分大事务
# – 缩短事务时间：快速提交
# – 避免长事务：设置合理的超时时间
# – 使用乐观锁：减少锁冲突

# 6. 连接池优化
# – 配置合理的连接池大小
# – 设置合适的连接超时时间
# – 定期清理空闲连接
# – 使用连接池监控

# 7. SQL调优工具
# – TiDB Dashboard：SQL分析
# – EXPLAIN ANALYZE：详细执行计划
# – PROFILE：执行细节分析
# – TRACE：执行过程跟踪

# 8. SQL调优示例
# 优化前：
SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;

# 优化后：
ALTER TABLE fgedudb.fgedu_users ADD INDEX idx_age_created_at (age, created_at);
SELECT id, username, email FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;

# 9. SQL监控
# – 监控慢查询
# – 监控执行计划异常
# – 监控SQL执行时间
# – 监控SQL错误率

# 10. SQL最佳实践
# – 使用参数化查询：避免SQL注入
# – 使用绑定变量：提高执行计划缓存命中率
# – 避免使用复杂视图：影响性能
# – 避免使用存储过程：TiDB对存储过程支持有限

3.1.2 系统调优

# 系统调优

# 1. CPU调优
# – 关闭不必要的服务
# – 调整进程优先级
# – 开启CPU性能模式
# – 避免CPU密集型操作

# 2. 内存调优
# – 分配足够的内存
# – 关闭swap
# – 调整内存参数
# – 监控内存使用情况

# 3. I/O调优
# – 使用SSD存储
# – 调整I/O调度器
# – 优化文件系统
# – 监控I/O性能

# 4. 网络调优
# – 使用高速网络
# – 调整网络参数
# – 优化网络拓扑
# – 监控网络性能

# 5. 存储调优
# – 使用NVMe SSD
# – 合理规划存储空间
# – 定期清理磁盘空间
# – 监控存储健康状态

# 6. 操作系统调优
# – 升级内核版本
# – 调整系统参数
# – 关闭不必要的功能
# – 定期更新系统

# 7. 调优工具
# – sar：系统资源监控
# – vmstat：虚拟内存监控
# – iostat：I/O监控
# – netstat：网络监控
# – top：进程监控

# 8. 调优示例
# 调整网络参数：
cat > /etc/sysctl.d/tidb.conf << EOF net.core.somaxconn = 65535 net.ipv4.tcp_max_syn_backlog = 65535 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_time = 600 net.ipv4.tcp_keepalive_probes = 5 net.ipv4.tcp_keepalive_intvl = 15 EOF sysctl -p /etc/sysctl.d/tidb.conf # 调整磁盘调度： echo deadline > /sys/block/sda/queue/scheduler

# 9. 监控与告警
# – 监控系统负载
# – 监控资源使用率
# – 配置告警阈值
# – 及时处理告警

# 10. 系统调优最佳实践
# – 定期进行系统调优
# – 记录调优过程和效果
# – 持续监控系统性能
# – 根据业务变化调整调优策略

3.2 性能诊断步骤

# 性能诊断步骤

# 1. 收集信息
# – 系统信息：CPU、内存、磁盘、网络
# – 数据库信息：版本、配置、状态
# – SQL信息：慢查询、执行计划
# – 监控信息：Prometheus、Grafana
# – 日志信息：TiDB、TiKV、PD日志

# 2. 分析瓶颈
# – CPU瓶颈：top、pidstat
# – 内存瓶颈：free、vmstat
# – I/O瓶颈：iostat、iotop
# – 网络瓶颈：netstat、ifstat
# – 数据库瓶颈：TiDB Dashboard、EXPLAIN

# 3. 定位问题
# – 识别性能瓶颈点
# – 分析问题根因
# – 评估影响范围
# – 制定解决策略

# 4. 执行优化
# – 应用调优方案
# – 监控优化效果
# – 调整优化策略
# – 验证优化结果

# 5. 诊断示例
# 步骤1：收集信息
[root@fgedu.net.cn ~]# top
[root@fgedu.net.cn ~]# iostat -x 1
[root@fgedu.net.cn ~]# netstat -an
[root@fgedu.net.cn ~]# tiup cluster display fgedudb

# 步骤2：分析慢查询
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e ”
SHOW GLOBAL VARIABLES LIKE ‘slow_query%’;
SHOW GLOBAL STATUS LIKE ‘Slow_queries’;
”

# 步骤3：分析执行计划
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e ”
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30 ORDER BY created_at;
”

# 步骤4：分析TiKV性能
[root@fgedu.net.cn ~]# tiup ctl:v7.5.0 tikv –host 192.168.1.10:20160 store

# 步骤5：分析PD调度
[root@fgedu.net.cn ~]# tiup ctl:v7.5.0 pd -u http://192.168.1.10:2379 config show

# 6. 诊断工具
# – TiDB Dashboard：综合诊断
# – Prometheus + Grafana：监控分析
# – tiup cluster display：集群状态
# – tiup ctl：组件诊断
# – MySQL客户端：SQL分析

# 7. 诊断最佳实践
# – 定期进行性能诊断
# – 建立性能基线
# – 记录诊断过程和结果
# – 持续优化系统性能
# – 培训运维人员掌握诊断技能

3.3 调优工具使用

3.3.1 TiDB Dashboard

# TiDB Dashboard使用

# 1. 访问方式
# – 通过TiDB节点访问：http://tidb-host:2379/dashboard
# – 通过反向代理访问：配置Nginx代理

# 2. 主要功能
# – 集群信息：查看集群状态和配置
# – SQL分析：分析慢查询、执行计划
# – 性能分析：分析系统和SQL性能
# – 监控面板：查看各种监控指标
# – 日志查询：查询组件日志
# – 集群管理：执行集群操作

# 3. SQL分析
# – 慢查询分析：查看慢查询语句
# – 执行计划分析：分析SQL执行计划
# – 统计信息：查看表统计信息
# – 索引使用：分析索引使用情况

# 4. 性能分析
# – 系统负载：查看CPU、内存、I/O使用情况
# – 热点分析：分析热点区域
# – 事务分析：分析事务执行情况
# – 连接分析：分析连接状态

# 5. 监控面板
# – 集群概览：整体集群状态
# – TiDB面板：TiDB节点状态
# – TiKV面板：TiKV节点状态
# – PD面板：PD节点状态
# – 性能面板：性能指标

# 6. 使用示例
# 步骤1：访问TiDB Dashboard
# 打开浏览器，访问 http://192.168.1.13:2379/dashboard

# 步骤2：查看慢查询
# 点击”SQL分析” → “慢查询”，查看慢查询语句

# 步骤3：分析执行计划
# 点击慢查询语句，查看执行计划

# 步骤4：查看系统监控
# 点击”监控” → “集群概览”，查看系统状态

# 步骤5：查看热点区域
# 点击”性能分析” → “热点分析”，查看热点区域

# 7. 最佳实践
# – 定期查看TiDB Dashboard
# – 设置合理的慢查询阈值
# – 关注系统负载和热点区域
# – 及时处理异常情况
# – 记录重要的性能指标

3.3.2 Prometheus与Grafana

# Prometheus与Grafana使用

# 1. 部署方式
# – 随TiDB集群一起部署
# – 独立部署

# 2. 访问方式
# – Prometheus：http://monitor-host:9090
# – Grafana：http://monitor-host:3000

# 3. Prometheus配置
# – 配置文件：/tidb/deploy/monitor-9090/prometheus.yml
# – scrape_interval：采集间隔
# – scrape_configs：采集配置

# 4. Grafana配置
# – 数据源：添加Prometheus数据源
# – 仪表盘：导入TiDB官方仪表盘
# – 告警：配置告警规则

# 5. 主要仪表盘
# – TiDB Overview：TiDB概览
# – TiKV Overview：TiKV概览
# – PD Overview：PD概览
# – Cluster Overview：集群概览
# – SQL Overview：SQL概览

# 6. 常用查询
# – TiDB QPS：sum(rate(tidb_server_query_total[5m])) by (instance)
# – TiKV I/O：sum(rate(tikv_engine_write_bytes_total[5m])) by (instance)
# – PD Leader：sum(pd_server_is_leader) by (instance)
# – 慢查询数：sum(rate(tidb_server_slow_query_total[5m])) by (instance)

# 7. 告警配置
# – 配置文件：/tidb/deploy/alertmanager-9093/alertmanager.yml
# – 告警规则：/tidb/deploy/monitor-9090/rules/
# – 告警渠道：邮件、Slack、Webhook等

# 8. 使用示例
# 步骤1：访问Grafana
# 打开浏览器，访问 http://192.168.1.20:3000

# 步骤2：查看集群概览
# 选择”Dashboards” → “TiDB Overview”

# 步骤3：查看TiKV性能
# 选择”Dashboards” → “TiKV Overview”

# 步骤4：查看SQL性能
# 选择”Dashboards” → “SQL Overview”

# 步骤5：配置告警
# 选择”Alerting” → “Alert rules”

# 9. 最佳实践
# – 定期查看监控面板
# – 设置合理的告警阈值
# – 关注关键性能指标
# – 建立监控基线
# – 及时处理告警信息

风哥提示：性能调优工具是诊断和解决性能问题的重要手段。熟练使用TiDB Dashboard、Prometheus、Grafana等工具，可以快速定位性能瓶颈，提高调优效率。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 性能调优案例

# 性能调优案例：电商平台TiDB集群

# 场景：电商平台，高峰期QPS 5000，响应时间要求<100ms # 1. 问题描述 # - 高峰期响应时间超过200ms # - 部分SQL执行时间超过1秒 # - TiKV节点CPU使用率超过80% # 2. 诊断过程 # - 步骤1：查看监控面板 # - TiDB QPS：5000 # - TiKV CPU：85% # - 慢查询数：100/分钟 # - 步骤2：分析慢查询 # - 发现多个全表扫描的SQL # - 发现缺少索引的查询 # - 步骤3：分析TiKV性能 # - 发现热点区域 # - 发现I/O瓶颈 # 3. 调优方案 # - SQL优化： # - 为频繁查询的列添加索引 # - 优化SQL语句，避免全表扫描 # - 限制返回数据量 # - TiKV优化： # - 增加TiKV节点数量 # - 调整TiKV配置参数 # - 优化存储配置 # - 系统优化： # - 调整网络参数 # - 优化操作系统配置 # - 增加硬件资源 # 4. 实施过程 # - 步骤1：添加索引 ALTER TABLE fgedudb.fgedu_orders ADD INDEX idx_user_id (user_id); ALTER TABLE fgedudb.fgedu_orders ADD INDEX idx_created_at (created_at); # - 步骤2：优化SQL # 优化前： SELECT * FROM fgedudb.fgedu_orders WHERE user_id = 123; # 优化后： SELECT id, order_no, amount FROM fgedudb.fgedu_orders WHERE user_id = 123; # - 步骤3：调整TiKV配置 # 修改tikv.toml [storage] io-threads = 8 scheduler-worker-pool-size = 16 # - 步骤4：增加TiKV节点 [root@fgedu.net.cn ~]# tiup cluster scale-out fgedudb scale-out.yaml # 5. 调优效果 # - 响应时间：从200ms降至50ms # - 慢查询数：从100/分钟降至10/分钟 # - TiKV CPU：从85%降至40% # - QPS：支持7000（提升40%） # 6. 经验总结 # - 索引优化是最有效的调优手段 # - 合理的SQL语句设计至关重要 # - 适当的硬件资源配置是基础 # - 定期监控和调优是必要的 # - 调优需要持续进行 # 7. 最佳实践 # - 建立SQL审核机制 # - 定期进行性能测试 # - 监控慢查询并及时优化 # - 合理规划硬件资源 # - 持续优化系统配置

4.2 性能诊断案例

# 性能诊断案例：金融系统TiDB集群

# 场景：金融系统，交易高峰期出现性能下降

# 1. 问题描述
# – 交易响应时间突然增加
# – 部分交易失败
# – 系统报错：”Transaction too large”

# 2. 诊断过程
# – 步骤1：查看监控面板
# – TiDB内存使用率：90%
# – 事务大小：超过限制
# – 慢查询数：增加

# – 步骤2：分析TiDB日志
# – 发现”Transaction too large”错误
# – 发现长事务

# – 步骤3：分析SQL语句
# – 发现大查询：SELECT * FROM large_table
# – 发现长事务：未及时提交的事务

# 3. 解决方案
# – 事务优化：
# – 拆分大事务
# – 缩短事务时间
# – 及时提交事务

# – SQL优化：
# – 限制查询结果集大小
# – 使用分页查询
# – 优化大表查询

# – 配置优化：
# – 调整tidb.toml中的事务大小限制
# – 调整内存配置

# 4. 实施过程
# – 步骤1：优化SQL
# 优化前：
SELECT * FROM fgedudb.fgedu_transactions WHERE user_id = 123;

# 优化后：
SELECT id, amount, status FROM fgedudb.fgedu_transactions WHERE user_id = 123 LIMIT 100;

# – 步骤2：调整配置
# 修改tidb.toml
[txn]
txn-total-size-limit = “100MB”

# – 步骤3：监控事务
# 在应用中添加事务监控

# 5. 解决效果
# – 响应时间：恢复正常
# – 交易失败率：0
# – 内存使用率：60%
# – 系统稳定性：恢复正常

# 6. 经验总结
# – 大事务是性能杀手
# – 及时提交事务很重要
# – 限制查询结果集大小
# – 合理配置事务参数
# – 监控事务状态

# 7. 最佳实践
# – 避免大事务
# – 及时提交事务
# – 限制查询结果集
# – 监控事务大小
# – 定期优化SQL

4.3 优化效果案例

# 优化效果案例：制造业ERP系统

# 场景：制造业ERP系统，报表查询性能差

# 1. 问题描述
# – 报表查询时间超过30秒
# – 系统在报表查询时响应缓慢
# – 影响其他业务操作

# 2. 诊断过程
# – 步骤1：分析SQL语句
# – 发现复杂的JOIN查询
# – 发现缺少索引
# – 发现全表扫描

# – 步骤2：分析执行计划
# – 扫描行数：1000万
# – 执行时间：35秒
# – 临时表使用：大量

# – 步骤3：分析TiKV性能
# – I/O使用率：90%
# – 网络流量：高

# 3. 优化方案
# – 索引优化：
# – 为JOIN列添加索引
# – 为WHERE条件列添加索引

# – SQL优化：
# – 重写复杂JOIN
# – 使用子查询优化
# – 限制查询范围

# – 存储优化：
# – 增加TiFlash节点
# – 启用列存索引

# 4. 实施过程
# – 步骤1：添加索引
ALTER TABLE fgedudb.fgedu_orders ADD INDEX idx_product_id (product_id);
ALTER TABLE fgedudb.fgedu_products ADD INDEX idx_category_id (category_id);

# – 步骤2：优化SQL
# 优化前：
SELECT p.category_id, COUNT(*) as order_count, SUM(o.amount) as total_amount
FROM fgedudb.fgedu_orders o
JOIN fgedudb.fgedu_products p ON o.product_id = p.id
WHERE o.created_at >= ‘2024-01-01’
GROUP BY p.category_id;

# 优化后：
SELECT p.category_id, COUNT(*) as order_count, SUM(o.amount) as total_amount
FROM fgedudb.fgedu_orders o
JOIN fgedudb.fgedu_products p ON o.product_id = p.id
WHERE o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-01-31’
GROUP BY p.category_id;

# – 步骤3：添加TiFlash节点
[root@fgedu.net.cn ~]# tiup cluster scale-out fgedudb tiflash-scale-out.yaml

# 5. 优化效果
# – 查询时间：从35秒降至3秒
# – 系统响应：恢复正常
# – I/O使用率：从90%降至30%
# – 其他业务：不受影响

# 6. 经验总结
# – 索引优化显著提升查询性能
# – SQL语句优化减少执行时间
# – TiFlash适合分析型查询
# – 合理的数据范围限制很重要
# – 定期优化是必要的

# 7. 最佳实践
# – 为分析型查询使用TiFlash
# – 合理设计索引
# – 优化SQL语句
# – 限制查询范围
# – 定期进行性能测试

风哥提示：性能调优需要综合考虑多个因素，包括SQL语句、索引设计、系统配置、硬件资源等。通过持续的监控和优化，可以显著提升TiDB集群的性能。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 性能调优最佳实践

索引设计：为频繁查询的列创建索引，避免过多索引
SQL优化：优化SQL语句，避免全表扫描，限制返回数据量
配置调优：根据硬件和业务需求调整配置参数
硬件选型：选择合适的硬件，特别是存储和网络
系统调优：优化操作系统参数，提高系统性能
监控与告警：建立完善的监控体系，及时发现性能问题
定期优化：定期进行性能评估和优化
容量规划：合理规划容量，预留足够的冗余
备份与恢复：确保备份不影响系统性能
培训与知识共享：提高团队的性能调优能力

5.2 常见性能问题与解决方案

# 常见性能问题与解决方案

# 问题1：慢查询
# 原因：
# – 缺少索引
# – SQL语句不合理
# – 数据量过大
# – 统计信息过时
# 解决方案：
# – 添加合适的索引
# – 优化SQL语句
# – 分区表
# – 定期执行ANALYZE TABLE

# 问题2：高CPU使用率
# 原因：
# – SQL执行密集
# – 压缩/解压操作频繁
# – 系统负载高
# 解决方案：
# – 优化SQL语句
# – 调整压缩配置
# – 增加CPU资源
# – 负载均衡

# 问题3：高I/O使用率
# 原因：
# – 磁盘读写频繁
# – 存储性能不足
# – 数据量过大
# 解决方案：
# – 使用SSD存储
# – 优化SQL减少I/O
# – 增加存储资源
# – 数据归档

# 问题4：内存不足
# 原因：
# – 内存配置不足
# – 大查询
# – 内存泄漏
# 解决方案：
# – 增加内存资源
# – 优化大查询
# – 调整内存参数
# – 监控内存使用

# 问题5：网络瓶颈
# 原因：
# – 网络带宽不足
# – 网络延迟高
# – 连接数过多
# 解决方案：
# – 增加网络带宽
# – 优化网络配置
# – 限制连接数
# – 使用连接池

# 问题6：事务冲突
# 原因：
# – 并发事务多
# – 锁竞争激烈
# – 长事务
# 解决方案：
# – 减少事务大小
# – 缩短事务时间
# – 使用乐观锁
# – 优化事务逻辑

# 问题7：热点区域
# 原因：
# – 数据访问集中
# – 索引设计不合理
# – 业务逻辑问题
# 解决方案：
# – 分散热点数据
# – 优化索引设计
# – 业务逻辑调整
# – 使用TiKV热点调度

# 问题8：配置不合理
# 原因：
# – 参数设置不当
# – 配置与硬件不匹配
# – 配置与业务不匹配
# 解决方案：
# – 根据硬件调整配置
# – 根据业务调整配置
# – 参考官方最佳实践
# – 测试配置效果

# 问题9：版本问题
# 原因：
# – 版本过旧
# – 存在已知bug
# – 缺少新特性
# 解决方案：
# – 升级到最新版本
# – 应用补丁
# – 利用新特性优化

# 问题10：监控不足
# 原因：
# – 监控覆盖不全面
# – 告警阈值不合理
# – 监控工具使用不当
# 解决方案：
# – 建立全面的监控体系
# – 配置合理的告警阈值
# – 熟练使用监控工具
# – 定期分析监控数据

5.3 性能调优检查清单

#!/bin/bash
# tidb-performance-checklist.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# TiDB性能调优检查清单

echo “=== TiDB性能调优检查清单 ===”

# 1. 硬件检查
echo “[ ] CPU资源是否充足？”
echo “[ ] 内存资源是否充足？”
echo “[ ] 存储性能是否满足要求？”
echo “[ ] 网络带宽是否充足？”
echo “[ ] 硬件监控是否到位？”

# 2. 系统检查
echo “[ ] 操作系统版本是否最新？”
echo “[ ] 系统参数是否优化？”
echo “[ ] 网络参数是否优化？”
echo “[ ] 文件系统是否优化？”
echo “[ ] 系统负载是否正常？”

# 3. 数据库配置检查
echo “[ ] TiDB配置是否优化？”
echo “[ ] TiKV配置是否优化？”
echo “[ ] PD配置是否优化？”
echo “[ ] 配置与硬件是否匹配？”
echo “[ ] 配置与业务是否匹配？”

# 4. 索引检查
echo “[ ] 高频查询是否有索引？”
echo “[ ] 索引是否合理？”
echo “[ ] 是否存在冗余索引？”
echo “[ ] 索引统计信息是否更新？”
echo “[ ] 索引使用情况是否监控？”

# 5. SQL检查
echo “[ ] 慢查询是否监控？”
echo “[ ] SQL语句是否优化？”
echo “[ ] 是否存在全表扫描？”
echo “[ ] 是否存在大查询？”
echo “[ ] SQL执行计划是否合理？”

# 6. 事务检查
echo “[ ] 是否存在长事务？”
echo “[ ] 事务大小是否合理？”
echo “[ ] 事务冲突是否频繁？”
echo “[ ] 事务隔离级别是否合适？”
echo “[ ] 事务超时设置是否合理？”

# 7. 监控检查
echo “[ ] 监控系统是否部署？”
echo “[ ] 监控指标是否全面？”
echo “[ ] 告警阈值是否合理？”
echo “[ ] 告警通知是否及时？”
echo “[ ] 监控数据是否分析？”

# 8. 备份检查
echo “[ ] 备份是否影响性能？”
echo “[ ] 备份策略是否合理？”
echo “[ ] 备份存储是否充足？”
echo “[ ] 备份恢复测试是否执行？”
echo “[ ] 备份监控是否到位？”

# 9. 容量检查
echo “[ ] 存储空间是否充足？”
echo “[ ] 内存使用是否合理？”
echo “[ ] 连接数是否在限制范围内？”
echo “[ ] QPS/TPS是否在预期范围内？”
echo “[ ] 容量规划是否更新？”

# 10. 调优检查
echo “[ ] 性能基准是否建立？”
echo “[ ] 性能问题是否定期分析？”
echo “[ ] 调优措施是否实施？”
echo “[ ] 调优效果是否验证？”
echo “[ ] 调优文档是否完善？”

echo “=== 检查完成 ===”

# 执行检查示例
# 检查系统负载
[root@fgedu.net.cn ~]# top

# 检查I/O性能
[root@fgedu.net.cn ~]# iostat -x 1

# 检查慢查询
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e “SHOW GLOBAL STATUS LIKE ‘Slow_queries’;”

# 检查索引使用
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e “SHOW INDEX FROM fgedudb.fgedu_users;”

# 检查TiKV状态
[root@fgedu.net.cn ~]# tiup ctl:v7.5.0 tikv –host 192.168.1.10:20160 store

# 检查PD状态
[root@fgedu.net.cn ~]# tiup ctl:v7.5.0 pd -u http://192.168.1.10:2379 member

# 检查TiDB状态
[root@fgedu.net.cn ~]# tiup cluster status fgedudb

# 检查监控数据
[root@fgedu.net.cn ~]# curl -s http://192.168.1.20:9090/api/v1/query?query=tidb_server_query_total | jq

# 检查SQL执行计划
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e “EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30;”

# 检查事务状态
[root@fgedu.net.cn ~]# mysql -h192.168.1.13 -P4000 -u root -p’root123′ -e “SHOW GLOBAL STATUS LIKE ‘Threads_running’;”

# 检查容量使用
[root@fgedu.net.cn ~]# df -h
[root@fgedu.net.cn ~]# free -h

# 检查网络状态
[root@fgedu.net.cn ~]# netstat -an | grep ESTABLISHED | wc -l
[root@fgedu.net.cn ~]# iftop -t -s 10

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html