526互联

12 performance_schema_02

发布时间 2023-06-28 00:06:03作者: 站在巨人的肩上Z

当数据库初始化完成并启动时，并非所有的instruments和consumers都启用了，如果你想要检测的事件并没有打开，需要进行设置。

1.  打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项

root@mysqldb 23:01:  [performance_schema]> update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';
Query OK, 217 rows affected (0.00 sec)
Rows matched: 502  Changed: 217  Warnings: 0

    打开等待事件的保存表配置项开关，修改setup_consumers配置表中对应的配置项。

root@mysqldb 23:09:  [performance_schema]> update setup_consumers set enabled='yes' where name like '%wait%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

配置好了之后，就可以查看server当前正在做什么了，可以通过查询events_wait_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件(正在做的事情)

root@mysqldb 23:12:  [performance_schema]> select * from events_waits_current limit 1 \G;
*************************** 1. row ***************************
            THREAD_ID: 13
             EVENT_ID: 3498
         END_EVENT_ID: 3498
           EVENT_NAME: wait/synch/mutex/innodb/dblwr_mutex
               SOURCE: buf0dblwr.cc:396
          TIMER_START: 8855684191275290
            TIMER_END: 8855684191409024
           TIMER_WAIT: 133734
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 140180919516184
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
1 row in set (0.00 sec)

　　*_current表中每个线程只保留一条记录，且一旦线程完成工作，该表就不会再记录该线程的事件信息了。*_history表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息只记录10条，再多就会被覆盖。*_history_log表中记录所有信息的事件信息，但总记录数量是10000行，超过会被覆盖掉。

root@mysqldb 23:25:  [performance_schema]> select thread_id,event_name,event_id,timer_wait from events_waits_history limit 5;
+-----------+----------------------------------------------------+----------+------------+
| thread_id | event_name                                         | event_id | timer_wait |
+-----------+----------------------------------------------------+----------+------------+
|        13 | wait/synch/mutex/innodb/buf_pool_flush_state_mutex |    21401 |      67586 |
|        13 | wait/synch/mutex/innodb/dblwr_mutex                |    21402 |     195568 |
|        13 | wait/synch/mutex/innodb/buf_pool_flush_state_mutex |    21403 |     182626 |
|        13 | wait/synch/mutex/innodb/flush_list_mutex           |    21404 |     497548 |
|        13 | wait/synch/mutex/innodb/buf_pool_flush_state_mutex |    21405 |      61834 |
+-----------+----------------------------------------------------+----------+------------+

这里的timer_wait是皮秒单位，1秒=10^12次方皮秒

查看执行次数最多的等待事件

root@mysqldb 23:41:  [performance_schema]> select  event_name, count_star from events_waits_summary_global_by_event_name order by count_star desc limit 5;
+--------------------------------------------------+------------+
| event_name                                       | count_star |
+--------------------------------------------------+------------+
| wait/synch/mutex/innodb/fil_system_mutex         |     153728 |
| wait/synch/sxlock/innodb/lock_sys_global_rw_lock |      35390 |
| wait/synch/mutex/innodb/lock_wait_mutex          |      31871 |
| wait/synch/sxlock/innodb/hash_table_locks        |      28864 |
| wait/synch/mutex/innodb/flush_list_mutex         |      26712 |
+--------------------------------------------------+------------+
5 rows in set (0.00 sec)

查看执行时间最长的等待事件

root@mysqldb 23:43:  [performance_schema]> select event_name,sum_timer_wait from events_waits_summary_global_by_event_name  order by sum_timer_wait desc limit 5;
+---------------------------------------------------------+------------------+
| event_name                                              | sum_timer_wait   |
+---------------------------------------------------------+------------------+
| idle                                                    | 9898439779271000 |
| wait/synch/cond/mysqlx/scheduler_dynamic_worker_pending | 4683928213912954 |
| wait/io/file/innodb/innodb_data_file                    |     539040525102 |
| wait/io/file/innodb/innodb_dblwr_file                   |      43726901006 |
| wait/io/file/innodb/innodb_log_file                     |      37349772208 |
+---------------------------------------------------------+------------------+
5 rows in set (0.01 sec)

查看事件打开的文件有哪些？

root@mysqldb 23:51:  [performance_schema]> select * from file_instances order by open_count desc  limit 5;
+-------------------------------------------------------------+--------------------------------------+------------+
| FILE_NAME                                                   | EVENT_NAME                           | OPEN_COUNT |
+-------------------------------------------------------------+--------------------------------------+------------+
| /data/mysql/data/undo_001                                   | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysql/data/undo_002                                   | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysql/data/ibdata1                                    | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysql/data/mysql.ibd                                  | wait/io/file/innodb/innodb_data_file |          3 |
| /data/mysql/data/mysql_innodb_cluster_metadata/clusters.ibd | wait/io/file/innodb/innodb_data_file |          3 |
+-------------------------------------------------------------+--------------------------------------+------------+