Loading...
墨滴

eZy

2021/10/26  阅读:29  主题:橙心

故障诊断:MySQL 未启用任何日志,操作源头如何定位?

本文主要篇幅被 performance_schema 配置及使用详细说明占据。

目录


之前的文章末尾,有对本篇主题进行预告:

  • 内部链接:
  • 外部链接:https://mp.weixin.qq.com/s/LXlB1RvO2cexf-zNYrbr7w

今天就让我把这个坑填上。

文章约定

  • performance_schema 简称为 PS。

模拟故障描述

数据库莫名被删,导致业务中断。

处理过程

假如 error log、general log、slow log、binlog 都没有开启,那只能从 performance_schema 下的一些表中尝试找出一些线索:

select t.schema_name,
       t.digest_text,
       t.digest,
       t.count_star,
       t.sum_timer_wait,
       t.sum_errors,
       t.sum_warnings,
       t.first_seen,
       t.last_seen
from performance_schema.events_statements_summary_by_digest t
where t.digest_text like '%drop%';

sys,DROP SCHEMA `demo` ,e7ea4ea23189f40df98432670d1229ca,1,2525100000,0,0,2021-10-21 08:08:28
1 row in set (0.01 sec)

-- ------

select t.user, t.host, t.event_name, t.count_star, t.sum_timer_wait
from performance_schema.events_statements_summary_by_account_by_event_name t
where t.event_name like '%drop%db%'
  and t.sum_timer_wait > 0;
  
root,localhost,statement/sql/drop_db,1,2525100000
1 row in set (0.00 sec)

注解(3W 信息得到确认):

  • performance_schema.events_statements_summary_by_digest:通过 digest_text、count_star、first_seen 三列,得出 2W 信息(即 When & do What)。

  • performance_schema.events_statements_summary_by_account_by_event_name:通过 user、host 及 event_name 确定了 1W 信息(即 Who do that)。

进一步挖掘 PS 视图,再没有找到其他有价值线索。

但我们得到了审计条目中最基本的 3W 信息,即:root@localhost 在 2021-10-21 08:08:28 干了 drop database 操作。可将这些关键线索反馈于相关人员,以进行下一步调查及追责工作。

另外,出现问题时,保留现场很重要,为什么这么说?这就要介绍下 performance_schema 的相关特性了,请看下文。

PS 介绍

特殊性

  • 其下表全部为 performance_schema 引擎
select distinct t.engine
from information_schema.tables t
where t.table_schema = 'performance_schema';

PERFORMANCE_SCHEMA

手动创建的表,不允许指定为 performance_schema 引擎:

create table t_dummy(x int)
engine = performance_schema;

-- 报错
[HY000][1683] Invalid performance_schema usage.
  • 重启清空

PS 非配置相关表,重启后记录会被清空。

故障发生后,尽量不要重启实例,让“案发现场”得以保留。比如类似 drop database 发生后,优先尝试使用逻辑备份进行恢复操作,如果一定要使用物理备份执行恢复操作,务必先对 performance_schema 进行备份。

PS 配置及使用详细说明(重点内容)

如果把 MySQL 比做一辆跑车,那 PS 就是这辆车的仪表盘。当然,MySQL 的性能仪表盘更加复杂。

MySQL 仪表盘的总开关由 performance_schema 参数控制,开启指定为 on,关闭则配置为 off。注意:此参数不可动态修改。

PS 的核心是测量仪 - instrument,鉴于上面的比喻,我还是倾向于叫它探测器,对应汽车零部件中的压力探测器,热量探测器等等。这些探测器分为以下几类:

mysql> select distinct substring_index(name,'/',1) instrument_class from setup_instruments order by 1;
+------------------+
| instrument_class |
+------------------+
| idle             |
| memory           |
| stage            |
| statement        |
| transaction      |
| wait             |
+------------------+
6 rows in set (0.00 sec)
  • idle:空闲类。
  • memory:内存类。
  • stage:处理阶段类,如 sql 语句的 sorting result 阶段。
  • statement:语句类。
  • transaction:事务类。
  • wait:等待类。

参考官方文档:https://dev.mysql.com/doc/refman/5.7/en/performance-schema-instrument-naming.html

探测器的启用与禁用通过 update setup_instruments 表实现:

mysql> desc setup_instruments;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| NAME    | varchar(128)     | NO   |     | NULL    |       |
| ENABLED | enum('YES','NO') | NO   |     | NULL    |       |
| TIMED   | enum('YES','NO') | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

更新 enabled 列可对探测器进行启用/禁用:

mysql> update setup_instruments set enabled='yes' where name='wait/synch/sxlock/innodb/trx_purge_latch';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新过后,大部分探测器会立即生效。

timed 列的更新影响探测器是否计数,可用的计数器单位参见如下表:

mysql> select * from performance_timers order by 1;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2289133004 |                1 |             16 |
| NANOSECOND  |      1000000000 |                1 |          17272 |
| MICROSECOND |         1000000 |                1 |          17110 |
| MILLISECOND |            1034 |                1 |          17260 |
| TICK        |             102 |                1 |           1512 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

如果想修改探测器默认计数单位,通过修改 setup_timers 表实现,且只能针对探测器所属分类进行修改,表内容如下:

mysql> select * from setup_timers order by 1;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
| wait        | CYCLE       |
+-------------+-------------+
5 rows in set2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                  |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1681 | 'performance_schema.setup_timers' is deprecated and will be removed in a future release. |
Warning | 1681 | 'performance_schema.setup_timers' is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

不过这里可以看到 setup_timers 表即将废弃,所以没有特殊需求,不建议修改默认计数器。

默认探测器启用状态一览:

select tmp.*,
       round(tmp.is_enabled_count / tmp.is_total_count * 1001as enabled_pct,
       version()                                                 as mysql_version
from (
         select substring_index(name'/'1)          as class,
                count(*)                               as is_total_count,
                count(if(si.enabled = 'YES'1null)) as is_enabled_count,
                count(if(si.timed = 'YES'1null))   as is_timed_count
         from performance_schema.setup_instruments si
         group by substring_index(name'/'1)
         with rollup
     ) tmp
order by 1 desc;

+-------------+----------------+------------------+----------------+-------------+---------------+
| class       | is_total_count | is_enabled_count | is_timed_count | enabled_pct | mysql_version |
+-------------+----------------+------------------+----------------+-------------+---------------+
| wait        |            321 |               54 |             54 |        16.8 | 5.7.31        |
| transaction |              1 |                0 |              0 |         0.0 | 5.7.31        |
| statement   |            193 |              193 |            193 |       100.0 | 5.7.31        |
| stage       |            129 |                9 |              9 |         7.0 | 5.7.31        |
| memory      |            376 |               70 |              0 |        18.6 | 5.7.31        |
| idle        |              1 |                1 |              1 |       100.0 | 5.7.31        |
| NULL        |           1021 |              327 |            257 |        32.0 | 5.7.31        |
+-------------+----------------+------------------+----------------+-------------+---------------+

在 MySQL 5.7.31 版本中,共计有 1021 个探测器,默认启用 327 个。

注意:开启 performance_schema 势必会有额外的性能开销,具体性能开销占比很早之前 percona 相关的文章内有提到,具体参考链接: https://www.percona.com/blog/2011/04/25/performance-schema-overhead/ 大概在 8% - 25% 区间范围内:

所以,没有特殊要求的情况下,不建议扩大探测器覆盖范围。但也完全没必要把 PS 关掉,毕竟实例类似奔驰中的汽车,如果只顾着踩油门,不关注胎压,发动机温度等指标,那势必造成翻车事故😂。

好了,到这里,我们对探测器有了比较具体的认识,如果回归到比喻(MySQL 实例比做跑车),这里的探测器可以具象成跑车的温度传感器,这个传感器可以启用或禁用,也可以配置探测数据输出单位(摄氏度 or 华氏度)。

当然,数据库有数据库的特殊性,以下内容就不能再沿用跑车的比喻,让我们接着看一下 MySQL 探测器的特殊性。

探测器可以指定探测范围/目标,通过更新以下两张表实现:

mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
20 rows in set (0.00 sec)
  • setup_actors:按 user、host、role 维度指定监控范围,其中 role 为保留字段(MySQL 目前没有 role 特性),enabled 指定是否对目标维度进行监控,history 指定是否保留监控历史。默认策略:对所有进入账号(user + host)进行监控。
  • setup_objects:按 object_schema,object_type,object_name 维度指定监控范围,enabled 指定是否对目标维度进行监控,timed 指定是否计数。默认策略:对系统数据库(mysql、performance_schema、information_schema)不监控,其他对象全部监控,对象类别涵盖:EVENT、FUNCTION、PROCEDURE、TABLE、TRIGGER。

好了,以上内容算是告诉了你探测器配置的详细过程,接下来我们来看看如何指定探测器收集的数据放到哪儿?这就不得不提到另外一张配置表:setup_consumers

mysql> select * from setup_consumers order by 1;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| statements_digest                | YES     |
| thread_instrumentation           | YES     |
+----------------------------------+---------+
15 rows in set (0.01 sec)

以上 sql 输出结果中的前 12 条信息 name 命名规则为:events_探测器类别_current/history/history_long,探测器类别覆盖到了:stages、statements、transactions、waits 四类。其中 global_instrumentation 为 1 级参数,thread_instrumentation、statements_digest 为 2 级参数,events_探测器类别_current 为 3 级参数,events_探测器类别_history/history_long 为 4 级参数。高级别依赖于低级别参数配置,如:要监控 stages 类对应 current 和 history/history_long 信息,必须确保参数从低到高都是 enabled 状态:

-- 1 级参数
update setup_consumers set enabled='YES'
where name in('global_instrumentation');
-- 2 级参数
update setup_consumers set enabled='YES'
where name in('thread_instrumentation');
-- 3 级参数
update setup_consumers set enabled='YES'
where name in('events_statements_current');
-- 4 级参数
update setup_consumers set enabled='YES'
where name in('events_statements_history','events_statements_history_long');

到这里,参数级别我们也有了了解。接下来我们再来看看 events_探测器类别_【current/history/history_long】后缀说明:

  • current:存储每个线程的当前事件(last event),一个线程对应一行。
  • history:存储每个线程经历过的历史事件,有行数限制,线程断开后相关内容清空
  • history_long:存储每个线程经历过的历史事件,有行数限制,线程断开后相关事件不清空,淘汰策略应该是 FIFO。

参考

  • https://dev.mysql.com/doc/refman/5.7/en/performance-schema-consumer-filtering.html
  • https://dev.mysql.com/doc/refman/5.7/en/performance-schema-event-tables.html

current -> history -> history_long 数据流演示:

-- session A
select sleep(1from mysql.user;


-- session B
select event_id,
       end_event_id,
       event_name,
       sql_text,
       digest,
       digest_text,
       current_schema,
       mysql_errno,
       errors,
       warnings,
       rows_affected,
       rows_sent,
       rows_examined
from performance_schema.events_statements_current
where thread_id <> (select thread_id from performance_schema.threads t where t.processlist_id = connection_id());

545,545,statement/sql/select,select sleep(1from mysql.user,1bdba8b66ad061c90864505e9d09b70c,SELECT `sleep` (?) FROM `mysql` . `user` ,performance_schema,0,0,0,0,15,15


-- session A
执行其他操作


-- session B 重复查询 performance_schema.events_statements_current
得到的结果是 session A 最后执行过的 sql


-- session B 查询
select event_id,
       end_event_id,
       event_name,
       sql_text,
       digest,
       digest_text,
       current_schema,
       mysql_errno,
       errors,
       warnings,
       rows_affected,
       rows_sent,
       rows_examined
from performance_schema.events_statements_history
where thread_id <> (select thread_id from performance_schema.threads t where t.processlist_id = connection_id())
order by event_id desc;

从 performance_schema.events_statements_current 淘汰出的语句进入 performance_schema.events_statements_history。


-- session A 断开连接,断开前记录 processlist_id 及 thread_id
select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               9 |
+-----------------+
1 row in set (0.00 sec)
mysql> select thread_id from performance_schema.threads t where t.processlist_id = 9;
+-----------+
| thread_id |
+-----------+
|        34 |
+-----------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@d877497f1518 ~]#


-- session B 按 thread_id = 34 复查 performance_schema.events_statements_history
-- 无结果返回


-- session B 查询
select event_id,
       end_event_id,
       event_name,
       sql_text,
       digest,
       digest_text,
       current_schema,
       mysql_errno,
       errors,
       warnings,
       rows_affected,
       rows_sent,
       rows_examined
from performance_schema.events_statements_history_long
where thread_id = 34;
-- 有结果返回。

至此,events_xxx_【current/history/history_long】数据流转过程已经很清晰。但我们知道,如果实例 TPS 很高,监控记录不可能一直往表里塞,否则生成的监控数据都可能比业务数据量都要大。所以,PS 对监控表的最大行数都有做限制。current 类表一个线程对应一行记录,记录数量依赖于已连接线程数。那 history/history_long 的最大记录上限如何配置呢?这就要通过相关参数进行指定了,具体参见以下内容:

select variable_name
from performance_schema.global_variables
where variable_name regexp '^performance.*history(_long)*.*'
order by 1;
+----------------------------------------------------------+
| variable_name                                            |
+----------------------------------------------------------+
| performance_schema_events_stages_history_long_size       |
| performance_schema_events_stages_history_size            |
| performance_schema_events_statements_history_long_size   |
| performance_schema_events_statements_history_size        |
| performance_schema_events_transactions_history_long_size |
| performance_schema_events_transactions_history_size      |
| performance_schema_events_waits_history_long_size        |
| performance_schema_events_waits_history_size             |
+----------------------------------------------------------+
8 rows in set (0.00 sec)

其中 history_size 后缀的参数作用于每线程,history_long_size 后缀是对应表最大行数。

performance_schema 其他相关参数分类:

-- max size
| performance_schema_accounts_size                         |
| performance_schema_digests_size                          |
| performance_schema_hosts_size                            |
| performance_schema_users_size                            |
| performance_schema_session_connect_attrs_size            |

-- 其他 max size 参数
| performance_schema_max_cond_classes                      |
| performance_schema_max_cond_instances                    |
| performance_schema_max_digest_length                     |
| performance_schema_max_file_classes                      |
| performance_schema_max_file_handles                      |
| performance_schema_max_file_instances                    |
| performance_schema_max_index_stat                        |
| performance_schema_max_memory_classes                    |
| performance_schema_max_metadata_locks                    |
| performance_schema_max_mutex_classes                     |
| performance_schema_max_mutex_instances                   |
| performance_schema_max_prepared_statements_instances     |
| performance_schema_max_program_instances                 |
| performance_schema_max_rwlock_classes                    |
| performance_schema_max_rwlock_instances                  |
| performance_schema_max_socket_classes                    |
| performance_schema_max_socket_instances                  |
| performance_schema_max_sql_text_length                   |
| performance_schema_max_stage_classes                     |
| performance_schema_max_statement_classes                 |
| performance_schema_max_statement_stack                   |
| performance_schema_max_table_handles                     |
| performance_schema_max_table_instances                   |
| performance_schema_max_table_lock_stat                   |
| performance_schema_max_thread_classes                    |
| performance_schema_max_thread_instances                  |

-- 配置相关表最大行数限制,注意,配置表的行数也不宜太多,否则对性能有一定的影响。
| performance_schema_setup_actors_size                     |
| performance_schema_setup_objects_size                    |

Ok!对 performance_schema 的详细介绍算是告一段落,这就完了吗?并没有,既然 performance_schema 是用来监控实例的,那 performance_schema 自身状态监控在哪里看呢?还真有,请接着往下看:

-- global status
mysql> select * from performance_schema.global_status where variable_name like 'perf%' order by 1;
+-----------------------------------------------+----------------+
| VARIABLE_NAME                                 | VARIABLE_VALUE |
+-----------------------------------------------+----------------+
| Performance_schema_accounts_lost              | 0              |
| Performance_schema_cond_classes_lost          | 0              |
| Performance_schema_cond_instances_lost        | 0              |
| Performance_schema_digest_lost                | 0              |
| Performance_schema_file_classes_lost          | 0              |
| Performance_schema_file_handles_lost          | 0              |
| Performance_schema_file_instances_lost        | 0              |
| Performance_schema_hosts_lost                 | 0              |
| Performance_schema_index_stat_lost            | 0              |
| Performance_schema_locker_lost                | 0              |
| Performance_schema_memory_classes_lost        | 0              |
| Performance_schema_metadata_lock_lost         | 0              |
| Performance_schema_mutex_classes_lost         | 0              |
| Performance_schema_mutex_instances_lost       | 0              |
| Performance_schema_nested_statement_lost      | 0              |
| Performance_schema_prepared_statements_lost   | 0              |
| Performance_schema_program_lost               | 0              |
| Performance_schema_rwlock_classes_lost        | 0              |
| Performance_schema_rwlock_instances_lost      | 0              |
| Performance_schema_session_connect_attrs_lost | 0              |
| Performance_schema_socket_classes_lost        | 0              |
| Performance_schema_socket_instances_lost      | 0              |
| Performance_schema_stage_classes_lost         | 0              |
| Performance_schema_statement_classes_lost     | 0              |
| Performance_schema_table_handles_lost         | 0              |
| Performance_schema_table_instances_lost       | 0              |
| Performance_schema_table_lock_stat_lost       | 0              |
| Performance_schema_thread_classes_lost        | 0              |
| Performance_schema_thread_instances_lost      | 0              |
| Performance_schema_users_lost                 | 0              |
+-----------------------------------------------+----------------+
30 rows in set (0.00 sec)


-- show engine
mysql> show engine performance_schema status;
+--------------------+-------------------------------------------------------------+-----------+
| Type               | Name                                                        | Status    |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | events_waits_current.size                                   | 176       |
| performance_schema | events_waits_current.count                                  | 1536      |
| performance_schema | events_waits_history.size                                   | 176       |
| performance_schema | events_waits_history.count                                  

...

| performance_schema | (current_statements_text_array).size                        | 1024      |
| performance_schema | (current_statements_text_array).memory                      | 2621440   |
| performance_schema | (statements_digest_token_array).count                       | 10000     |
| performance_schema | (statements_digest_token_array).size                        | 1024      |
| performance_schema | (statements_digest_token_array).memory                      | 10240000  |
| performance_schema | performance_schema.memory                                   | 149010616 |
+--------------------+-------------------------------------------------------------+-----------+
229 rows in set (0.01 sec)

参考

  • https://dev.mysql.com/doc/refman/5.7/en/performance-schema-status-monitoring.html
  • https://dev.mysql.com/doc/refman/5.7/en/show-engine.html

global status 部分介绍(官方文档译文,部分修改)

  • PS 相关 global status 提供有关由于内存限制而无法加载或创建探测器的统计信息。这些统计信息的名称有几种形式,可划分为 4 类:
    • performance_schema_xxx_classes_lost:显示对应 xxx 类有多少探测器不能加载。
    • performance_schema_xxx_instances_lost:显示对应 xxx 类有多少实例不能创建(如文件实例、表实例创建)。
    • performance_schema_xxx_handles_lost:显示对应 xxx 类有多少实例不能打开(打开为文件句柄、表句柄)。
    • performance_schema_xxx_lost:显示对应 xxx 事件有多少没被记录或丢失。

例如,如果在服务器源中监测了互斥锁,但服务器无法在运行时为探测器分配内存,则它会增加 performance_schema_mutex_classes_lost。互斥锁仍然作为同步对象运行(即服务器继续正常运行),但不会收集它的性能数据。如果可以分配探测器,则可以将其用于初始化已监测的互斥体实例。对于像全局互斥这样的单例互斥,只有一个实例。其他互斥锁在每个连接或各个缓存和数据缓冲区中的每个页面都有一个实例,因此实例数量随时间变化。增加最大连接数或某些缓冲区的最大大小会增加可能一次分配的最大实例数。如果服务器无法创建给定的监测互斥体实例,会增加performance_schema_mutex_instances_lost。

探测器一经分配就不进行回收,且探测器是能分配尽量分配的原则。例如:当前配置 - performance_schema_max_mutex_classes=200,已占用 150 个 mutex 探测器。有 A 和 B 两个插件,安装 A 插件需要分配 40 个 mutex 探测器,B 需要 20 个。

  • 先安装 A 插件,则新分配 40 个探测器,再卸载 A 插件,之前分配的 40 个探测器不进行回收,重新安装 A 插件,继续使用之前分配的 40 个探测器。
  • 再安装 B 插件,成功分配 10 个探测器,Performance_schema_mutex_classes_lost 加 10,B 插件的部分资源得到监控。

在某些情况下,故意造成探测器不足可能是合适的。例如,如果您不关心文件 I/O 的性能数据,则可以将与文件 I/O 相关的所有 Performance Schema 参数设置为 0 来启动服务器。


show engine status 部分介绍(官方文档译文,部分修改)

  • name 命名规则

    • 未公开为表的内部缓冲区在括号内命名,例如:(pfs_cond_class).size,(pfs_mutex_class).memory。
    • 在 PS 数据库中作为表公开的内部缓冲区以表命名,不带括号,例如:events_waits_history.size, mutex_instances.count。
    • 适用于整个性能模式的值以 performance_schema 前缀开头,例如: performance_schema.memory。
  • 缓冲区属性具有以下含义:

    • size:内部记录的大小,例如表中一行的大小,size 值不能改变。
    • count:是内部记录的数量,例如表中的行数,count 可以使用性能架构配置选项更改值。

对于表 tbl_name.memory = size * count,对于整个性能模式,performance_schema.memory 是使用的所有内存的总和(所有其他 memory 值的总和 )。

在某些情况下,performance_schema 配置参数和 SHOW ENGINE 值之间存在直接关系。例如:events_waits_history_long.count 对应于 performance_schema_events_waits_history_long_size。在其他情况下,这种关系更为复杂。例如:events_waits_history.count 对应于 performance_schema_events_waits_history_size (每个线程的行数)乘以 performance_schema_max_thread_instances (线程数)。


以上篇幅内容较多,其实总结下来,监控 performance_schema 只要关注几个关键指标

  • 确保 PS 是打开状态
select @ps_on_cnt := count(*) cnt
from performance_schema.global_variables
where variable_name = 'performance_schema'
  and variable_value in ('1''on');

select @ps_gt_on_cnt := count(*) cnt
from setup_consumers
where name in ('global_instrumentation''thread_instrumentation')
  and enabled = 'YES';

-- 确保以下 SQL 返回值为 3,否则报警
select @ps_on_cnt + @ps_gt_on_cnt;
  • 确保没有 lost 事件发生:
-- 确保以下 SQL 返回值为 0,否则报警
select count(*)
from (
         select *
         from performance_schema.global_status
         where variable_name like 'perf%') tmp
where tmp.variable_value <> 0;
  • 尽量确保 PS 占用内存在合理的范围内,这里建议是 buffer pool size 的 5%:
show engine performance_schema status;

performance_schema.memory   

PS 与 global status

这两类信息没有直接关系,就算把 performance_schema 完全关闭,global status 依旧采集数据。

好了!至此,performance_schema 使用详细说明算是告一段落,是不是有点晕😵?没关系!下面我将提供一张 performance_schema 相关的思维导图,从更高的视角概括性的再回顾一遍以上提到的内容。

PS 概览思维导图

顺便提一下 PS 伴侣(对比咖啡伴侣):sys schema

如果是真实环境,数据库一般都比较大,执行 drop database 操作需要花一定的时间,此时可以直接查看 sys schema 下的一些视图,drop 操作大概率会在查询 statements_with_runtimes_in_95th_percentile 视图结果中返回。

sys schema 下就是一些视图及函数,视图大部分是对 performance_schema 下性能表的二次封装。

碎碎念(不建议跳过)

对处理故障现场的人

  • 保留案发现场很重要,早年间处理过一起 rm 误操作事故,使用的是 extundelete,此工具在不重启应用 & 操作系统的情况下恢复成功率很高(文件句柄保持打开状态)。

对监控相关人员

  • 某些性能表不能经常性查询,如:buffer pool 相关的性能表,如果 buffer pool size 配置比较大,对相关表的查询将相当慢,MySQL 早期版本中,类似查询还可能导致性能问题。

吐槽

  • MySQL 官方文档没有详细介绍每种 instruments 的相关页面(目前已有 1000+ 个)。

至此,全文结束,负责故障诊断的 DBA 们加油!

预告

学以致用:利用 PS 进行锁阻塞故障诊断。


公众号改变了推送规则,若你还想看到我的文章,请给本文【点赞、在看、分享】三连,新文才会第一时间推送到你的微信中。

原文对应公众号为 2018 年上半年注册,不具备留言功能,如想与作者进一步沟通,可发送邮件至:984513956@qq.com。


点击下方链接关注我,相信每篇文章都能给你带来收获。


往期推荐


作者:eZy90(ixdba/linora)

来源:公众号 - 悟空的数橘窟私房菜(ID:wkDB007)

关于作者:DBA 一枚,09 年开始接触数据库,早年间从事 Oracle DBA 一职,目前专注于开源数据库领域。混过很多家公司,也玩过很多种数据库。

其他说明:不保证全文没有任何错漏之处,如有不妥不吝赐教。

eZy

2021/10/26  阅读:29  主题:橙心

作者介绍

eZy