使用MySQL时CPU在95%+，QPS突然增到2万+，面对MySQL的宕机风险，该如何处理？
应急方法
第一：先限制Innodb的并发处理。如果innodb_thread_concurrency = 0 可以先改成 16或64 看机器压力，如果非常大，先改成16让机器的压力下来，然后慢慢增大，适应自已的业务。
set global innodb_thread_concurrency=16;
第二：对于连接数已经超过600的情况，可以适当的限制一下连接数，宁可让前端报一下错，也别让DB挂。只要DB活着总是可以用来加载一下数据，慢慢的DB压力也会降下来的。限制单用户连接数在300以下。
set global max_user_connections=300;
关闭 innodb_stats_on_metadata 防止对读取information_schema时造成大量读取磁盘进行信息统计 （有些监控程序从这里抓取数据，会终止）
set global innodb_stats_on_metadata=0;
应急处置完毕后进行RootCause分析：
思路：
1、确定高负载的类型 htop，dstat命令看负载高是CPU还是IO
2、监控具体的sql语句，是insert update 还是 delete导致高负载
3、检查mysql慢日志
打开慢查询方法：vi my.cnf，在[mysqld]如下几行:
log_slow_queries = /data/slow.log #慢查询日志路径
long_query_time = 1 #记录SQL查询超过1s的语句
log-queries-not-using-indexes = 1 #记录没有使用索引的sql
4、检查硬件问题
dstat：看具体哪个用户哪个进程占用了相关系统资源，当前CPU、内存谁在使用
# dstat -l -m -r -c --top-io --top-mem --top-cpu
Htop：htop是top的增强版，更直观
tcpdump：抓取mysql包分析，一般抓3306端口的数据
tcpdump -i eth0 -A -s 3000 port 3306 > ~/sql.log
然后使用awk，sort，wc 等命令进行分析
或用
# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e 'while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {if (defined $q) { print "$qn"; }$q=$_;} else {$_ =~ s/^[ t]+//; $q.=" $_";}}'
就可以看出最繁忙的sql语句
strace 或 pstack 查看系统调用是否有问题，进程是否堵塞，是否有Broken pipe
# strace -p 26578
# pstack 26356
分析mysql慢日志，查看哪些sql语句最耗时
# pt-query-digest slow.logs
查看系统到底在干什么
show full processlist;
检查mysql配置参数是否有问题，引起大量的IO或者高CPU操作
innodb_flush_log_at_trx_commit、innodb_buffer_pool_size 、key_buffer_size 等重要参数
show variables like '%innodb%';
查看当前事务，内存使用情况
show engine innodb status \G
最后通过zabbix或者cacti等监控来查看IO、CPU、MEMORY、磁盘等是否有异常
利用 performance_schema 排查 qps 过高过程记录
Performance Schema System Variables
Performance Schema Startup Configuration
Performance Schema 是用于在低级别监视 MySQL 服务器执行的功能，Performance Schema 具有以下特点。Performance Schema 提供了一种在运行时检查服务器内部执行情况的方法。
- 它是使用
PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的 PERFORMANCE_SCHEMA主要关注性能数据。这与INFORMATION_SCHEMA不同，后者用于元数据检查。Performance Schema监视服务器事件。- “事件” 是服务器执行的任何需要时间并且已被检测以便可以收集计时信息的事情。
- 一般来说，事件可以是函数调用、等待操作系统、SQL 语句执行的一个阶段（例如解析或排序）或者整个语句或语句组。
- 事件收集提供对有关服务器和多个存储引擎的同步调用（例如互斥锁）、文件和表 I/O、表锁等信息的访问。
Performance Schema事件不同于写入服务器二进制日志（binlog）的事件（描述数据修改）和事件调度程序事件（这是一种存储程序）。Performance Schema事件特定于 MySQL 服务器的给定实例。performance_schema表被视为服务器本地表，对它们的更改不会复制或写入二进制日志。- 提供当前事件以及事件历史记录和摘要。这能够确定仪表活动执行的次数以及所花费的时间。
- 事件信息可用于显示特定线程的活动，或与特定对象（例如互斥锁或文件）关联的活动。
PERFORMANCE_SCHEMA存储引擎使用服务器源代码中的 “检测点” 收集事件数据。- 收集的事件存储在
performance_schema数据库的表中。 - 这些表可以像其他表一样使用
SELECT语句进行查询。 - 可以通过 SQL 语句更新
performance_schema数据库中的表来动态修改Performance Schema配置。 - 配置更改会立即影响数据收集。
Performance Schema中的表是内存表，不使用持久磁盘存储。- 这些内容在服务器启动时开始重新填充，并在服务器关闭时丢弃。
- MySQL 支持的所有平台上都可以进行监控。
- 可能存在一些限制： 计时器的类型可能因平台而异。
- 适用于存储引擎的工具可能不适用于所有存储引擎。
- 每个第三方发动机的仪表由发动机维护人员负责。
- 数据采集是通过修改服务器源码添加
instrumentation来实现的。 - 与复制或事件调度程序等其他功能不同，没有与性能架构关联的单独线程。
INFORMATION_SCHEMA 提供对数据库元数据、有关 MySQL 服务器的信息（例如数据库或表的名称、列的数据类型或访问权限）的访问。有时用于此信息的其他术语是数据字典和系统目录
information_schema是每个 MySQL 实例中的一个数据库，用于存储有关 MySQL 服务器维护的所有其他数据库的信息。information_schema数据库包含多个只读表。- 它们实际上是视图，而不是基表，因此没有与它们关联的文件，并且您无法在它们上设置触发器。
- 此外，没有具有该名称的数据库目录。
- 虽然您可以使用
USE语句选择information_schema作为默认数据库，但您只能读取表的内容，而不能对其执行INSERT、UPDATE或DELETE操作。
确认是否开启 performance schema
--performance-schema 参数，官方默认值是 ON ，可以通过 MySQL 内查看
- 查看 MySQL 版本
select version();
- 查看是否开启了
performance schema
SHOW VARIABLES LIKE 'perf%';
通过 performance_schema 统计 qps 情况
查看当前 performance_schema 记录了多少 sql 语句，使用
NOT IN来排除 MySQL 的系统库
SELECT COUNT(DIGEST_TEXT)
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('performance_schema', 'information_schema', 'mysql')
ORDER BY SUM_TIMER_WAIT DESC;
可以通过 limit 的方式来看使用次数最多的 sql
SELECT
ROUND(SUM_TIMER_WAIT / (SELECT SUM(SUM_TIMER_WAIT) FROM performance_schema.events_statements_summary_by_digest), 2) AS latency_percentage,
COUNT_STAR AS execution_count,
SUM_TIMER_WAIT / COUNT_STAR AS avg_latency,
SCHEMA_NAME,
DIGEST_TEXT,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('performance_schema', 'information_schema', 'mysql')
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
以下内容从 chatgpt 复制的
SUM_TIMER_WAIT：这是 Performance Schema 的一个列，表示每个 SQL 语句在执行过程中所花费的总时间（以纳秒为单位）。(SELECT SUM(SUM_TIMER_WAIT) FROM performance_schema.events_statements_summary_by_digest)：这是一个子查询，用于计算所有SQL语句的总执行时间，即所有SUM_TIMER_WAIT的总和。
ROUND(SUM_TIMER_WAIT / (SELECT SUM(SUM_TIMER_WAIT) FROM
performance_schema.events_statements_summary_by_digest), 2) AS latency_percentage：这是一个计算字段，用于计算每个 SQL 语句的执行时间在所有 SQL 语句中所占的百分比，然后使用 ROUND 函数将百分比保留两位小数。
COUNT_STAR AS execution_count：这是 Performance Schema 的一个列，表示每个 SQL 语句的执行次数。SUM_TIMER_WAIT / COUNT_STAR AS avg_latency：这是一个计算字段，用于计算每个 SQL 语句的平均执行时间，即将SUM_TIMER_WAIT总和除以COUNT_STAR（执行次数）。SCHEMA_NAME：这是 Performance Schema的一个列，表示每个SQL语句所属的数据库名称。DIGEST_TEXT：这是 Performance Schema 的一个列，表示每个 SQL 语句的具体内容。LAST_SEEN：这是 Performance Schema 的一个列，表示每个 SQL 语句最后执行的时间。FROM performance_schema.events_statements_summary_by_digest：这是指定查询的来源表，即 Performance Schema 中的events_statements_summary_by_digest表。该表存储了关于执行过的 SQL 语句的摘要信息。WHERE SCHEMA_NAME NOT IN ('performance_schema', 'information_schema', 'mysql')：这是一个条件子句，用于排除一些系统数据库，只统计自定义数据库的SQL语句。ORDER BY SUM_TIMER_WAIT DESC：这是将结果按照SUM_TIMER_WAIT（总执行时间）从高到低进行排序，这样能够让我们看到执行时间最长的SQL语句排在前面。
综上所述，这个查询通过 Performance Schema 提供的信息，计算和展示了每个 SQL 语句的执行次数、总执行时间、平均执行时间以及其在所有 SQL 语句中的执行时间占比。这些信息对于分析数据库性能和优化查询是非常有用的。
扫一扫
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
