# 性能优化工具实施步骤

1. 评估需求
– 识别性能问题类型
– 确定优化目标
– 评估系统规模

2. 选择工具
– 评估工具功能
– 考虑学习成本
– 测试工具效果

3. 安装配置
– 安装工具软件
– 配置工具参数
– 测试工具功能

4. 工具使用
– 运行工具分析
– 解读工具输出
– 制定优化方案

5. 效果验证
– 实施优化方案
– 验证优化效果
– 调整工具配置

6. 持续使用
– 定期使用工具
– 建立使用规范
– 持续更新工具

# Percona Toolkit使用示例

# 1. 安装Percona Toolkit
wget https://www.percona.com/downloads/percona-toolkit/3.5.1/binary/tarball/percona-toolkit-3.5.1_x86_64.tar.gz
tar -xzf percona-toolkit-3.5.1_x86_64.tar.gz
cd percona-toolkit-3.5.1

# 2. 使用pt-query-digest分析慢查询
pt-query-digest /var/lib/mysql/slow-query.log

# 3. 使用pt-table-checksum检查主从数据一致性
pt-table-checksum –host=master-host –user=root –password=password

# 4. 使用pt-online-schema-change在线修改表结构
pt-online-schema-change –alter=”ADD COLUMN new_column INT” –user=root –password=password D=fgedudb,t=fgedu_users

# MySQLTuner使用示例

# 1. 下载MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

# 2. 运行MySQLTuner
./mysqltuner.pl –user=root –pass=password

# tuning-primer使用示例

# 1. 下载tuning-primer
wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
chmod +x tuning-primer.sh

# 2. 运行tuning-primer
./tuning-primer.sh

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

# 分析慢查询
function analyze_slow_queries() {
echo “分析慢查询…”
pt-query-digest /var/lib/mysql/slow-query.log > slow_query_analysis.log
}

# 检查主从数据一致性
function check_replication_consistency() {
echo “检查主从数据一致性…”
pt-table-checksum –host=master-host –user=root –password=password > replication_check.log
}

# 分析表结构
function analyze_table_structure() {
echo “分析表结构…”
pt-duplicate-key-checker –host=localhost –user=root –password=password –database=fgedudb > table_structure_analysis.log
}

# 分析服务器配置
function analyze_server_config() {
echo “分析服务器配置…”
./mysqltuner.pl –user=root –pass=password > server_config_analysis.log
}

# 主函数
function main() {
analyze_slow_queries
check_replication_consistency
analyze_table_structure
analyze_server_config
}

# 执行主函数
main

# 实战：使用Percona Toolkit分析慢查询

# 1. 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = ‘/var/lib/mysql/slow-query.log’;

# 2. 运行pt-query-digest分析慢查询
pt-query-digest /var/lib/mysql/slow-query.log

# 输出示例：
# # 360 queries total, 10 unique, 0.00 QPS, 0.00x concurrency
# # Attribute total min max avg 95% stddev median
# # ============ ======= ======= ======= ======= ======= ======= =======
# # Exec time 123.45s 0.12s 5.67s 0.34s 1.23s 0.45s 0.23s
# # Lock time 0.12s 0.00s 0.01s 0.00s 0.00s 0.00s 0.00s
# # Rows sent 1000.00 1.00 10.00 2.78 5.00 2.34 2.00
# # Rows examine 1000000.00 1000.00 100000.00 2777.78 5000.00 2345.67 2000.00
# # Query size 1234.00 10.00 50.00 3.43 5.00 2.34 3.00
#
# # Profile
# # Rank Query ID Response time Calls R/Call V/M Item
# # ==== ================== ============= ===== ====== ===== ============
# # 1 0x1234567890ABCDEF 67.89s 100 0.68s 0.99 SELECT fgedu_users
# # 2 0x9876543210FEDCBA 34.56s 200 0.17s 0.98 SELECT fgedu_orders

# 3. 分析结果
# 发现两个主要慢查询，需要优化

# 4. 优化方案
# 添加索引，优化查询语句
ALTER TABLE fgedu_users ADD INDEX idx_email (email);
ALTER TABLE fgedu_orders ADD INDEX idx_user_id (user_id);

# 1. 运行MySQLTuner
./mysqltuner.pl –user=root –pass=password

# 输出示例：
# >> MySQLTuner 1.7.19 – Major Hayden
# >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
# >> Run with ‘–help’ for additional options and output filtering
#
# [–] Skipped version check for MySQLTuner script
# Please enter your MySQL administrative login: root
# Please enter your MySQL administrative password:
# [OK] Currently running supported MySQL version 8.4.0
# [OK] Operating on 64-bit architecture
#
# ——– Storage Engine Statistics ——————————————-
# [–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
# [–] Data in InnoDB tables: 50.0G (Tables: 100)
# [–] Data in MyISAM tables: 10.0G (Tables: 50)
# [–] Data in MEMORY tables: 0B (Tables: 5)
# [!!] Total fragmented tables: 20
#
# ——– Performance Metrics ————————————————-
# [–] Up for: 10d 5h 30m 15s (123M q [139.553 qps], 1.2M conn, TX: 1.2T, RX: 120G)
# [–] Reads / Writes: 70% / 30%
# [–] Binary logging is enabled (GTID mode: OFF)
# [–] Physical Memory : 64.0G
# [–] Max MySQL memory : 48.0G
# [–] Other process memory: 4.0G
# [–] Total buffers: 32.0G global + 2.0M per thread (1000 max threads)
# [–] P_S Max memory usage: 0B
# [–] Galera GCache Max memory usage: 0B
# [OK] Maximum reached memory usage: 34.0G (53.13% of installed RAM)
# [OK] Maximum possible memory usage: 40.0G (62.50% of installed RAM)
# [OK] Overall possible memory usage with other process is compatible with memory available
#
# ——– InnoDB Metrics —————————————————–
# [–] InnoDB is enabled.
# [OK] InnoDB Buffer Pool size vs total MySQL memory: 24.0G (75.0% of total MySQL memory) within 25%-80% recommendation
# [OK] InnoDB Buffer Pool instances: 8
# [!!] InnoDB Buffer Pool Chunk Size (128.0M) exceeds buffer pool size (24.0G) or innodb_buffer_pool_instances (8)
# [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
# [OK] InnoDB Read buffer efficiency: 99.99% (1234567890 hits/ 1234567900 total)
# [OK] InnoDB Write log efficiency: 99.99% (1234567890 hits/ 1234567900 total)
# [OK] InnoDB log waits: 0.00% (0 waits / 1234567890 writes)

# 2. 分析结果
# 发现一些配置问题，需要优化

# 3. 优化方案
# 调整InnoDB Buffer Pool Chunk Size
# 优化表碎片
# 调整其他参数

# 1. 性能优化工具最佳实践

# 工具选择
– 根据问题类型选择合适的工具
– 结合多种工具进行综合分析
– 选择有社区支持的工具

# 工具使用
– 定期使用工具进行健康检查
– 建立工具使用规范
– 记录工具使用结果

# 工具配置
– 配置工具参数以适应系统规模
– 优化工具性能影响
– 集成工具到监控系统

# 2. 常用工具组合

# 慢查询分析
– pt-query-digest + EXPLAIN
– MySQL Workbench + Performance Schema

# 服务器配置优化
– MySQLTuner + tuning-primer
– Percona Monitoring and Management

# 复制监控
– pt-table-checksum + pt-table-sync
– MySQL Enterprise Monitor

# 3. 工具使用技巧

# 批量处理
– 编写脚本批量执行工具
– 建立自动化检查流程

# 结果分析
– 对比工具输出结果
– 分析性能趋势
– 制定优化方案

# 持续优化
– 定期更新工具版本
– 学习工具新特性
– 分享工具使用经验