Mysql等待事件

最新推荐文章于 2024-07-17 09:30:19 发布
最新推荐文章于 2024-07-17 09:30:19 发布
阅读量3.5k 收藏 5
点赞数 1
分类专栏： Mysql 基本管理 Mysql—基础功能学习 文章标签： mysql等待事件
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fly43108622/article/details/86581470
版权 
--查看配置表如下：
mysql> SELECT table_name
         FROM information_schema.tables t
        WHERE t.table_schema='performance_schema'
          AND t.table_name LIKE 'setup%' ;
+-------------------+
| table_name        |
+-------------------+
| setup_actors      |       --配置监控的范围
| setup_consumers   |       --配置监控的类型
| setup_instruments |       --配置等待事件的类型
| setup_objects     |       --配置监控的对象
| setup_timers      |       --配置时间计量单位(皮秒)，8.0版本会被废除
+-------------------+
5 rows in set (0.00 sec)


--1、查看监控的范围，默认监控所有的host，所有的user
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

The setup_actors table has these columns:
    HOST：The host name. This should be a literal name, or '%' to mean “any host.”
    USER：The user name. This should be a literal name, or '%' to mean “any user.”
    ROLE：Unused.
    ENABLED：Whether to enable instrumentation for foreground threads matched by the row. The value is YES or NO.
    HISTORY：Whether to log historical events for foreground threads matched by the row. The value is YES or NO.




--2、查看当前监控的类型(写入到哪些监控的统计表里)和是否开启监控，默认开启如下
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             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
15 rows in set (0.00 sec)



--3、查看等待事件的类型和是否开启了这种类型的记录   
--1022种类型，这里按等待类型查看数量
mysql> SELECT SUBSTRING_INDEX(NAME, '/', 1),COUNT(1)  
    ->       FROM performance_schema.setup_instruments
    ->      GROUP BY 1  
    ->      ORDER BY 2 DESC;
+-------------------------------+----------+
| SUBSTRING_INDEX(NAME, '/', 1) | COUNT(1) |
+-------------------------------+----------+
| memory                        |      377 |
| wait                          |      321 |     --等待事件
| statement                     |      193 |     --语句执行的信息
| stage                         |      129 |     --语句执行阶段的时间统计
| idle                          |        1 |     --空闲
| transaction                   |        1 |
+-------------------------------+----------+
6 rows in set (0.00 sec)




--4、查看监控的对象， 默认对mysql、performance_schema、information_schema三个库的用户对象都不做监控
mysql> SELECT * FROM performance_schema.setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
20 rows in set (0.00 sec)


--查看时间单位计量，都是皮秒(1秒＝1000000000000皮秒)，8.0版本废除的表
mysql> SELECT * FROM performance_schema.setup_timers;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+
5 rows in set, 1 warning (0.00 sec)

 

记录执行语句的等待事件表 ， 官方文档：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-statement-tables.html



--查看可记录执行语句的等待事件的表，如下：
mysql> SELECT table_name
       FROM information_schema.tables t
       WHERE t.table_schema='performance_schema'
       AND t.table_name LIKE 'events_statements%' ;
+----------------------------------------------------+
| table_name                                         |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |   
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
+----------------------------------------------------+
10 rows in set (0.00 sec)



--查看是否开启了配置信息，写入到表中
mysql> SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE '%statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | NO      |
| statements_digest              | YES     |
+--------------------------------+---------+
4 rows in set (0.00 sec)


mysql> SELECT *
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'statement/%';
+---------------------------------------+---------+-------+
| NAME                                  | ENABLED | TIMED |
+---------------------------------------+---------+-------+
| statement/sql/select                  | YES     | YES   |
| statement/sql/create_table            | YES     | YES   |
| .........                             |
| statement/abstract/new_packet         | YES     | YES   |
| statement/abstract/relay_log          | YES     | YES   |
+---------------------------------------+---------+-------+
193 rows in set (0.01 sec)


--可以在my.cnf配置，永久的开启配置信息来记录等待事件执行语句到表中
[mysqld]
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-statements-digest=ON


--查看history历史表记录的数据量，history记录每个线程的最近10条，history_long记录所有线程的最近10000条
mysql> show variables like 'performance_schema_events_statements_history%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
+--------------------------------------------------------+-------+
2 rows in set (0.01 sec)




--查看历史执行时间最长的前5条sql记录，如下：
mysql> SELECT thread_id
              ,event_id
              ,event_name
              ,(timer_end-timer_start)/1000000000000 exec_time_sec
              ,lock_time/1000000000000 lock_time_sce
              ,SUBSTR(sql_text,1,50)
         FROM performance_schema.events_statements_history
        ORDER BY 4 DESC
        LIMIT 5;
+-----------+----------+---------------------------+---------------+---------------+----------------------------------------------------+
| thread_id | event_id | event_name                | exec_time_sec | lock_time_sce | SUBSTR(sql_text,1,50)                              |
+-----------+----------+---------------------------+---------------+---------------+----------------------------------------------------+
|        29 |    15305 | statement/sql/select      |        0.0041 |        0.0037 | EXPLAIN select thread_id,event_id ,                |
|        29 |    15237 | statement/sql/show_status |        0.0018 |        0.0001 | SHOW STATUS                                        |
|        29 |    15189 | statement/sql/show_status |        0.0016 |        0.0001 | SHOW STATUS                                        |
|        32 |      341 | statement/sql/select      |        0.0012 |        0.0001 | SELECT * FROM performance_schema.setup_inst        |
|        32 |      397 | statement/sql/select      |        0.0012 |        0.0002 | SELECT thread_id ,event_id ,event_na               |
+-----------+----------+---------------------------+---------------+---------------+----------------------------------------------------+
5 rows in set (0.00 sec)

 

记录执行语句的等待事件表 ， 官方文档：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-stage-tables.html



--查看可记录语句每步执行时间的等待事件的表，如下：
mysql> SELECT table_name
       FROM information_schema.tables t
       WHERE t.table_schema='performance_schema'
       AND t.table_name LIKE 'events_stages%';
+------------------------------------------------+
| table_name                                     |
+------------------------------------------------+
| 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     |
+------------------------------------------------+
8 rows in set (0.00 sec)



--查看是否开启了配置信息，写入到表中
mysql> SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE '%stages%';
+----------------------------+---------+
| NAME                       | ENABLED |
+----------------------------+---------+
| events_stages_current      | NO      |
| events_stages_history      | NO      |
| events_stages_history_long | NO      |
+----------------------------+---------+
3 rows in set (0.00 sec)


mysql> SELECT *
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'stage%';
+----------------------------------------------------------------------------+---------+-------+
| NAME                                                                       | ENABLED | TIMED |
+----------------------------------------------------------------------------+---------+-------+
| stage/sql/After create                                                     | NO      | NO    |
| stage/sql/allocating local table                                           | NO      | NO    |
| stage/sql/preparing for alter table                                        | NO      | NO    |
| stage/sql/altering table                                                   | NO      | NO    |
| stage/sql/committing alter table to storage engine                         | NO      | NO    |
| ......
| stage/innodb/buffer pool load                                              | YES     | YES   |
+----------------------------------------------------------------------------+---------+-------+
129 rows in set (0.00 sec)


--可以在my.cnf配置，永久的开启配置信息来记录等待事件执行语句到表中
[mysqld]
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON


--查看history历史表记录的数据量，history记录每个线程的最近10条，history_long记录所有线程的最近10000条
mysql> show variables like 'performance_schema_events_stages_history%';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size      | 10    |
+----------------------------------------------------+-------+
2 rows in set (0.00 sec)



--查看历史执行时间最长的前5条每步具体时间，如下：
mysql> SELECT thread_id
             ,event_id
             ,event_name
             ,(timer_end-timer_start)/1000000000000 exec_time_sec
             ,timer_wait/1000000000000 timer_wait_sce
        FROM performance_schema.events_stages_history
       ORDER BY 4 DESC
       LIMIT 5;
+-----------+----------+-------------------------------+---------------+----------------+
| thread_id | event_id | event_name                    | exec_time_sec | timer_wait_sce |
+-----------+----------+-------------------------------+---------------+----------------+
|        24 |        1 | stage/innodb/buffer pool load |        0.6036 |         0.6036 |
|         1 |      200 | stage/sql/System lock         |        0.0081 |         0.0081 |
|         1 |      205 | stage/sql/Opening tables      |        0.0029 |         0.0029 |
|         1 |      199 | stage/sql/Opening tables      |        0.0005 |         0.0005 |
|         1 |      208 | stage/sql/System lock         |        0.0003 |         0.0003 |
+-----------+----------+-------------------------------+---------------+----------------+
5 rows in set (0.00 sec)

 

记录执行语句的等待事件表 ， 官方文档：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-wait-tables.html



--查看可记录语句每步执行时间的等待事件的表，如下：
mysql> SELECT table_name
       FROM information_schema.tables t
       WHERE t.table_schema='performance_schema'
       AND t.table_name LIKE 'events_waits%';
+-----------------------------------------------+
| table_name                                    |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
+-----------------------------------------------+
9 rows in set (0.17 sec)



--查看是否开启了配置信息，写入到表中
mysql> SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE '%waits%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_waits_current      | NO      |
| events_waits_history      | NO      |
| events_waits_history_long | NO      |
+---------------------------+---------+
3 rows in set (0.04 sec)


mysql> SELECT *
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'wait/%';
+--------------------------------------------------------------------+---------+-------+
| NAME                                                               | ENABLED | TIMED |
+--------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc                          | NO      | NO    |
| wait/synch/mutex/sql/LOCK_des_key_file                             | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit                    | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue              | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done                      | NO      | NO    |
......
| wait/lock/metadata/sql/mdl                                         | NO      | NO    |
+--------------------------------------------------------------------+---------+-------+
321 rows in set (0.00 sec)


--可以在my.cnf配置，永久的开启配置信息来记录等待事件执行语句到表中
[mysqld]
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON


--查看history历史表记录的数据量，history记录每个线程的最近10条，history_long记录所有线程的最近10000条
mysql> show variables like 'performance_schema_events_waits_history%';      
+---------------------------------------------------+-------+
| Variable_name                                     | Value |
+---------------------------------------------------+-------+
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size      | 10    |
+---------------------------------------------------+-------+
2 rows in set (0.07 sec)



--查看历史执行时间最长的前5条每步具体时间，如下：
mysql> SELECT thread_id
             ,event_id
             ,event_name
             ,(timer_end-timer_start)/1000000000000 exec_time_sec
             ,timer_wait/1000000000000 timer_wait_sce
        FROM performance_schema.events_waits_history
       ORDER BY 4 DESC
       LIMIT 5;
+-----------+----------+--------------------------------------------+---------------+----------------+
| thread_id | event_id | event_name                                 | exec_time_sec | timer_wait_sce |
+-----------+----------+--------------------------------------------+---------------+----------------+
|        12 |        3 | wait/io/file/innodb/innodb_log_file        |        0.0169 |         0.0169 |
|         6 |      113 | wait/io/file/innodb/innodb_data_file       |        0.0169 |         0.0169 |
|        12 |        8 | wait/io/file/innodb/innodb_log_file        |        0.0034 |         0.0034 |
|        30 |     1242 | wait/io/socket/sql/client_connection       |        0.0001 |         0.0001 |
|         1 |    17170 | wait/synch/mutex/sql/LOCK_connection_count |        0.0000 |         0.0000 |
+-----------+----------+--------------------------------------------+---------------+----------------+
5 rows in set (0.00 sec)

 

--查看sql相应的具体等待事件


--执行一条测试的sql，分析此条sql的等待事件
mysql> SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns;
+----------+
| COUNT(1) |
+----------+
|     3193 |
+----------+
1 row in set, 1 warning (0.08 sec)



--查看上述sql的每个阶段的时间和事件
--说明：events_statements*表的event_id字段和events_stages*表的nesting_event_id字段关联
mysql> SELECT statement.sql_text,
              stages.event_id stage_event_id,
              stages.event_name stage_event_name ,
              stages.timer_wait/1000000000000  stage_timer_wait_sec  --秒
         FROM performance_schema.events_statements_history_long statement
         JOIN performance_schema.events_stages_history_long stages
           ON statement.event_id=stages.nesting_event_id
        WHERE statement.sql_text LIKE 'SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns'
        ORDER BY stages.timer_wait DESC
        LIMIT 10;
+----------------------------------------------------------------+----------------+--------------------------------+----------------------+
| sql_text                                                       | stage_event_id | stage_event_name               | stage_timer_wait_sec |
+----------------------------------------------------------------+----------------+--------------------------------+----------------------+
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          59168 | stage/sql/checking permissions |               0.0064 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          69109 | stage/sql/Sending data         |               0.0052 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          56443 | stage/sql/checking permissions |               0.0032 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          57885 | stage/sql/Opening tables       |               0.0015 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          56371 | stage/sql/starting             |               0.0012 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          65769 | stage/sql/Opening tables       |               0.0012 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          65969 | stage/sql/Opening tables       |               0.0006 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          58929 | stage/sql/checking permissions |               0.0006 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          68261 | stage/sql/Opening tables       |               0.0005 |
| SELECT /*+test_waits*/COUNT(1) FROM information_schema.columns |          66097 | stage/sql/Opening tables       |               0.0004 |
+----------------------------------------------------------------+----------------+--------------------------------+----------------------+
10 rows in set (0.08 sec)


--查看上述第二行的阶段(红色)的具体等待事件，如下：
--说明：events_stages*表的event_id字段和events_waits*表的nesting_event_id字段关联
mysql> SELECT thread_id
             ,event_id
             ,event_name
             ,(timer_end-timer_start) exec_time  --皮秒
             ,timer_wait timer_wait              --皮秒
         FROM performance_schema.events_waits_history_long t
        WHERE t.nesting_event_id = 69109
        ORDER BY timer_wait DESC;
+-----------+----------+-------------------------------------------+-----------+------------+
| thread_id | event_id | event_name                                | exec_time | timer_wait |
+-----------+----------+-------------------------------------------+-----------+------------+
|        29 |    69112 | wait/synch/sxlock/innodb/hash_table_locks |    626410 |     626410 |
|        29 |    69115 | wait/synch/sxlock/innodb/hash_table_locks |    194250 |     194250 |
|        29 |    69114 | wait/synch/sxlock/innodb/hash_table_locks |    156880 |     156880 |
|        29 |    69121 | wait/synch/sxlock/innodb/hash_table_locks |    150960 |     150960 |
|        29 |    69119 | wait/synch/sxlock/innodb/hash_table_locks |    145040 |     145040 |
|        29 |    69116 | wait/synch/sxlock/innodb/hash_table_locks |    125430 |     125430 |
|        29 |    69123 | wait/synch/sxlock/innodb/hash_table_locks |    122100 |     122100 |
|        29 |    69117 | wait/synch/sxlock/innodb/hash_table_locks |    121360 |     121360 |
|        29 |    69113 | wait/synch/sxlock/innodb/hash_table_locks |    119880 |     119880 |
|        29 |    69122 | wait/synch/sxlock/innodb/hash_table_locks |    119510 |     119510 |
|        29 |    69120 | wait/synch/sxlock/innodb/hash_table_locks |    115810 |     115810 |
|        29 |    69118 | wait/synch/sxlock/innodb/hash_table_locks |    110630 |     110630 |
|        29 |    69133 | wait/synch/sxlock/innodb/hash_table_locks |    106930 |     106930 |
|        29 |    69140 | wait/synch/sxlock/innodb/hash_table_locks |    106190 |     106190 |
|        29 |    69126 | wait/synch/sxlock/innodb/hash_table_locks |    105450 |     105450 |
|        29 |    69147 | wait/synch/sxlock/innodb/hash_table_locks |     96570 |      96570 |
|        29 |    69110 | wait/synch/mutex/innodb/trx_mutex         |     90280 |      90280 |
|        29 |    69124 | wait/synch/sxlock/innodb/hash_table_locks |     68080 |      68080 |
|        29 |    69127 | wait/synch/sxlock/innodb/hash_table_locks |     66600 |      66600 |
|        29 |    69144 | wait/synch/sxlock/innodb/hash_table_locks |     65860 |      65860 |
|        29 |    69143 | wait/synch/sxlock/innodb/hash_table_locks |     64750 |      64750 |
|        29 |    69141 | wait/synch/sxlock/innodb/hash_table_locks |     64010 |      64010 |
|        29 |    69132 | wait/synch/sxlock/innodb/hash_table_locks |     63270 |      63270 |
|        29 |    69142 | wait/synch/sxlock/innodb/hash_table_locks |     61050 |      61050 |
|        29 |    69137 | wait/synch/sxlock/innodb/hash_table_locks |     60680 |      60680 |
|        29 |    69145 | wait/synch/sxlock/innodb/hash_table_locks |     60310 |      60310 |
|        29 |    69146 | wait/synch/sxlock/innodb/hash_table_locks |     59940 |      59940 |
|        29 |    69128 | wait/synch/sxlock/innodb/hash_table_locks |     59570 |      59570 |
|        29 |    69134 | wait/synch/sxlock/innodb/hash_table_locks |     58830 |      58830 |
|        29 |    69131 | wait/synch/sxlock/innodb/hash_table_locks |     58830 |      58830 |
|        29 |    69125 | wait/synch/sxlock/innodb/hash_table_locks |     58460 |      58460 |
|        29 |    69129 | wait/synch/sxlock/innodb/hash_table_locks |     58090 |      58090 |
|        29 |    69136 | wait/synch/sxlock/innodb/hash_table_locks |     57350 |      57350 |
|        29 |    69135 | wait/synch/sxlock/innodb/hash_table_locks |     56240 |      56240 |
|        29 |    69139 | wait/synch/sxlock/innodb/hash_table_locks |     55500 |      55500 |
|        29 |    69138 | wait/synch/sxlock/innodb/hash_table_locks |     55130 |      55130 |
|        29 |    69130 | wait/synch/sxlock/innodb/hash_table_locks |     52910 |      52910 |
|        29 |    69111 | wait/synch/mutex/innodb/trx_mutex         |     36630 |      36630 |
+-----------+----------+-------------------------------------------+-----------+------------+
38 rows in set (0.01 sec)

 

翔之天空
关注
专栏目录
【第24天】MYSQL进阶-查询优化- performance_schema系列实战一：利用等待事件排查MySQL性能问题（SQL 小虚竹)
小虚竹的专栏
01-09 1万+
SQL进阶-查询优化- performance_schema系列实战一：利用等待事件排查MySQL性能问题
mysql索引详解
热门推荐
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql性能优化等待事件
u011340468的专栏
05-14 886
1.物理实体关系图 2.Performance_schema系统信息统计表 1.performance_timers表 TIMER_NAME: 计数器的名称，为NULL表示平台不支持，在SETUP_TIMERS表中可使用PERFORMANCE_TIMERS表字段不为NULL的计时器 TIMER_FREQUENCY: 表示每秒对应的计数器单位的数量 TIMER_RESOLUTION: 计时器的精度，表示每个计时器被调用时额外增加的值 TIMER_OVERHEAD: 使用计时器获取事件的开.
mysql 有等待事件_等待事件 wait event
weixin_30928785的博客
02-18 520
衡量数据库状况, 优化数据库等当一个进程连接到数据库后, 进程所经历的种种等待就开始被记录, 并且通过一系列的性能视图进行展示, 通过等待事件用户可以很快发现数据库的性能瓶颈, 从而进行针对性能的优化和分析.v$event_nameoracle 等待事件主要分两类: 空闲等待(idle) 和 非空闲等待(non-idle)空闲等待: 指 oracle 正等待某种工作, 在诊断和优化数据库的时候, ...
openGauss插件使用指南:系统视图—PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_HISTORY
最新发布
weixin_53596073的博客
07-17 840
PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_HISTORY视图。兼容Mysql对应视图，仅在B库创建。EVENTS_STATEMENTS_HISTORY字段。SQL_TEXT用SHA2算法计算的Hash值。因仅记录成功语句，所以该值永远为0。CPU时间（单位：微秒）。语句执行时的schema。连接到该后台的应用名。
mysql 等待事件查询_日常使用查看等待事件的查询sql
weixin_42498766的博客
01-20 800
1.查某个时间段的等待事件总数select trunc(sample_time, ‘mi’), count(1)from gv$active_session_historywhere sample_time >= to_date(‘2020-01-16 01:50:00’, ‘yyyy-mm-dd hh24:mi:ss’)and sample_time < to_date(‘2020-...
mysql 查看等待事件_查看等待事件
weixin_39581845的博客
01-19 548
--v$session_wait视图中的p1、p2、p3表示等待事件的具体含义，如果Wait Event是db file scattered read,那么p1=file_id/p2=block_id/p3=blocks,然后通过dba_extents即可确定出热点对象.--如果是latch free的话,那么p2为闩锁号,它指向v$latch.--求等待事件及其对应的latchcol event...
mysql 查看等待事件_分享两个实用SQL--查看故障时间等待事件、问题sql及会话访问次数...
weixin_30440721的博客
01-27 225
分享两个SQL，这两个SQL只是基础原型，大家自己自定义条件去做一些调整。主要是在前面讲过的dba_hist_active_sess_history相关sql上总结出来的比较实用的sql。DBA_HIST_ACTIVE_SESS_HISTORYDBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-m...
mysql 锁 等待_SQL-mysql锁等待与死锁
weixin_36212334的博客
01-18 732
一 前言本篇是MYSQL高级进阶篇内容第二篇，学习本篇的基础是知识追寻者之前发布过的文章，尤其是《MYSQL架构入门篇》重中之重；公众号：知识追寻者知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)二 锁等待锁等待的意思非常好理解，就是session (事物会话，开启一个事物代表一个会话)A 对 ...
mysql等待事件类型_enq: TM – contention等待事件
weixin_29137599的博客
02-02 219
介绍TM enqueue ，这里的TM 是指TableManipulation,最常见的enqueue 之一， enq: TM – contention 最常见的enqueue 并发争用等待之一。使用TM 锁在下列场景中被申请：在OPS(早期的RAC)中LGWR会以ID1=0 & ID2=0去申请该队列锁来检查DML_LOCKS 在所有实例中是全0还是全非0当一个单表或分区 需要做不...
mysql等待事件类型_【等待事件】User I/O类 等待事件（2.9）--local write wait
weixin_42122838的博客
01-28 251
【等待事件】User I/O类 等待事件(2.9)--local write waitSELECTA.*FROMV$EVENT_NAME AWHERENAMEIN('local write wait'); 造成此等待事件的原因：1)磁盘损坏2)若执行TRUNCATE操作很慢，则可能由于表及其表上的索引的初始化值过大，可以通过SQL语句ALTER INDEX IND_BIG_TEMP REBUILD...
mysql等待事件类型_等待事件之日志等待事件解决办法
weixin_30480281的博客
02-02 190
我坚持每天看3套RAC的AWR，总结了一下。 查找日志等待事件的SQL: 9i:select * from v$event_name where name like '%log%';(大概14个左右) 10g: select name,wait_class from v$event_name where name like '%log%';(大概35个左右) 11g: select name,wa...
mysql 查看等待事件_PostgreSQL等待事件-锁等待分析
weixin_34878397的博客
01-27 1366
阅读使人充实，讨论使人敏捷，写作使人精确。DB印象推荐搜索PostgreSQLOracleMySQL分布式>>>锁等待处理概述锁等待是我们比较常见的问题，无论是Oracle、MySQL还是PostgreSQL，都设计了大量的锁来保证并发操作的数据一致性，同时，当锁等待产生时会以队列方式进行，先到先得，后者依序排队等候。在高并发场景中，锁等待往往在某些凑巧的情况下能发挥非常巨大的杀...
mysql等待事件类型_oracle 常见等待事件及处理方法
weixin_29113833的博客
02-02 159
我们可以通过视图v$session_wait来查看系统当前的等待事件，以及与等待事件相对应的资源的相关信息看书笔记db file scattered read DB ,db file sequential read DB,free buffer waits,log buffer space,log file switch,log file sync我们可以通过视图v$session_wait来查看...
mysql 等待事件查询_等待事件之日志等待事件解决办法
weixin_42532938的博客
01-27 319
我坚持每天看3套RAC的AWR，总结了一下。 查找日志等待事件的SQL: 9i:select * from v$event_name where name like '%log%';(大概14个左右) 10g: select name,wait_class from v$event_name where name like '%log%';(大概35个左右) 11g: select name,wa...
等待事件查询
kf_panda
10-30 732
可以分别从当前状态、最近状态和启动至今三个时间段去看： 　　这三个状态分别对应：v$session/v$session+v$session_wait[当前状态]、v$session_wait_history[最近状态]和v$session_event[启动至今]三个性能视图。 　　从10g开始v$session包含了v$session_wait的所有字段，所以对于当前状态，10g以
mysql 查看等待事件_wait for a undo record等待事件
weixin_30713705的博客
01-27 282
今天在做AWR分析时，碰到了个新的等待事件：wait for a undo recordTop 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent...
mysql 有等待事件_查询数据库等待事件top10
weixin_39966740的博客
02-03 524
--------------------------------------------------------------------------------------------------------1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系select t2.event,round(100 * t2.time_waited / (t1....
mysql 查看等待事件_高速缓冲内等待事件 - Oracle等待事件详细分析_数据库技术_Linux公社-Linux系统门户网站...
weixin_39538500的博客
02-18 152
1、cache buffers lru chain要想查看或修改工作组(LRU+LRUW)进程，始终要持有管理相应工作组的cache buffers lru chain 锁存器，若在此过程中发生争用，则要等待：latch：cache buffers lru chain 事件。在下面两类情况下我们必须首先获得cache buffers lru chain锁存器：(1)进程想要读取还没有装载到内存上的...
mysql 8.0 sql等待事件
09-16
MySQL 8.0引入了一种新的特性，称为SQL等待事件（SQL Wait Events），该特性可以用于分析和诊断MySQL数据库中的性能问题。SQL等待事件允许用户监视和跟踪数据库中发生的等待事件，并找出导致性能瓶颈的原因。 SQL等待事件是指当一个SQL语句执行时，可能会因为某种原因而处于等待状态。这些等待事件可能包括锁等待、磁盘IO等待、网络等待等。通过监视和分析这些等待事件，我们可以了解到MySQL数据库中的性能瓶颈所在。 通过使用SQL等待事件，我们可以获得以下几个方面的信息： 1. 等待事件类型：我们可以获知SQL语句实际上是因为何种原因而处于等待状态，比如等待锁、等待磁盘IO等。 2. 等待事件持续时间：可以获知SQL语句在等待事件中停留的时间长度，这有助于我们判断等待事件是否严重影响了SQL语句的性能。 3. 等待事件计数：我们可以获知每个等待事件发生的频率，从而可以找出导致频繁等待的原因。 4. 相关SQL语句：可以获知导致等待事件的具体SQL语句是哪条，从而可以进一步分析和优化这些SQL语句。 通过对SQL等待事件的详细分析，我们可以发现数据库中的性能瓶颈，并针对性地进行优化，从而提高数据库的整体性能。这对于开发人员和DBA来说都是非常有价值的工具和特性，能够帮助他们更好地分析和调优MySQL数据库。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值