性能模式有几个相关的状态变量：There are several status variables associated with the Performance Schema:

mysql> 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_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_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_thread_classes_lost        | 0     |
| Performance_schema_thread_instances_lost      | 0     |
| Performance_schema_users_lost                 | 0     |
+-----------------------------------------------+-------+

Performance Schema 状态变量提供有关由于内存限制而无法加载或创建的工具的信息。这些变量的名称有几种形式：The Performance Schema status variables provide information about instrumentation that could not be loaded or created due to memory constraints. Names for these variables have several forms:

例如，如果在服务器源代码中对互斥锁进行了检测，但服务器在运行时无法为该检测分配内存，则会增加 Performance_schema_mutex_classes_lost。互斥锁仍然作为同步对象起作用（也就是说，服务器仍然可以正常运行），但不会收集其性能数据。如果可以分配检测对象，则可以用于初始化已检测的互斥锁实例。对于单例互斥锁，例如全局互斥锁，只有一个实例。其他互斥锁每个连接或每个页面在各种缓存和数据缓冲区中都有一个实例，因此实例数量随时间变化。增加最大连接数或某些缓冲区的最大大小会增加可能一次分配的最大实例数。如果服务器无法创建给定的已检测互斥锁实例，它会增加 Performance_schema_mutex_instances_lost。For example, if a mutex is instrumented in the server source but the server cannot allocate memory for the instrumentation at runtime, it increments Performance_schema_mutex_classes_lost. The mutex still functions as a synchronization object (that is, the server continues to function normally), but performance data for it is not collected. If the instrument can be allocated, it can be used for initializing instrumented mutex instances. For a singleton mutex such as a global mutex, there is only one instance. Other mutexes have an instance per connection, or per page in various caches and data buffers, so the number of instances varies over time. Increasing the maximum number of connections or the maximum size of some buffers increases the maximum number of instances that might be allocated at once. If the server cannot create a given instrumented mutex instance, it increments Performance_schema_mutex_instances_lost.

假设满足以下条件：Suppose that the following conditions hold:

服务器为插件分配互斥体仪器，具体取决于它们需要的数量以及可用数量，如下列语句序列所示：The server allocates mutex instruments for the plugins depending on how many they need and how many are available, as illustrated by the following sequence of statements:

INSTALL PLUGIN plugin_a

服务器现在有 150 + 40 = 190 个互斥体仪器。The server now has 150+40 = 190 mutex instruments.

UNINSTALL PLUGIN plugin_a;

服务器仍然有 190 个工具。插件代码生成的所有历史数据仍然可用，但不再收集这些工具的新事件。The server still has 190 instruments. All the historical data generated by the plugin code is still available, but new events for the instruments are not collected.

INSTALL PLUGIN plugin_a;

服务器检测到已有 40 个工具被定义，因此不会创建新工具，并且会重用之前分配的内部内存缓冲区。服务器仍然有 190 个工具。The server detects that the 40 instruments are already defined, so no new instruments are created, and previously assigned internal memory buffers are reused. The server still has 190 instruments.

INSTALL PLUGIN plugin_b;

服务器还有 200-190 = 10 个工具的位置（在此情况下为互斥类），并且发现插件包含 20 个新工具。加载了 10 个工具，另外 10 个被丢弃或“丢失。” Performance_schema_mutex_classes_lost 指示丢失的工具（互斥类）的数量：The server has room for 200-190 = 10 instruments (in this case, mutex classes), and sees that the plugin contains 20 new instruments. 10 instruments are loaded, and 10 are discarded or “lost.” The Performance_schema_mutex_classes_lost indicates the number of instruments (mutex classes) lost:

mysql> SHOW STATUS LIKE "perf%mutex_classes_lost";
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Performance_schema_mutex_classes_lost | 10    |
+---------------------------------------+-------+
1 row in set (0.10 sec)

仪器仍然可用，并为 plugin_b 收集（部分）数据。The instrumentation still works and collects (partial) data for plugin_b.

当服务器无法创建互斥锁仪器时，会出现以下结果：When the server cannot create a mutex instrument, these results occur:

刚才描述的模式适用于所有类型的工具，不仅仅是互斥锁。The pattern just described applies to all types of instruments, not just mutexes.

Performance_schema_mutex_classes_lost 的值大于 0 可能发生在两种情况：A value of Performance_schema_mutex_classes_lost greater than 0 can happen in two cases:

如果为 performance_schema_max_mutex_classes 选择的值过小，错误日志中不会报告错误，运行时也不会失败。然而，performance_schema 数据库中表的内容将缺少事件。Performance_schema_mutex_classes_lost 状态变量是唯一可见的迹象，表明由于无法创建工具而导致一些事件在内部被丢弃。If the value chosen for performance_schema_max_mutex_classes is too small, no error is reported in the error log and there is no failure at runtime. However, the content of the tables in the performance_schema database misses events. The Performance_schema_mutex_classes_lost status variable is the only visible sign to indicate that some events were dropped internally due to failure to create instruments.

如果一个工具没有丢失，它在性能模式（Performance Schema）中是已知的，并且在对实例进行工具化时会使用它。例如，wait/synch/mutex/sql/LOCK_delete 是 setup_instruments 表中一个互斥工具的名称。这个单一工具在代码中创建互斥时使用（在 THD::LOCK_delete 中），但是随着服务器运行，可能需要许多该互斥的实例。在这种情况下，LOCK_delete 是一个每连接（THD）的互斥，因此如果服务器有 1000 个连接，就会有 1000 个线程，以及 1000 个被工具化的 LOCK_delete 互斥实例（THD::LOCK_delete）。If an instrument is not lost, it is known to the Performance Schema, and is used when instrumenting instances. For example, wait/synch/mutex/sql/LOCK_delete is the name of a mutex instrument in the setup_instruments table. This single instrument is used when creating a mutex in the code (in THD::LOCK_delete) however many instances of the mutex are needed as the server runs. In this case, LOCK_delete is a mutex that is per connection (THD), so if a server has 1000 connections, there are 1000 threads, and 1000 instrumented LOCK_delete mutex instances (THD::LOCK_delete).

如果服务器没有足够的空间容纳所有这 1000 个带有监控的互斥锁（实例），有些互斥锁会带监控创建，而有些则不带监控创建。如果服务器只能创建 800 个实例，则会丢失 200 个实例。服务器将继续运行，但会将 Performance_schema_mutex_instances_lost 增加 200，以表示无法创建这些实例。If the server does not have room for all these 1000 instrumented mutexes (instances), some mutexes are created with instrumentation, and some are created without instrumentation. If the server can create only 800 instances, 200 instances are lost. The server continues to run, but increments Performance_schema_mutex_instances_lost by 200 to indicate that instances could not be created.

当代码在运行时初始化的互斥锁数量超过为 --performance_schema_max_mutex_instances=N 分配的数量时，Performance_schema_mutex_instances_lost 的值可能大于 0。A value of Performance_schema_mutex_instances_lost greater than 0 can happen when the code initializes more mutexes at runtime than were allocated for --performance_schema_max_mutex_instances=N.

关键点是，如果 SHOW STATUS LIKE 'perf%' 显示没有丢失任何数据（所有值都是零），那么性能模式（Performance Schema）数据是准确的，可以信赖。如果有数据丢失，那么数据是不完整的，并且由于分配给性能模式的内存不足，它无法记录所有内容。在这种情况下，具体的 Performance_schema_xxx_lost 变量会指出问题领域。The bottom line is that if SHOW STATUS LIKE 'perf%' says that nothing was lost (all values are zero), the Performance Schema data is accurate and can be relied upon. If something was lost, the data is incomplete, and the Performance Schema could not record everything given the insufficient amount of memory it was given to use. In this case, the specific Performance_schema_xxx_lost variable indicates the problem area.

在某些情况下，故意造成监控工具的资源饥饿可能是适当的。例如，如果您不关心文件 I/O 的性能数据，可以在启动服务器时将所有与文件 I/O 相关的性能模式参数设置为 0。不会为文件相关的类、实例或句柄分配内存，所有文件事件都会丢失。It might be appropriate in some cases to cause deliberate instrument starvation. For example, if you do not care about performance data for file I/O, you can start the server with all Performance Schema parameters related to file I/O set to 0. No memory is allocated for file-related classes, instances, or handles, and all file events are lost.

使用 SHOW ENGINE PERFORMANCE_SCHEMA STATUS 来检查 Performance Schema 代码的内部操作：Use SHOW ENGINE PERFORMANCE_SCHEMA STATUS to inspect the internal operation of the Performance Schema code:

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
...
*************************** 3. row ***************************
  Type: performance_schema
  Name: events_waits_history.size
Status: 76
*************************** 4. row ***************************
  Type: performance_schema
  Name: events_waits_history.count
Status: 10000
*************************** 5. row ***************************
  Type: performance_schema
  Name: events_waits_history.memory
Status: 760000
...
*************************** 57. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 26459600
...

该语句旨在帮助 DBA 理解不同 Performance Schema 选项对内存需求的影响。有关字段含义的描述，请参见 第 15.7.7.16 节，“SHOW ENGINE 语句”。This statement is intended to help the DBA understand the effects that different Performance Schema options have on memory requirements. For a description of the field meanings, see Section 15.7.7.16, “SHOW ENGINE Statement”.