1
2
3

SHOW VARIABLES LIKE '%slow%';
SHOW STATUS LIKE '%slow%';
SHOW VARIABLES LIKE 'long_query_time';

1
2
3
4
5
6
7

# /etc/my.cnf
[mysqld]
slow_query_log = on
slow_query_log_file = /data/f/mysql_slow_cw.log   # Linux 用这个
# slow_query_log_file = <WAMP_INSTALL_DIR>/bin/mysql/mysql5.5.16/data/show-slow.log   # Windows 必须绝对路径
long_query_time = 2                                 # 超过 2 秒算慢
log-queries-not-using-indexes                       # 没走索引的也记

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 访问最多的 10 条慢查询
mysqldumpslow -s c -t 10 /var/lib/mysql/huixuli2-slow.log

# 查询时间最慢的 3 条
mysqldumpslow -s t -t 3 /var/lib/mysql/huixuli2-slow.log

# 按时间排序前 10 条含 left join 的
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log

# 扫描行数最多的
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log

1
2
3
4
5
6
7

# Debian/Ubuntu
wget http://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest

# 或整套 Percona Toolkit
wget percona.com/get/percona-toolkit.rpm
rpm -ivh percona-toolkit-2.2.13-1.noarch.rpm

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

# 1. 直接分析
pt-query-digest slow.log > slow_report.log

# 2. 最近 12 小时
pt-query-digest --since=12h slow.log > report.log

# 3. 指定时间范围
pt-query-digest slow.log \
  --since '2014-05-17 09:30:00' \
  --until '2014-06-17 10:00:00' > report.log

# 4. 只分析 SELECT
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log

# 5. 只看 root 用户的
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log

# 6. 全表扫描/全 join 的
pt-query-digest --filter \
  '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' \
  slow.log

# 7. 结果存到 review 表（自动建表）
pt-query-digest --user=root --password=*** \
  --review h=localhost,D=test,t=query_review --create-review-table \
  slow.log

# 8. 历史表（带时间窗）
pt-query-digest --user=root --password=*** \
  --review h=localhost,D=test,t=query_history --create-history-table \
  slow.log_20140401

# 9. 通过 tcpdump 抓 MySQL 协议再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

# 10. 分析 binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql

# 11. 分析 general log
pt-query-digest --type=genlog localhost.log

1
2
3
4
5

# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 8G            # 缓冲池总大小
innodb_buffer_pool_instances = 4        # 缓冲池实例数
innodb_buffer_pool_chunk_size = 1024M   # 调整块大小

1

innodb_buffer_pool_size = innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances × N

1
2
3
4
5
6
7

# 配置
innodb_buffer_pool_size = 8G
innodb_buffer_pool_chunk_size = 1024M
innodb_buffer_pool_instances = 4

# 验证（在线改）
SET GLOBAL innodb_buffer_pool_size = 8589934592;  # 8G = 8*1024*1024*1024

1
2
3
4
5
6
7
8

-- 读取请求命中率（目标 ≥ 99%）
SELECT
    100 * (1 - (variable_value / (SELECT variable_value
                                  FROM performance_schema.global_status
                                  WHERE variable_name = 'Innodb_buffer_pool_read_requests')))
    AS hit_ratio
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

1
2
3
4

SHOW STATUS LIKE 'innodb_buffer_pool_read%';
# Innodb_buffer_pool_read_requests: 54329170948
# Innodb_buffer_pool_reads:         4890414994
# 命中率 = 1 - 4890414994/54329170948 ≈ 91.7% （不达标，加大 buffer pool）

1
2
3
4
5
6
7

# Debian/Ubuntu
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

# 验证
sysbench --version
# sysbench 1.0.20

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 默认通过 INSERT INTO 导数；指定 --db-ps-mode=disable 还能用 LOAD DATA LOCAL INFILE 提速 30%
sysbench oltp_read_write \
  --mysql-host=10.0.0.10 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='{{REDACTED}}' \
  --mysql-db=sbtest \
  --table-size=1000000 \
  --tables=10 \
  --threads=32 \
  --events=999999999 \
  --report-interval \
  prepare

1
2
3
4
5
6

sysbench oltp_read_write \
  --mysql-host=10.0.0.10 \
  --mysql-db=sbtest \
  --tables=10 --table-size=1000000 \
  --threads=32 \
  prewarm

1
2
3
4
5
6
7
8

sysbench oltp_read_write \
  --mysql-host=10.0.0.10 \
  --mysql-db=sbtest \
  --tables=10 --table-size=1000000 \
  --threads=64 \
  --time=60 \
  --report-interval=10 \
  run

1
2
3
4
5

sysbench oltp_read_write \
  --mysql-host=10.0.0.10 \
  --mysql-db=sbtest \
  --tables=10 \
  cleanup

1

[ 60s ] thds: 64 tps: 5028.08 qps: 100641.26 (r/w/o: 70457.59/20121.51/10062.16) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SQL statistics:
    queries performed:
        read:                            4276622
        write:                           1221892
        other:                           610946
        total:                           6109460
    transactions:                        305473  (5088.63 per sec.)
    queries:                             6109460 (101772.64 per sec.)

Latency (ms):
         min:                                    5.81
         avg:                                   12.57
         max:                                  228.87
         95th percentile:                      17.32
         sum:                              3840044.28

Threads fairness:
    events (avg/stddev):           4773.0156/30.77
    execution time (avg/stddev):   60.0007/0.01

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# CPU 性能
sysbench cpu --cpu-max-prime=20000 --threads=32 run
# 看 events per second

# 内存吞吐（推荐 1MB 块大小）
sysbench memory --memory-block-size=1M --memory-total-size=100G --num-threads=1 run
# 看 MiB/sec

# 磁盘 IO
sysbench fileio --file-num=1 --file-total-size=10G --file-test-mode=rndrw prepare
sysbench fileio --file-num=1 --file-total-size=10G --file-test-mode=rndrw run
sysbench fileio --file-num=1 --file-total-size=10G --file-test-mode=rndrw cleanup
# 看 IOPS（reads/s + writes/s）和吞吐量（read MiB/s + written MiB/s）

1
2
3
4
5

-- 哪些表被锁住
SHOW OPEN TABLES WHERE In_use > 0;

-- 当前所有 InnoDB 事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 拼接成 KILL 命令，复制出来执行
SELECT
    GROUP_CONCAT(CONCAT('KILL ', trx_mysql_thread_id) SEPARATOR '; ') AS cmd
FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 不 kill 自己的版本
SELECT
    GROUP_CONCAT(CONCAT('KILL IF EXISTS ', id) SEPARATOR '; ') AS kill_commands
FROM information_schema.PROCESSLIST
WHERE HOST LIKE '%example.telus.net%'
  AND id != CONNECTION_ID();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查 sleep 连接的客户端 IP
SELECT substring_index(host, ':', 1) AS client_ip, Command, Time
FROM information_schema.processlist;

-- 批量 kill 指定用户的 sleep 连接
SELECT
    GROUP_CONCAT(CONCAT('kill ', id) SEPARATOR '; ') AS cmd
FROM information_schema.PROCESSLIST
WHERE USER = 'app_user'
  AND command = 'Sleep'
  AND db = 'app_db';

1
2
3

The last packet successfully received from the server was 120,935 milliseconds ago.
The last packet sent successfully to the server was 381,491 milliseconds ago.
is longer than the server configured value of 'wait_timeout'.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看当前超时
SHOW VARIABLES LIKE '%timeout%';

-- 在线调大
SET GLOBAL wait_timeout = 31536000;
SET GLOBAL interactive_timeout = 31536000;

-- 持久化（/etc/my.cnf）
[mysqld]
wait_timeout = 31536000
interactive_timeout = 31536000

1

导出数据库，出现 SELECT /*!40001 SQL_NO_CACHE */ * FROM ，数据库卡死

1
2
3
4
5
6
7

docker exec mysql /usr/bin/mysqldump \
  -u root -p'{{REDACTED}}' \
  --single-transaction \
  --quick \
  hxl_industry_iot_dev \
  --ignore-table=hxl_industry_iot_dev.hxl_location_historydata \
  > backup.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 1. 找出"扫了最多行"的 SQL
SELECT
    digest_text,
    count_star AS exec_count,
    sum_rows_examined AS total_examined,
    sum_rows_sent AS total_sent,
    sum_timer_wait/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_rows_examined DESC
LIMIT 10;

-- 2. 找出"全表扫描次数最多"的表
SELECT object_name, count_read, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE count_read > 0
ORDER BY count_read DESC
LIMIT 10;

1
2
3
4
5
6
7

-- sys.statements_with_full_table_scans
SELECT query, db, exec_count, total_latency
FROM sys.statements_with_full_table_scans
LIMIT 10;

-- sys.schema_redundant_indexes（推荐清理冗余索引）
SELECT * FROM sys.schema_redundant_indexes;

1
2
3

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND created_at > '2024-01-01';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[mysqld]
# 8.0.30+ 用容量单位（替代 innodb_log_file_size）
innodb_redo_log_capacity = 8G

# 8.0+ 自适应刷新（默认 on）
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10

# 8.0+ 多线程刷新
innodb_page_cleaners = 4
innodb_cleaner_lsn_age_factor = high

1
2
3
4
5
6
7
8

-- 创建资源组（绑定 CPU 核）
CREATE RESOURCE GROUP batch_group
    TYPE = USER
    VCPU = 2-3                       -- 绑到 CPU 2、3 号核
    THREAD_PRIORITY = 10;

-- 给会话分配
SET RESOURCE GROUP batch_group;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# 2024+ 推荐用 lua 脚本（自定义场景更灵活）
sysbench \
  --db-driver=mysql \
  --mysql-host=10.0.0.10 \
  --mysql-db=sbtest \
  --oltp-table-size=1000000 \
  --oltp-tables-count=10 \
  --threads=64 \
  --time=120 \
  --report-interval=10 \
  oltp_read_write \
  prepare

# 报告解读：加关注 P99 延迟
# [ 120s ] thds: 64 tps: 5028.08 qps: 100641.26
#          lat (ms,99%): 23.5    ← 99% 延迟（生产更应看这个）