# 启用Performance Schema
SET GLOBAL performance_schema = ‘ON’;

# 查看Performance Schema状态
SHOW VARIABLES LIKE ‘performance_schema’;

+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| performance_schema | ON |
+——————–+——-+

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

# 查看慢查询日志配置
SHOW VARIABLES LIKE ‘%slow%’;

# 查看sys模式是否安装
SHOW DATABASES LIKE ‘sys’;

# 如果未安装，执行安装脚本
# mysql -u root -p < /usr/share/mysql/sys_schema.sql

# 收集语句性能数据
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM
performance_schema.events_statements_summary_by_digest
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

# 收集等待事件数据
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT
FROM
performance_schema.events_waits_summary_global_by_event_name
WHERE
SUM_TIMER_WAIT > 0
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

# 收集慢查询数据
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile ORDER BY avg_latency DESC LIMIT 10;

# 收集未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE schema_name = ‘fgedudb’;

# 收集内存使用情况
SELECT * FROM sys.memory_by_host_by_current_bytes ORDER BY current_bytes DESC;

# 分析慢查询日志
# mysqldumpslow -s t /mysql/data/fgedu-slow.log

# 使用pt-query-digest分析慢查询日志
# pt-query-digest /mysql/data/fgedu-slow.log

# 分析执行时间最长的SQL语句
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 10;

# 分析使用全表扫描的SQL语句
SELECT
query,
db,
exec_count
FROM
sys.statements_with_full_table_scans
ORDER BY
exec_count DESC
LIMIT 10;

# 分析内存使用情况
SELECT
host,
current_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

# 分析I/O使用情况
SELECT
file_name,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

# 分析连接数情况
SELECT
user,
host,
current_connections
FROM
sys.user_summary
ORDER BY
current_connections DESC;

# 分析线程状态
SELECT
thread_id,
user,
host,
command,
time,
state,
info
FROM
performance_schema.threads
WHERE
PROCESSLIST_ID IS NOT NULL
ORDER BY
time DESC
LIMIT 20;

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

# 输出文件
OUTPUT_FILE=”/mysql/monitor/performance_$(date +”%Y%m%d_%H%M%S”).txt”

# 执行查询
mysql -u root -p”password” -e ”
— 执行时间最长的SQL语句
SELECT ‘=== 执行时间最长的SQL语句 ===’;
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 10;

— 使用全表扫描的SQL语句
SELECT ‘\n=== 使用全表扫描的SQL语句 ===’;
SELECT
query,
db,
exec_count
FROM
sys.statements_with_full_table_scans
ORDER BY
exec_count DESC
LIMIT 10;

— 未使用的索引
SELECT ‘\n=== 未使用的索引 ===’;
SELECT
table_schema,
table_name,
index_name
FROM
sys.schema_unused_indexes
WHERE
table_schema = ‘fgedudb’;

— 内存使用情况
SELECT ‘\n=== 内存使用情况 ===’;
SELECT
host,
current_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

— I/O使用情况
SELECT ‘\n=== I/O使用情况 ===’;
SELECT
file_name,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

— 连接数情况
SELECT ‘\n=== 连接数情况 ===’;
SELECT
user,
host,
current_connections
FROM
sys.user_summary
ORDER BY
current_connections DESC;

— 锁等待情况
SELECT ‘\n=== 锁等待情况 ===’;
SELECT
waiting_trx_id,
waiting_query,
blocking_trx_id,
blocking_query,
wait_age
FROM
sys.innodb_lock_waits
ORDER BY
wait_age DESC
LIMIT 5;
” > $OUTPUT_FILE

# 压缩输出文件
gzip $OUTPUT_FILE

# 删除7天前的文件
find /mysql/monitor -name “performance_*.txt.gz” -mtime +7 -delete

# 步骤1：查看执行时间最长的SQL语句
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 5;

# 步骤2：分析SQL语句执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE age > 30;

# 步骤3：查看索引使用情况
SELECT
table_schema,
table_name,
index_name,
index_columns
FROM
sys.schema_unused_indexes
WHERE
table_schema = ‘fgedudb’;

# 步骤4：查看资源使用情况
SELECT
host,
current_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

# 步骤5：查看I/O使用情况
SELECT
file_name,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

# 安装MySQL Exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64

# 创建MySQL用户
mysql -u root -p -e “CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘password’ WITH MAX_USER_CONNECTIONS 3;”
mysql -u root -p -e “GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;”

# 创建配置文件
cat > .my.cnf << EOF [client] user=exporter password=password host=localhost EOF # 启动MySQL Exporter ./mysqld_exporter --config.my-cnf=.my.cnf # 配置Prometheus cat >> /etc/prometheus/prometheus.yml << EOF - job_name: 'mysql' static_configs: - targets: ['localhost:9104'] EOF # 重启Prometheus systemctl restart prometheus # 配置Grafana # 在Grafana中导入MySQL监控模板（ID: 7362）