InnoDB集成MySQL性能模式
你可以使用MySQL Performance Schema特性来分析某些内部InnoDB操作。这种类型的调优主要针对那些评估优化策略以克服性能瓶颈的专家用户。dba还可以使用此功能进行容量规划，以查看他们的典型工作负载在特定的CPU、RAM和磁盘存储组合上是否遇到任何性能瓶颈;如果是，则判断是否可以通过增加系统某些部分的容量来提高性能。
使用这个特性来检查InnoDB的性能:
.您必须熟悉如何使用Performance Schema特性。例如，您应该知道如何启用仪器和消费者，以及如何查询performance_schema表以检索数据。
.您应该熟悉用于InnoDB的性能模式工具。要查看与innodb相关的仪器，可以查询setup_instruments表中包含’innodb’的仪器名称。
mysql> use performance_schema Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM setup_instruments WHERE NAME LIKE '%innodb%'; +-------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/commit_cond_mutex | NO | NO | | wait/synch/mutex/innodb/innobase_share_mutex | NO | NO | | wait/synch/mutex/innodb/autoinc_mutex | NO | NO | .......... | memory/innodb/adaptive hash index | NO | NO | | memory/innodb/buf_buf_pool | NO | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO | | memory/innodb/dict_stats_index_map_t | NO | NO | | memory/innodb/dict_stats_n_diff_on_level | NO | NO | | memory/innodb/other | NO | NO | | memory/innodb/row_log_buf | NO | NO | | memory/innodb/row_merge_sort | NO | NO | | memory/innodb/std | NO | NO | | memory/innodb/trx_sys_t::rw_trx_ids | NO | NO | | memory/innodb/partitioning | NO | NO | | memory/innodb/api0api | NO | NO | | memory/innodb/btr0btr | NO | NO | | memory/innodb/btr0bulk | NO | NO | | memory/innodb/btr0cur | NO | NO | | memory/innodb/btr0pcur | NO | NO | | memory/innodb/btr0sea | NO | NO | | memory/innodb/buf0buf | NO | NO | | memory/innodb/buf0dblwr | NO | NO | | memory/innodb/buf0dump | NO | NO | | memory/innodb/buf0flu | NO | NO | | memory/innodb/buf0lru | NO | NO | | memory/innodb/dict0dict | NO | NO | | memory/innodb/dict0mem | NO | NO | | memory/innodb/dict0stats | NO | NO | | memory/innodb/dict0stats_bg | NO | NO | | memory/innodb/eval0eval | NO | NO | | memory/innodb/fil0fil | NO | NO | | memory/innodb/fsp0file | NO | NO | | memory/innodb/fsp0space | NO | NO | | memory/innodb/fsp0sysspace | NO | NO | | memory/innodb/fts0ast | NO | NO | | memory/innodb/fts0config | NO | NO | | memory/innodb/fts0fts | NO | NO | | memory/innodb/fts0opt | NO | NO | | memory/innodb/fts0pars | NO | NO | | memory/innodb/fts0que | NO | NO | | memory/innodb/fts0sql | NO | NO | | memory/innodb/gis0sea | NO | NO | | memory/innodb/ha0ha | NO | NO | | memory/innodb/ha_innodb | NO | NO | | memory/innodb/handler0alter | NO | NO | | memory/innodb/hash0hash | NO | NO | | memory/innodb/i_s | NO | NO | | memory/innodb/ibuf0ibuf | NO | NO | | memory/innodb/lexyy | NO | NO | | memory/innodb/lock0lock | NO | NO | | memory/innodb/log0log | NO | NO | | memory/innodb/log0recv | NO | NO | | memory/innodb/mem0mem | NO | NO | | memory/innodb/os0event | NO | NO | | memory/innodb/os0file | NO | NO | | memory/innodb/page0cur | NO | NO | | memory/innodb/page0zip | NO | NO | | memory/innodb/pars0lex | NO | NO | | memory/innodb/read0read | NO | NO | | memory/innodb/rem0rec | NO | NO | | memory/innodb/row0ftsort | NO | NO | | memory/innodb/row0import | NO | NO | | memory/innodb/row0log | NO | NO | | memory/innodb/row0merge | NO | NO | | memory/innodb/row0mysql | NO | NO | | memory/innodb/row0sel | NO | NO | | memory/innodb/row0trunc | NO | NO | | memory/innodb/srv0conc | NO | NO | | memory/innodb/srv0srv | NO | NO | | memory/innodb/srv0start | NO | NO | | memory/innodb/sync0arr | NO | NO | | memory/innodb/sync0debug | NO | NO | | memory/innodb/sync0rw | NO | NO | | memory/innodb/sync0types | NO | NO | | memory/innodb/trx0i_s | NO | NO | | memory/innodb/trx0purge | NO | NO | | memory/innodb/trx0roll | NO | NO | | memory/innodb/trx0rseg | NO | NO | | memory/innodb/trx0sys | NO | NO | | memory/innodb/trx0trx | NO | NO | | memory/innodb/trx0undo | NO | NO | | memory/innodb/usr0sess | NO | NO | | memory/innodb/ut0list | NO | NO | | memory/innodb/ut0mem | NO | NO | | memory/innodb/ut0mutex | NO | NO | | memory/innodb/ut0pool | NO | NO | | memory/innodb/ut0rbt | NO | NO | | memory/innodb/ut0wqueue | NO | NO | +-------------------------------------------------------+---------+-------+ 167 rows in set (0.00 sec)
关于已仪表化的InnoDB对象的附加信息，您可以查询Performance Schema实例表，它提供了关于已仪表化对象的附加信息。与InnoDB相关的实例表
包括:
.mutex_instances表
.rwlock_instances表
.cond_instances表
.file_instances表
注意:
与InnoDB缓冲池相关的互斥量和读写锁不在本章的讨论范围内;SHOW ENGINE INNODB MUTEX命令的输出结果也是如此。
例如，在执行文件I/O仪表化时，要查看Performance Schema所看到的已仪表化的InnoDB文件对象的信息，您可以发出以下查询:
mysql> SELECT * FROM file_instances WHERE EVENT_NAME LIKE '%innodb%'\G *************************** 1. row *************************** FILE_NAME: /mysqldata/mysql/ibdata1 EVENT_NAME: wait/io/file/innodb/innodb_data_file OPEN_COUNT: 3 *************************** 2. row *************************** FILE_NAME: /mysqldata/mysql/ib_logfile0 EVENT_NAME: wait/io/file/innodb/innodb_log_file OPEN_COUNT: 2 *************************** 3. row *************************** FILE_NAME: /mysqldata/mysql/ib_logfile1 EVENT_NAME: wait/io/file/innodb/innodb_log_file OPEN_COUNT: 2 ......
你应该要熟悉存储InnoDB事件数据的performance_schema表。与innodb相关事件相关的表包括:
.等待事件表，存储等待事件。
.汇总表，提供随时间推移终止的事件的聚合信息。汇总表包括文件I/O汇总表，它聚合了关于I/O操作的信息。
.阶段事件表，存储InnoDB ALTER TABLE和缓冲池load操作事件数据。
使用性能模式监控InnoDB表的ALTER TABLE进度
你可以使用Performance Schema监控InnoDB表的ALTER TABLE进度。
有7个阶段事件表示ALTER TABLE的不同阶段。每个阶段事件报告整个ALTER TABLE操作在其不同阶段进行时的WORK_COMPLETED和WORK_ESTIMATED运行总数。WORK_ESTIMATEDe使用一个公式计算，该公式考虑了ALTER TABLE执行的所有工作，并且可能在ALTER TABLE处理期间进行修改。WORK_COMPLETED和WORK_ESTIMATED值是ALTER TABLE执行的所有工作的抽象表示。
按照发生的顺序，ALTER TABLE阶段的事件包括:
.stage/innodb/alter table (read PK and internal sort):当ALTER TABLE处于read -primary-key阶段时，这个阶段是活动的。它一开始将设置WORK_COMPLETED=0和WORK_ESTIMATED设置为主键中估计的页面数。当这个阶段完成时，WORK_ESTIMATED被更新为主键中的实际页面数。
.stage/innodb/alter table (merge sort):对于通过ALTER TABLE操作添加的每个索引，重复此阶段。
.stage/innodb/alter table (insert):对于通过ALTER TABLE操作添加的每个索引，重复此阶段。
.stage/innodb/alter table (log apply index):这个阶段包括应用运行ALTER TABLE时生成的DML日志。
.stage/innodb/alter table (flush):在此阶段开始之前，根据刷新列表的长度，使用更准确的估计值更新WORK_ESTIMATED。
.stage/innodb/alter table (log apply table):此阶段包括应用在ALTER TABLE运行时生成的并发DML日志。这个阶段的持续时间取决于表变化的程度。如果在表上没有运行并发的DML，则此阶段会瞬间完成。
.stage/innodb/alter table (end):包括在刷新阶段之后出现的任何剩余工作，例如当运行ALTER TABLE时重新应用在表上执行的DML。
注意:
InnoDB ALTER TABLE阶段事件目前不会对添加的空间索引进行计数。
使用Performance Schema(性能模式)监控ALTER TABLE操作
下面的例子演示了如何启用stage/innodb/alter table% stage事件仪器和相关的消费者表来监控alter table的进度。
1.启用stage/innodb/alter%仪器:
mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%'; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0
2.启用阶段事件消费者表，其中包括events_stages_current、events_stages_history和events_stages_history_long。
mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
3.执行ALTER TABLE操作。在本例中，将middle_name列添加到employees示例数据库的employees表中。
mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name; Query OK, 0 rows affected (1.80 sec) Records: 0 Duplicates: 0 Warnings: 0
4.通过查询Performance Schema的events_stages_current表来检查ALTER TABLE操作的进度。所显示的阶段事件取决于当前正在进行的ALTER TABLE阶段。WORK_COMPLETED列显示完成的工作，WORK_ESTIMATED列提供了对剩余工作的估计。
mysql> use performance_schema Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current; Empty set (0.00 sec) mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current; +----------------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +----------------------------------+----------------+----------------+ | stage/innodb/alter table (flush) | 938 | 1793 | +----------------------------------+----------------+----------------+ 1 row in set (0.00 sec)
如果ALTER TABLE操作已经完成，events_stages_current表返回一个空集合。在这种情况下，您可以检查events_stages_history表来查看已完成操作的事件数据。例如:
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history; +------------------------------------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +------------------------------------------------------+----------------+----------------+ | stage/innodb/alter table (read PK and internal sort) | 886 | 1793 | | stage/innodb/alter table (flush) | 1793 | 1793 | | stage/innodb/alter table (log apply table) | 2177 | 2177 | | stage/innodb/alter table (end) | 2177 | 2177 | | stage/innodb/alter table (log apply table) | 2561 | 2561 | +------------------------------------------------------+----------------+----------------+ 5 rows in set (0.00 sec)
如上所示，在ALTER TABLE处理期间修改了WORK_ESTIMATED的值。初始阶段完成后的预计工作量为1793。当ALTER TABLE处理完成时，WORK_ESTIMATED被设置为实际值，即2561。
使用Performance Schema(性能模式)监控InnoDB互斥锁等待
互斥锁是代码中使用的一种同步机制，用于强制在给定时间内只有一个线程可以访问公共资源。当服务器上执行的两个或多个线程需要访问相同的资源时，线程之间会相互竞争。第一个获得互斥锁的线程会导致其他线程等待，直到锁被释放。
对于仪表化的InnoDB互斥锁，可以使用Performance Schema来监控互斥锁的等待。
例如，在Performance Schema表中收集的等待事件数据可以帮助识别等待时间最长或总等待时间最长的互斥锁。
下面的例子演示了如何启用InnoDB互斥锁等待工具，如何启用关联的消费者，以及如何查询等待事件数据。
1.要查看可用的InnoDB互斥锁等待工具，请查询Performance Schema setup_instruments表，如下所示。所有InnoDB互斥锁等待工具默认是禁用的。
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%'; +-------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/commit_cond_mutex | NO | NO | | wait/synch/mutex/innodb/innobase_share_mutex | NO | NO | | wait/synch/mutex/innodb/autoinc_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_zip_mutex | NO | NO | | wait/synch/mutex/innodb/cache_last_read_mutex | NO | NO | | wait/synch/mutex/innodb/dict_foreign_err_mutex | NO | NO | | wait/synch/mutex/innodb/dict_sys_mutex | NO | NO | | wait/synch/mutex/innodb/recalc_pool_mutex | NO | NO | | wait/synch/mutex/innodb/file_format_max_mutex | NO | NO | | wait/synch/mutex/innodb/fil_system_mutex | NO | NO | | wait/synch/mutex/innodb/flush_list_mutex | NO | NO | | wait/synch/mutex/innodb/fts_bg_threads_mutex | NO | NO | | wait/synch/mutex/innodb/fts_delete_mutex | NO | NO | | wait/synch/mutex/innodb/fts_optimize_mutex | NO | NO | | wait/synch/mutex/innodb/fts_doc_id_mutex | NO | NO | | wait/synch/mutex/innodb/fts_pll_tokenize_mutex | NO | NO | | wait/synch/mutex/innodb/log_flush_order_mutex | NO | NO | | wait/synch/mutex/innodb/hash_table_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO | | wait/synch/mutex/innodb/log_sys_mutex | NO | NO | | wait/synch/mutex/innodb/log_sys_write_mutex | NO | NO | | wait/synch/mutex/innodb/log_cmdq_mutex | NO | NO | | wait/synch/mutex/innodb/mutex_list_mutex | NO | NO | | wait/synch/mutex/innodb/page_cleaner_mutex | NO | NO | | wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | NO | NO | | wait/synch/mutex/innodb/purge_sys_pq_mutex | NO | NO | | wait/synch/mutex/innodb/recv_sys_mutex | NO | NO | | wait/synch/mutex/innodb/recv_writer_mutex | NO | NO | | wait/synch/mutex/innodb/redo_rseg_mutex | NO | NO | | wait/synch/mutex/innodb/noredo_rseg_mutex | NO | NO | | wait/synch/mutex/innodb/rw_lock_list_mutex | NO | NO | | wait/synch/mutex/innodb/rw_lock_mutex | NO | NO | | wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | NO | NO | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | NO | NO | | wait/synch/mutex/innodb/srv_misc_tmpfile_mutex | NO | NO | | wait/synch/mutex/innodb/srv_monitor_file_mutex | NO | NO | | wait/synch/mutex/innodb/buf_dblwr_mutex | NO | NO | | wait/synch/mutex/innodb/trx_undo_mutex | NO | NO | | wait/synch/mutex/innodb/trx_pool_mutex | NO | NO | | wait/synch/mutex/innodb/trx_pool_manager_mutex | NO | NO | | wait/synch/mutex/innodb/srv_sys_mutex | NO | NO | | wait/synch/mutex/innodb/lock_mutex | NO | NO | | wait/synch/mutex/innodb/lock_wait_mutex | NO | NO | | wait/synch/mutex/innodb/trx_mutex | NO | NO | | wait/synch/mutex/innodb/srv_threads_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_active_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_match_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_path_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_ssn_mutex | NO | NO | | wait/synch/mutex/innodb/trx_sys_mutex | NO | NO | | wait/synch/mutex/innodb/thread_mutex | NO | NO | | wait/synch/mutex/innodb/sync_array_mutex | NO | NO | | wait/synch/mutex/innodb/zip_pad_mutex | NO | NO | | wait/synch/mutex/innodb/row_drop_list_mutex | NO | NO | | wait/synch/mutex/innodb/master_key_id_mutex | NO | NO | +-------------------------------------------------------+---------+-------+ 58 rows in set (0.00 sec)
2.一些InnoDB互斥锁实例是在服务器启动时创建的，并且只有在服务器启动时启用了相关的工具时才会被检测。为了确保所有InnoDB互斥锁实例都被仪表化和启用，请将以下性能模式仪表规则添加到您的MySQL配置文件:
performance-schema-instrument='wait/synch/mutex/innodb%=ON'
如果您不需要某等待事件的所有InnoDB互斥锁数据，您可以通过在MySQL配置文件中添加额外的性能模式仪表规则来禁用特定的仪表。例如，要禁用与全文搜索相关的InnoDB互斥锁等待事件工具，请添加以下规则:
performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'
注意:
前缀较长的规则(如wait/sync /mutex/innodb/fts%)优先于前缀较短的规则(如wait/sync /mutex/innodb/ %)。
将performance-schema-instrument规则添加到配置文件后，重新启动服务器。除了那些与全文搜索相关的其它所有的InnoDB互斥锁都是启用的。要验证这一点，请查询setup_instruments表。对于您启用的仪器，ENABLED和TIMED列应该设置为YES。
[root@localhost mysql]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL... SUCCESS! mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%'; +-------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/commit_cond_mutex | YES | YES | | wait/synch/mutex/innodb/innobase_share_mutex | YES | YES | | wait/synch/mutex/innodb/autoinc_mutex | YES | YES | | wait/synch/mutex/innodb/buf_pool_mutex | YES | YES | | wait/synch/mutex/innodb/buf_pool_zip_mutex | YES | YES | | wait/synch/mutex/innodb/cache_last_read_mutex | YES | YES | | wait/synch/mutex/innodb/dict_foreign_err_mutex | YES | YES | | wait/synch/mutex/innodb/dict_sys_mutex | YES | YES | | wait/synch/mutex/innodb/recalc_pool_mutex | YES | YES | | wait/synch/mutex/innodb/file_format_max_mutex | YES | YES | | wait/synch/mutex/innodb/fil_system_mutex | YES | YES | | wait/synch/mutex/innodb/flush_list_mutex | YES | YES | | wait/synch/mutex/innodb/fts_bg_threads_mutex | NO | NO | | wait/synch/mutex/innodb/fts_delete_mutex | NO | NO | | wait/synch/mutex/innodb/fts_optimize_mutex | NO | NO | | wait/synch/mutex/innodb/fts_doc_id_mutex | NO | NO | | wait/synch/mutex/innodb/fts_pll_tokenize_mutex | NO | NO | | wait/synch/mutex/innodb/log_flush_order_mutex | YES | YES | | wait/synch/mutex/innodb/hash_table_mutex | YES | YES | | wait/synch/mutex/innodb/ibuf_bitmap_mutex | YES | YES | | wait/synch/mutex/innodb/ibuf_mutex | YES | YES | | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES | YES | | wait/synch/mutex/innodb/log_sys_mutex | YES | YES | | wait/synch/mutex/innodb/log_sys_write_mutex | YES | YES | | wait/synch/mutex/innodb/log_cmdq_mutex | YES | YES | | wait/synch/mutex/innodb/mutex_list_mutex | YES | YES | | wait/synch/mutex/innodb/page_cleaner_mutex | YES | YES | | wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | YES | YES | | wait/synch/mutex/innodb/purge_sys_pq_mutex | YES | YES | | wait/synch/mutex/innodb/recv_sys_mutex | YES | YES | | wait/synch/mutex/innodb/recv_writer_mutex | YES | YES | | wait/synch/mutex/innodb/redo_rseg_mutex | YES | YES | | wait/synch/mutex/innodb/noredo_rseg_mutex | YES | YES | | wait/synch/mutex/innodb/rw_lock_list_mutex | YES | YES | | wait/synch/mutex/innodb/rw_lock_mutex | YES | YES | | wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | YES | YES | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | YES | YES | | wait/synch/mutex/innodb/srv_misc_tmpfile_mutex | YES | YES | | wait/synch/mutex/innodb/srv_monitor_file_mutex | YES | YES | | wait/synch/mutex/innodb/buf_dblwr_mutex | YES | YES | | wait/synch/mutex/innodb/trx_undo_mutex | YES | YES | | wait/synch/mutex/innodb/trx_pool_mutex | YES | YES | | wait/synch/mutex/innodb/trx_pool_manager_mutex | YES | YES | | wait/synch/mutex/innodb/srv_sys_mutex | YES | YES | | wait/synch/mutex/innodb/lock_mutex | YES | YES | | wait/synch/mutex/innodb/lock_wait_mutex | YES | YES | | wait/synch/mutex/innodb/trx_mutex | YES | YES | | wait/synch/mutex/innodb/srv_threads_mutex | YES | YES | | wait/synch/mutex/innodb/rtr_active_mutex | YES | YES | | wait/synch/mutex/innodb/rtr_match_mutex | YES | YES | | wait/synch/mutex/innodb/rtr_path_mutex | YES | YES | | wait/synch/mutex/innodb/rtr_ssn_mutex | YES | YES | | wait/synch/mutex/innodb/trx_sys_mutex | YES | YES | | wait/synch/mutex/innodb/thread_mutex | YES | YES | | wait/synch/mutex/innodb/sync_array_mutex | YES | YES | | wait/synch/mutex/innodb/zip_pad_mutex | YES | YES | | wait/synch/mutex/innodb/row_drop_list_mutex | YES | YES | | wait/synch/mutex/innodb/master_key_id_mutex | YES | YES | +-------------------------------------------------------+---------+-------+ 58 rows in set (0.00 sec)
3.通过更新setup_consumers表来启用等待事件消费者。默认情况下，等待事件消费者是禁用的
mysql> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%'; Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
可以通过查询setup_consumers表来验证是否启用了等待事件消费者。应该启用events_waits_current、events_waits_history和events_waits_history_long消费者。
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 | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
4.启用仪器和使用者后，运行要监视的工作负载。在这个例子中，mysqlslap负载模拟客户端用于模拟工作负载。
[mysql@localhost mysql]$ mysqlslap -uroot -p123456 mysql --auto-generate-sql --concurrency=100 --iterations=10 --number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 11.287 seconds
Minimum number of seconds to run all queries: 11.172 seconds
Maximum number of seconds to run all queries: 12.000 seconds
Number of clients running queries: 100
Average number of queries per client: 10
5.查询wait事件数据。在这个例子中，从events_waits_summary_global_by_event_name表中查询等待事件数据，该表聚合了events_waits_current、events_waits_history和events_waits_history_long表中的数据。数据按事件名称(EVENT_NAME)汇总，这是产生事件的工具的名称。汇总数据包括:
.COUNT_STAR 汇总等待事件的数量。
.SUM_TIMER_WAIT 汇总的定时等待事件的总等待时间。
.MIN_TIMER_WAIT 汇总的定时等待事件的最小等待时间。
.AVG_TIMER_WAIT 汇总的定时等待事件的平均等待时间。
.MAX_TIMER_WAIT 汇总的定时等待事件的最大等待时间。
下面的查询返回工具名称(EVENT_NAME)、等待事件数(COUNT_STAR)和该工具事件的总等待时间(SUM_TIMER_WAIT)。因为等待的时间默认以皮秒(万亿分之一秒)为单位，所以等待时间除以1000000000以毫秒为单位。数据按汇总的等待事件数量降序排列(COUNT_STAR)。可以调整ORDER BY子句，按总等待时间对数据进行排序。
mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS
-> FROM performance_schema.events_waits_summary_global_by_event_name
-> WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'
-> ORDER BY COUNT_STAR DESC;
+--------------------------------------------------+------------+-------------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_MS |
+--------------------------------------------------+------------+-------------------+
| wait/synch/mutex/innodb/buf_pool_mutex | 10169469 | 1019.8992 |
| wait/synch/mutex/innodb/flush_list_mutex | 6774758 | 1142.6652 |
| wait/synch/mutex/innodb/buf_dblwr_mutex | 3387219 | 254.6546 |
| wait/synch/mutex/innodb/log_sys_mutex | 1306588 | 236.5495 |
| wait/synch/mutex/innodb/sync_array_mutex | 892914 | 214.6077 |
| wait/synch/mutex/innodb/rw_lock_list_mutex | 524623 | 17.1254 |
| wait/synch/mutex/innodb/fil_system_mutex | 449955 | 103.0167 |
| wait/synch/mutex/innodb/dict_sys_mutex | 432432 | 44.9851 |
| wait/synch/mutex/innodb/log_sys_write_mutex | 426698 | 32.9908 |
| wait/synch/mutex/innodb/log_flush_order_mutex | 424318 | 76.9258 |
| wait/synch/mutex/innodb/lock_wait_mutex | 423173 | 154.4097 |
| wait/synch/mutex/innodb/row_drop_list_mutex | 423165 | 115.7234 |
| wait/synch/mutex/innodb/trx_mutex | 148083 | 14.0274 |
| wait/synch/mutex/innodb/recalc_pool_mutex | 42731 | 16.2913 |
| wait/synch/mutex/innodb/redo_rseg_mutex | 36442 | 72.2952 |
| wait/synch/mutex/innodb/trx_sys_mutex | 32181 | 595.8351 |
| wait/synch/mutex/innodb/lock_mutex | 20761 | 49.5238 |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 6939 | 2.3340 |
| wait/synch/mutex/innodb/trx_undo_mutex | 6550 | 0.8013 |
| wait/synch/mutex/innodb/trx_pool_mutex | 2272 | 15580.2291 |
| wait/synch/mutex/innodb/innobase_share_mutex | 2015 | 0.2733 |
| wait/synch/mutex/innodb/trx_pool_manager_mutex | 1138 | 0.2584 |
| wait/synch/mutex/innodb/file_format_max_mutex | 1021 | 0.0499 |
| wait/synch/mutex/innodb/page_cleaner_mutex | 567 | 0.0531 |
| wait/synch/mutex/innodb/purge_sys_pq_mutex | 70 | 0.0063 |
| wait/synch/mutex/innodb/srv_sys_mutex | 52 | 0.0071 |
| wait/synch/mutex/innodb/ibuf_mutex | 29 | 0.0022 |
| wait/synch/mutex/innodb/thread_mutex | 27 | 0.0045 |
| wait/synch/mutex/innodb/recv_sys_mutex | 22 | 0.0023 |
| wait/synch/mutex/innodb/buf_pool_zip_mutex | 22 | 0.0020 |
| wait/synch/mutex/innodb/recv_writer_mutex | 1 | 0.0002 |
| wait/synch/mutex/innodb/autoinc_mutex | 1 | 0.0001 |
+--------------------------------------------------+------------+-------------------+
32 rows in set (0.02 sec)
注意:
上述结果集包括启动过程中产生的等待事件数据。要排除这些数据，您可以在启动后和运行工作负载之前立即截断events_waits_summary_global_by_event_name表。但truncate操作本身产生的等待事件数据量可以忽略不计。
mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS
-> FROM performance_schema.events_waits_summary_global_by_event_name
-> WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%'
-> ORDER BY COUNT_STAR DESC;
+-----------------------------------------------+------------+-------------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_MS |
+-----------------------------------------------+------------+-------------------+
| wait/synch/mutex/innodb/buf_pool_mutex | 264 | 0.0381 |
| wait/synch/mutex/innodb/flush_list_mutex | 176 | 0.0316 |
| wait/synch/mutex/innodb/buf_dblwr_mutex | 88 | 0.0079 |
| wait/synch/mutex/innodb/log_sys_mutex | 33 | 0.0079 |
| wait/synch/mutex/innodb/sync_array_mutex | 22 | 0.0042 |
| wait/synch/mutex/innodb/dict_sys_mutex | 11 | 0.0009 |
| wait/synch/mutex/innodb/fil_system_mutex | 11 | 0.0022 |
| wait/synch/mutex/innodb/log_flush_order_mutex | 11 | 0.0020 |
| wait/synch/mutex/innodb/log_sys_write_mutex | 11 | 0.0010 |
| wait/synch/mutex/innodb/lock_wait_mutex | 11 | 0.0073 |
| wait/synch/mutex/innodb/row_drop_list_mutex | 11 | 0.0067 |
| wait/synch/mutex/innodb/recalc_pool_mutex | 1 | 0.0008 |
+-----------------------------------------------+------------+-------------------+
12 rows in set (0.02 sec)