• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 性能架构快速入门

    本节简要介绍性能模式,并提供示例说明如何使用它。有关其他示例,请参见“使用性能模式诊断问题”。

    默认情况下,性能模式处于启用状态。要明确启用或禁用它,请在将performance_schema变量设置为适当值的情况下启动服务器。例如,在服务器my.cnf文件中使用以下行:

    [mysqld]
    performance_schema=ON
    

    服务器启动时,它将看到performance_schema并尝试初始化性能模式。要验证初始化是否成功,请使用以下语句:

    mysql> SHOW VARIABLES LIKE 'performance_schema';
    +--------------------	+-------	+
    | Variable_name	| Value	|
    +--------------------	+-------	+
    | performance_schema	| ON	|
    +--------------------	+-------	+
    

    ON表示性能模式已成功初始化并且可以使用。值OFF表示发生了一些错误。检查服务器错误日志,以获取有关发生问题的信息。

    Performance Schema是作为存储引擎实现的,因此您将在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中看到它:

    mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
           WHERE ENGINE='PERFORMANCE_SCHEMA'\G
    *************************** 1. row***************************
          ENGINE: PERFORMANCE_SCHEMA
         SUPPORT: YES
         COMMENT: Performance Schema
    TRANSACTIONS: NO
              XA: NO
      SAVEPOINTS: NO
    mysql> SHOW ENGINES\G
    ...
          Engine: PERFORMANCE_SCHEMA
         Support: YES
         Comment: Performance Schema
    Transactions: NO
              XA: NO
      Savepoints: NO
    ...
    

    PERFORMANCE_SCHEMA存储引擎上的表进行操作performance_schema数据库。您可以performance_schema建立默认数据库,以便对其表的引用无需使用数据库名称进行限定:

    mysql> USE performance_schema;
    

    性能架构表存储在performance_schema数据库中。与任何其他数据库一样,可以通过从INFORMATION_SCHEMA数据库中选择或使用SHOW语句来获取有关此数据库及其表的结构的信息。例如,使用以下任一语句来参见存在哪些性能模式表:

    mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_SCHEMA = 'performance_schema';
    +------------------------------------------------------	+
    | TABLE_NAME	|
    +------------------------------------------------------	+
    | accounts	|
    | cond_instances	|
    ...
    | events_stages_current	|
    | events_stages_history	|
    | events_stages_history_long	|
    | events_stages_summary_by_account_by_event_name	|
    | events_stages_summary_by_host_by_event_name	|
    | events_stages_summary_by_thread_by_event_name	|
    | events_stages_summary_by_user_by_event_name	|
    | events_stages_summary_global_by_event_name	|
    | events_statements_current	|
    | events_statements_history	|
    | events_statements_history_long	|
    ...
    | file_instances	|
    | file_summary_by_event_name	|
    | file_summary_by_instance	|
    | host_cache	|
    | hosts	|
    | memory_summary_by_account_by_event_name	|
    | memory_summary_by_host_by_event_name	|
    | memory_summary_by_thread_by_event_name	|
    | memory_summary_by_user_by_event_name	|
    | memory_summary_global_by_event_name	|
    | metadata_locks	|
    | mutex_instances	|
    | objects_summary_global_by_type	|
    | performance_timers	|
    | replication_connection_configuration	|
    | replication_connection_status	|
    | replication_applier_configuration	|
    | replication_applier_status	|
    | replication_applier_status_by_coordinator	|
    | replication_applier_status_by_worker	|
    | rwlock_instances	|
    | session_account_connect_attrs	|
    | session_connect_attrs	|
    | setup_actors	|
    | setup_consumers	|
    | setup_instruments	|
    | setup_objects	|
    | socket_instances	|
    | socket_summary_by_event_name	|
    | socket_summary_by_instance	|
    | table_handles	|
    | table_io_waits_summary_by_index_usage	|
    | table_io_waits_summary_by_table	|
    | table_lock_waits_summary_by_table	|
    | threads	|
    | users	|
    +------------------------------------------------------	+
    
    mysql> SHOW TABLES FROM performance_schema;
    +------------------------------------------------------	+
    | Tables_in_performance_schema	|
    +------------------------------------------------------	+
    | accounts	|
    | cond_instances	|
    | events_stages_current	|
    | events_stages_history	|
    | events_stages_history_long	|
    ...
    

    随着实施其他工具的进行,性能架构表的数量会随着时间增加。

    performance_schema数据库的名称是小写的,其中的表的名称也是小写的。查询应以小写形式指定名称。

    要参见单个表的结构,请使用SHOW CREATE TABLE

    mysql> SHOW CREATE TABLE performance_schema.setup_consumers\G
    *************************** 1. row***************************
           Table: setup_consumers
    Create Table: CREATE TABLE `setup_consumers` (
      `NAME` varchar(64) NOT NULL,
      `ENABLED` enum('YES','NO') NOT NULL,
      PRIMARY KEY (`NAME`)
    ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    

    也可以通过从表中进行选择INFORMATION_SCHEMA.COLUMNS或使用诸如的语句来获得表结构SHOW COLUMNS

    performance_schema数据库中的表可以根据其中的信息类型进行分组:当前事件,事件历史记录和摘要,对象实例以及设置(配置)信息。以下示例说明了这些表的一些用法。有关每个组中表的详细信息,请参见“性能模式表说明”。

    最初,并非所有工具和使用者都已启用,因此性能架构不会收集所有事件。要打开所有这些功能并启用事件计时,请执行两个语句(行数可能因MySQL版本而异):

    mysql> UPDATE performance_schema.setup_instruments
           SET ENABLED = 'YES', TIMED = 'YES';
    Query OK, 560 rows affected (0.04 sec)
    mysql> UPDATE performance_schema.setup_consumers
           SET ENABLED = 'YES';
    Query OK, 10 rows affected (0.00 sec)
    

    要参见服务器当前正在做什么,请检查events_waits_current表。每个线程包含一行,显示每个线程的最新监视事件:

    mysql> SELECT *
           FROM performance_schema.events_waits_current\G
    *************************** 1. row***************************
                THREAD_ID: 0
                 EVENT_ID: 5523
             END_EVENT_ID: 5523
               EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
                   SOURCE: thr_lock.c:525
              TIMER_START: 201660494489586
                TIMER_END: 201660494576112
               TIMER_WAIT: 86526
                    SPINS: NULL
            OBJECT_SCHEMA: NULL
              OBJECT_NAME: NULL
               INDEX_NAME: NULL
              OBJECT_TYPE: NULL
    OBJECT_INSTANCE_BEGIN: 142270668
         NESTING_EVENT_ID: NULL
       NESTING_EVENT_TYPE: NULL
                OPERATION: lock
          NUMBER_OF_BYTES: NULL
                    FLAGS: 0
    ...
    

    此事件表明线程0正在等待86,526皮秒来获取THR_LOCK::mutexmysys子系统中的互斥锁的锁定。前几列提供以下信息:

    • ID列指示事件来自哪个线程以及事件编号。
    • EVENT_NAME指示已检测的内容,并SOURCE指示哪个源文件包含已检测的代码。
    • 计时器列显示事件何时开始和停止以及花费了多长时间。如果事件仍在进行中,则TIMER_ENDTIMER_WAIT值为NULL。计时器值是近似值,以皮秒表示。有关计时器和事件时间收集的信息,请参见“性能架构事件计时”。

    历史表中包含同一种行作为当前事件表,但有更多的行,并显示服务器已经做什么“近日”而非“目前。”在events_waits_historyevents_waits_history_long表包含每个线程的最近10个事件和最近10,000个事件,分别。例如,要参见线程13产生的最近事件的信息,请执行以下操作:

    mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT
           FROM performance_schema.events_waits_history
           WHERE THREAD_ID = 13
           ORDER BY EVENT_ID;
    +----------	+-----------------------------------------	+------------	+
    | EVENT_ID	| EVENT_NAME	| TIMER_WAIT	|
    +----------	+-----------------------------------------	+------------	+
    |       86	| wait/synch/mutex/mysys/THR_LOCK::mutex	|     686322	|
    |       87	| wait/synch/mutex/mysys/THR_LOCK_malloc	|     320535	|
    |       88	| wait/synch/mutex/mysys/THR_LOCK_malloc	|     339390	|
    |       89	| wait/synch/mutex/mysys/THR_LOCK_malloc	|     377100	|
    |       90	| wait/synch/mutex/sql/LOCK_plugin	|     614673	|
    |       91	| wait/synch/mutex/sql/LOCK_open	|     659925	|
    |       92	| wait/synch/mutex/sql/THD::LOCK_thd_data	|     494001	|
    |       93	| wait/synch/mutex/mysys/THR_LOCK_malloc	|     222489	|
    |       94	| wait/synch/mutex/mysys/THR_LOCK_malloc	|     214947	|
    |       95	| wait/synch/mutex/mysys/LOCK_alarm	|     312993	|
    +----------	+-----------------------------------------	+------------	+
    

    当新事件添加到历史表中时,如果表已满,则旧事件将被丢弃。

    摘要表提供了一段时间内所有事件的汇总信息。该组中的表以不同的方式总结了事件数据。要参见执行次数最多或等待时间最多的工具,请对或列events_waits_summary_global_by_event_name上的表进行排序,这些表分别对应于针对所有事件计算的或值:COUNT_STARSUM_TIMER_WAITCOUNT(*)SUM(TIMER_WAIT)

    mysql> SELECT EVENT_NAME, COUNT_STAR
           FROM performance_schema.events_waits_summary_global_by_event_name
           ORDER BY COUNT_STAR DESC LIMIT 10;
    +---------------------------------------------------	+------------	+
    | EVENT_NAME	| COUNT_STAR	|
    +---------------------------------------------------	+------------	+
    | wait/synch/mutex/mysys/THR_LOCK_malloc	|       6419	|
    | wait/io/file/sql/FRM	|        452	|
    | wait/synch/mutex/sql/LOCK_plugin	|        337	|
    | wait/synch/mutex/mysys/THR_LOCK_open	|        187	|
    | wait/synch/mutex/mysys/LOCK_alarm	|        147	|
    | wait/synch/mutex/sql/THD::LOCK_thd_data	|        115	|
    | wait/io/file/myisam/kfile	|        102	|
    | wait/synch/mutex/sql/LOCK_global_system_variables	|         89	|
    | wait/synch/mutex/mysys/THR_LOCK::mutex	|         89	|
    | wait/synch/mutex/sql/LOCK_open	|         88	|
    +---------------------------------------------------	+------------	+
    
    mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT
           FROM performance_schema.events_waits_summary_global_by_event_name
           ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
    +----------------------------------------	+----------------	+
    | EVENT_NAME	| SUM_TIMER_WAIT	|
    +----------------------------------------	+----------------	+
    | wait/io/file/sql/MYSQL_LOG	|     1599816582	|
    | wait/synch/mutex/mysys/THR_LOCK_malloc	|     1530083250	|
    | wait/io/file/sql/binlog_index	|     1385291934	|
    | wait/io/file/sql/FRM	|     1292823243	|
    | wait/io/file/myisam/kfile	|      411193611	|
    | wait/io/file/myisam/dfile	|      322401645	|
    | wait/synch/mutex/mysys/LOCK_alarm	|      145126935	|
    | wait/io/file/sql/casetest	|      104324715	|
    | wait/synch/mutex/sql/LOCK_plugin	|       86027823	|
    | wait/io/file/sql/pid	|       72591750	|
    +----------------------------------------	+----------------	+
    

    这些结果表明,THR_LOCK_malloc互斥体在使用频率和线程等待尝试获取它的时间上都是“热”的。

    注意

    THR_LOCK_malloc互斥仅用于调试版本。在生产环境中,它不存在,因为它不存在。

    实例表记录了要检测的对象的类型。服务器使用检测对象时,将产生一个事件。这些表提供事件名称和说明性注释或状态信息。例如,下file_instances表列出了用于文件I / O操作及其相关文件的工具实例:

    mysql> SELECT *
           FROM performance_schema.file_instances\G
    *************************** 1. row***************************
     FILE_NAME: /opt/mysql-log/60500/binlog.000007
    EVENT_NAME: wait/io/file/sql/binlog
    OPEN_COUNT: 0
    *************************** 2. row***************************
     FILE_NAME: /opt/mysql/60500/data/mysql/tables_priv.MYI
    EVENT_NAME: wait/io/file/myisam/kfile
    OPEN_COUNT: 1
    *************************** 3. row***************************
     FILE_NAME: /opt/mysql/60500/data/mysql/columns_priv.MYI
    EVENT_NAME: wait/io/file/myisam/kfile
    OPEN_COUNT: 1
    ...
    

    设置表用于配置和显示监视特性。例如,setup_instruments列出了可以收集事件的一组工具,并显示了启用了哪些工具:

    mysql> SELECT NAME, ENABLED, TIMED
           FROM performance_schema.setup_instruments;
    +---------------------------------------------------	+---------	+-------	+
    | NAME	| ENABLED	| TIMED	|
    +---------------------------------------------------	+---------	+-------	+
    ...
    | stage/sql/end	| NO	| NO	|
    | stage/sql/executing	| NO	| NO	|
    | stage/sql/init	| NO	| NO	|
    | stage/sql/insert	| NO	| NO	|
    ...
    | statement/sql/load	| YES	| YES	|
    | statement/sql/grant	| YES	| YES	|
    | statement/sql/check	| YES	| YES	|
    | statement/sql/flush	| YES	| YES	|
    ...
    | wait/synch/mutex/sql/LOCK_global_read_lock	| YES	| YES	|
    | wait/synch/mutex/sql/LOCK_global_system_variables	| YES	| YES	|
    | wait/synch/mutex/sql/LOCK_lock_db	| YES	| YES	|
    | wait/synch/mutex/sql/LOCK_manager	| YES	| YES	|
    ...
    | wait/synch/rwlock/sql/LOCK_grant	| YES	| YES	|
    | wait/synch/rwlock/sql/LOGGER::LOCK_logger	| YES	| YES	|
    | wait/synch/rwlock/sql/LOCK_sys_init_connect	| YES	| YES	|
    | wait/synch/rwlock/sql/LOCK_sys_init_slave	| YES	| YES	|
    ...
    | wait/io/file/sql/binlog	| YES	| YES	|
    | wait/io/file/sql/binlog_index	| YES	| YES	|
    | wait/io/file/sql/casetest	| YES	| YES	|
    | wait/io/file/sql/dbopt	| YES	| YES	|
    ...
    

    要了解如何解释仪器名称,请参见“性能模式仪器命名约定”。

    要控制是否为仪器收集事件,请将其ENABLED值设置为YESNO。例如:

    mysql> UPDATE performance_schema.setup_instruments
           SET ENABLED = 'NO'
           WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';
    

    性能架构使用收集的事件来更新performance_schema数据库中的表,这些表充当事件信息的“使用者”。下setup_consumers表列出了可用的使用者和已启用的使用者:

    mysql> SELECT * FROM performance_schema.setup_consumers;
    +----------------------------------	+---------	+
    | 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	| YES	|
    | events_transactions_history	| YES	|
    | events_transactions_history_long	| NO	|
    | events_waits_current	| NO	|
    | events_waits_history	| NO	|
    | events_waits_history_long	| NO	|
    | global_instrumentation	| YES	|
    | thread_instrumentation	| YES	|
    | statements_digest	| YES	|
    +----------------------------------	+---------	+
    

    若要控制性能架构是否将使用者保持为事件信息的目的地,请设置其ENABLED值。

    有关设置表以及如何使用它们控制事件收集的更多信息,请参见“性能架构事件过滤”。

    有些杂项表不属于前面的任何组。例如,performance_timers列出可用的事件计时器及其特征。有关计时器的信息,请参见“性能模式事件计时”。