Skip to content

相关system variables和status variables

Jump to bottom
xiaoboluo768 edited this page Jun 8, 2020 · 2 revisions 
# status
admin@localhost : sbtest 06:06:32> SHOW STATUS LIKE 'perf%';
+-----------------------------------------------+-------+
| Variable_name                                 | 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)

# variables
admin@localhost : sbtest 06:45:04> SHOW variables LIKE 'perf%';
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
| performance_schema_accounts_size                         | -1    |
| performance_schema_digests_size                          | 10000 |
| performance_schema_events_stages_history_long_size       | 10000 |
| performance_schema_events_stages_history_size            | 10    |
| performance_schema_events_statements_history_long_size   | 10000 |
| performance_schema_events_statements_history_size        | 10    |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size      | 10    |
| performance_schema_events_waits_history_long_size        | 10000 |
| performance_schema_events_waits_history_size             | 10    |
| performance_schema_hosts_size                            | -1    |
| performance_schema_max_cond_classes                      | 80    |
| performance_schema_max_cond_instances                    | -1    |
| performance_schema_max_digest_length                     | 1024  |
| performance_schema_max_file_classes                      | 80    |
| performance_schema_max_file_handles                      | 32768 |
| performance_schema_max_file_instances                    | -1    |
| performance_schema_max_index_stat                        | -1    |
| performance_schema_max_memory_classes                    | 320   |
| performance_schema_max_metadata_locks                    | -1    |
| performance_schema_max_mutex_classes                     | 210   |
| performance_schema_max_mutex_instances                   | -1    |
| performance_schema_max_prepared_statements_instances     | -1    |
| performance_schema_max_program_instances                 | -1    |
| performance_schema_max_rwlock_classes                    | 40    |
| performance_schema_max_rwlock_instances                  | -1    |
| performance_schema_max_socket_classes                    | 10    |
| performance_schema_max_socket_instances                  | -1    |
| performance_schema_max_sql_text_length                   | 1024  |
| performance_schema_max_stage_classes                     | 150   |
| performance_schema_max_statement_classes                 | 193   |
| performance_schema_max_statement_stack                   | 10    |
| performance_schema_max_table_handles                     | -1    |
| performance_schema_max_table_instances                   | -1    |
| performance_schema_max_table_lock_stat                   | -1    |
| performance_schema_max_thread_classes                    | 50    |
| performance_schema_max_thread_instances                  | -1    |
| performance_schema_session_connect_attrs_size            | 512   |
| performance_schema_setup_actors_size                     | -1    |
| performance_schema_setup_objects_size                    | -1    |
| performance_schema_users_size                            | -1    |
+----------------------------------------------------------+-------+
42 rows in set (0.00 sec)

  • status variables中都是带着lost后缀的，这些status variables都是做什么用的呢？

    • 这些status variables用于查看instruments因为相关内存参数的限制而无法加载或创建的信息。这些变量的名称有几种形式：
      • Performance_schema_xxx_classes_lost：表示有多少个xxx类型的instruments无法加载或创建
      • Performance_schema_xxx_instances_lost：表示有多少个xxx对象类型的实例的instruments无法加载或创建
      • Performance_schema_xxx_handles_lost：表示有多少个xxx对象类型的handler的instruments无法加载或创建
      • Performance_schema_locker_lost：表示有多少个事件丢失或未记录

  • 如果server在运行时检测到mutex类型的instruments无法分配内存，则会增加状态变量Performance_schema_mutex_classes_lost的值（稍后会举例说明）。对于无法分配内存的instruments对应的mutex对象在server中仍然会继续发挥它的作用，但是此时这个mutex的性能数据不会被收集。如果这个mutex对应的instruments可以被分配，则表示这个mutex对应的instruments可以用于该mutex实例初始化一个instruments 实例来收集它的性能数据（当然具体是否能初始化，还需要看该mutex对应的实例数量阀值参数的设定值）。要注意的是：对于全局mutex类型，一个server可能只会创建一个实例，但对于其他mutex，在每个连接数、每个连接对应的数据缓存和数据缓存中每个页面上都有一个实例，当有mutex的instruments实例不能够创建时，则会增加状态变量Performance_schema_mutex_instances_lost的值。对于其他对象类型的instruments对应的lost变量，原理类似。

  • 假设以下条件成立：

    • server以performance_schema_max_mutex_classes = 200选项启动，代表mutex实例的instruments容量只有200个，假设此时server已载入了150个mutex instruments，现在要准备载入一个包含40个mutex instruments的插件plugin_a和一个包含20个mutex instruments的插件plugin_b，过程如下：
    • 载入插件plugin_a，此时server有150 + 40 = 190 mutex instruments
    • 卸载插件plugin_a，此时server仍然有190个mutex instruments，plugin_a插件的instruments代码生成的历史性能数据仍然可用，但是不会继续收集新的性能数据
    • 重新载入插件plugin_a，此时server检测到这个插件对应的mutex instruments已经被定义过，不会重新创建新的instruments，此时会重用之前分配的内部存储缓冲区，server此时仍然是190个mutex instruments
    • 载入插件plugin_b，该插件包含20个mutex instruments，而此时server的mutex instruments可用容量还剩下200-190=10，也就是说，该插件有10个mutex instruments不能够被载入，有多少个不能被载入的server就记录在状态变量Performance_schema_mutex_classes_lost中（状态变量Performance_schema_mutex_instances_lost保持不变）。
    • 导致Performance_schema_mutex_classes_lost状态变量值增加还有一种情况，就是当你自行调整了performance_schema_max_mutex_classes = N参数的大小，而N小于了MySQL发行版本中默认加载的插件需要的instruments数量（例如你可能禁用某些存储引擎，但是你并不知道这个N需要减少到多少合适，默认情况下不建议修改这个默认值，因为这个默认值足够分配MySQL发行版本默认加载的插件所需的instruments容量）
    • PS：
      • 1)、该模式适用于所有类型的instruments，不仅仅是mutex instrumetns，重启server时载入插件时发现instruments容量不足也会增加Performance_schema_mutex_classes_lost状态变量值
      • 2)、如果加载第三方插件，你需要把performance_schema_max_mutex_classes 变量值设置得足够大，因为第三方插件需要多少instruments数量并不能提前预知，所以该变量的默认值并没有考虑这些第三方插件需要的instruments容量，你需要自行考虑，否则在server启动时发现instruments容量不足会导致增加Performance_schema_mutex_classes_lost状态变量值
      • 3)、如果某instruments因为performance_schema_max_mutex_classes 设置的容量不足而无法加载，在错误日志以及运行时都不会报错，但是插件非instruments代码可以正常运行，也就是说，如果你没有留意到Performance_schema_mutex_classes_lost状态变量增加的话，可能导致你错失收集这些无法加载instrumetns的性能数据

  • 如果没有instruments因为容量限制而载入丢失，则就可以使用这些instruments生成instruments instance来检测像mutex这样的对象的性能数据，例如：'wait/synch/mutex/sql/LOCK_delete'，该instruments是配置表setup_instruments中的一个mutex instruments的名称。当在代码（在THD::LOCK_delete中）运行时创建mutex 对象时，将使用这个instruments来初始化一个instruments instance来检测这个mutex对象的性能数据，但是随着server运行时间的推移，数据库访问量的增加，可能需要创建很多mutex对象，也就对应需要许多mutex instruments instance(例如：server有1000个连接，即有1000个线程，那么就表示需要创建1000个'LOCK_delete' mutex（THD::LOCK_delete，LOCK_delete是线程级别的mutex对象），对应地，为了监控这些mutex对象的性能，也需要对应创建1000个mutex instruments instance)，如果server没有1000个mutex instruments instance的空间，那么就可能有一部分instruments instance无法创建，但是这些mutex对象会正常运行，只是无法收集性能数据，假如系统变量performance_schema_max_mutex_instances=800，就表示有200个instruments instance无法创建，那么状态变量Performance_schema_mutex_instances_lost的值就增加200。performance_schema_max_mutex_instances系统变量默认值是-1，表示会自动调整，非必须情况不建议去自定义这个系统参数的值。

  • 当某类对象的instruments instance无法创建时，对应的Performance_schema_xxx_lost状态变量值就会增加，可以通过如下语句查看：

admin@localhost : (none) 11:53:43> SHOW STATUS LIKE '%performance%';
+-----------------------------------------------+-------+
| Variable_name                                 | 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)
  • 有时候，可能会故意设置某类对象的instruments instance容量阀值为0，例如不关心IO的性能数据，则可以把IO相关的performance_schema参数设置为0，这样就不会为IO类型对象分配内存，不会收集IO类型对象的性能数据，可以使用如下语句来查看performance_schema内部的一些计数器和阀值（给定容量、当前使用量、内存分配量）：

admin@localhost : (none) 11:53:51> 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                                  | 2560      |
| performance_schema | events_waits_history.memory                                 | 450560    |
| performance_schema | events_waits_history_long.size                              | 176       |
| performance_schema | events_waits_history_long.count                             | 10000     |
| performance_schema | events_waits_history_long.memory                            | 1760000   |

......
| performance_schema | performance_schema.memory                                   | 137225400 |
+--------------------+-------------------------------------------------------------+-----------+
229 rows in set (0.00 sec)

  • PS：

    • MySQL 5.6.x版本中，使用information_schema下的GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS来获取全局、会话级别的system variables和status variables信息，在MySQL 5.7.6开始在performance_schema下添加相同的表来代替infromation_schema下的GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS表，同时添加系统变量show_compatibility_56来控制是否使用5.6.x之前的方式（即查询information_schema），但是在5.7.8之前的版本，这些表中记录的信息可能不完整，在5.7.8中修复。所以如果使用performance_schema来查询这些表，建议使用5.7.8及其更高的版本
    • MySQL 5.7.x中，除了把information_schema下的系统变量和状态变量信息表功能迁移到performance_schema下之外，还添加了variables_by_thread、status_by_thread、status_by_account、status_by_host、status_by_user几个表，可以根据不同的对象来分类查询相应的系统变量和状态变量
    • MySQL 8.0中移除了show_compatibility_56系统变量和变量信息表，即不再支持在information_schema下查询系统变量和状态变量信息

  • 参考链接：

上一篇： 性能分析思路 | 下一篇： 快速入门

  • 验证、测试、整理：罗小波
  • QQ：309969177
  • 提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
Clone this wiki locally