ITPub博客

首页 > 数据库 > MySQL > MySQL5.6 PERFORMANCE_SCHEMA 说明

MySQL5.6 PERFORMANCE_SCHEMA 说明

MySQL 作者:pentium 时间:2019-01-19 22:51:47 0 删除 编辑

通过 sql语句找到在经历什么等待事件!

Statement -> stage -> wait 的三级结构,通过 nesting_event_id 进行关联,它表示某个事件的父 event_id

比如分析包含 count( * ) 的某条 SQL 语句,具体如下: ( 类似于 oracle v$sql, v$sqlstat, v$sqlarea)

SELECT

EVENT_ID,

sql_text

FROM events_statements_history

WHERE sql_text LIKE '%count(*)%' ;

+ ----------+--------------------------------------+

| EVENT_ID | sql_text |

+ ----------+--------------------------------------+

| 1690 | select count ( * ) from chuck.test_slow |

+ ----------+--------------------------------------+

a. 查看每个阶段的时间消耗: (类似于oracle的时间模型 V$SYS_TIME_MODEL  V$SESS_TIME_MODEL)

SELECT

event_id,

EVENT_NAME,

SOURCE,

TIMER_END - TIMER_START

FROM events_stages_history_long

WHERE NESTING_EVENT_ID = 1690 ;

 

+ ----------+--------------------------------+----------------------+-----------------------+

| event_id | EVENT_NAME | SOURCE | TIMER_END - TIMER_START |

+ ----------+--------------------------------+----------------------+-----------------------+

……

| 2647 | stage / sql / Sending data | sql_executor.cc: 192 | 7369072089000 |

 

b. 查看某个阶段的锁等待情况 ( 类似于 oracle v$session_wait)

针对每个 stage 可能出现的锁等待,一个 stage 会对应一个或多个 wait events_waits_history_long 这个表容易爆满 [ 默认阀值 10000] 。由于 select count ( * ) 需要 IO( 逻辑 IO 或者物理 IO) ,所以在 stage / sql / Sending data 阶段会有 io 等待的统计。通过 stage_xxx 表的 event_id 字段与 waits_xxx 表的 nesting_event_id 进行关联。

SELECT

event_id,

event_name,

source,

timer_wait,

object_name ,

index_name,

operation,

nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 2647 ;

+ ----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

| event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |

+ ----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

| 190607 | wait / io / table / sql / handler | handler.cc: 2842 | 1845888 | test_slow | idx_c1 | fetch | 2647 |

 

https://www.cnblogs.com/zhoujinyi/p/5236705.html

MySQL5.6 PERFORMANCE_SCHEMA 说明

背景:

      MySQL 5.5 开始新增一个数据库: PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为 PERFORMANCE_SCHEMA ,而用户是不能创建存储引擎为 PERFORMANCE_SCHEMA 的表。 MySQL5.5 默认是关闭的,需要手动开启,在配置文件里添加:

[mysqld]

performance_schema = ON

查看是否开启:

mysql > show variables like 'performance_schema' ;

+ --------------------+-------+

| Variable_name      | Value |

+ --------------------+-------+

| performance_schema | ON     |

+ --------------------+-------+

MySQL5.6 开始,默认打开,本文就从 MySQL5.6 来说明,在数据库使用当中 PERFORMANCE_SCHEMA 的一些比较常用的功能。具体的信息可以查看 官方文档

相关表信息:

:配置( setup )表:

zjy @performance_schema 10 : 16 : 56 > show tables like '%setup%' ;

+ ----------------------------------------+

| Tables_in_performance_schema ( % setup % ) |

+ ----------------------------------------+

| setup_actors                           |

| setup_consumers                        |

| setup_instruments                      |

| setup_objects                          |

| setup_timers                           |

+ ----------------------------------------+

1 setup_actors :配置用户纬度的监控,默认监控所有用户。

zjy @performance_schema 10 : 19 : 11 > select * from setup_actors;

+ ------+------+------+

| HOST | USER | ROLE |

+ ------+------+------+

| %     | %     | %     |

+ ------+------+------+

2 setup_consumers :配置 events 的消费者类型,即收集的 events 写入到哪些统计表中。

zjy@: performance_schema 10 : 23 : 35 > select * from setup_consumers;

+ --------------------------------+---------+

| NAME                           | ENABLED |

+ --------------------------------+---------+

| events_stages_current          | NO      |

| events_stages_history          | NO      |

| events_stages_history_long     | NO      |

| events_statements_current      | YES     |

| events_statements_history      | NO      |

| events_statements_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 列为 YES 。如:

zjy @performance_schema 10 : 25 : 02 > update setup_consumers set ENABLED = 'YES' where NAME in ( 'events_stages_current' , 'events_waits_current' );

Query OK, 2 rows affected ( 0.00 sec)

更新完后立即生效,但是服务器重启之后又会变回默认值,要永久生效需要在配置文件里添加:

[mysqld]

#performance_schema

performance_schema_consumer_events_waits_current = on

performance_schema_consumer_events_stages_current = on

performance_schema_consumer_events_statements_current = on

performance_schema_consumer_events_waits_history = on

performance_schema_consumer_events_stages_history = on

performance_schema_consumer_events_statements_history = on

即在这些表的前面加上: performance_schema_consumer_xxx 。表 setup_consumers 里面的值有个层级关系:

global_instrumentation > thread_instrumentation = statements_digest > events_stages_ current = events_statements_current = events_waits_current > events_stages_ history = events_statements_history = events_waits_history > events_stages_ history_long = events_statements_history_long = events_waits_history_long

只有上一层次的为 YES ,才会继续检查该本层为 YES or NO global_instrumentation 是最高级别 consumer ,如果它设置为 NO ,则所有的 consumer 都会忽略。其中 history history_long 存的是 current 表的历史记录条数, history 表记录了每个线程最近等待的 10 个事件,而 history_long 表则记录了最近所有线程产生的 10000 个事件,这里的 10 10000 都是可以配置的。这三个表表结构相同, history history_long 表数据都来源于 current 表。长度通过控制参数:

zjy @performance_schema 11 : 10 : 03 > show variables like 'performance_schema%history%size' ;

+ --------------------------------------------------------+-------+

| Variable_name                                          | Value |

+ --------------------------------------------------------+-------+

| performance_schema_events_stages_history_long_size     | 10000 |

| performance_schema_events_stages_history_size          | 10     |

| performance_schema_events_statements_history_long_size | 10000 |

| performance_schema_events_statements_history_size      | 10     |

| performance_schema_events_waits_history_long_size      | 10000 |

| performance_schema_events_waits_history_size           | 10     |

+ --------------------------------------------------------+-------+

3 setup_instruments :配置具体的 instrument ,主要包含 4 大类: idle stage/xxx statement/xxx wait/xxx

zjy @performance_schema 10 : 56 : 35 > select name, count ( * ) from setup_instruments group by LEFT (name, 5 );

+ ---------------------------------+----------+

| name                             | count ( * ) |

+ ---------------------------------+----------+

| idle                            |         1 |

| stage / sql / After create           |       111 |

| statement / sql / select             |       179 |

| wait / synch / mutex / sql / PAGE::lock |       296 |

+ ---------------------------------+----------+

idle 表示 socket 空闲的时间, stage 类表示语句的每个执行阶段的统计, statement 类统计语句维度的信息, wait 类统计各种等待事件,比如 IO mutux spin_lock,condition 等。

4 setup_objects :配置监控对象,默认对 mysql performance_schema information_schema 中的表都不监控,而其它 DB 的所有表都监控。

zjy @performance_schema 11 : 00 : 18 > select * from setup_objects;

+ -------------+--------------------+-------------+---------+-------+

| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |

+ -------------+--------------------+-------------+---------+-------+

| TABLE        | mysql              | %            | NO      | NO    |

| TABLE        | performance_schema | %            | NO      | NO    |

| TABLE        | information_schema | %            | NO      | NO    |

| TABLE        | %                   | %            | YES      | YES    |

+ -------------+--------------------+-------------+---------+-------+

5 setup_timers :配置每种类型指令的统计时间单位。 MICROSECOND 表示统计单位是微妙, CYCLE 表示统计单位是时钟周期,时间度量与 CPU 的主频有关, NANOSECOND 表示统计单位是纳秒。但无论采用哪种度量单位,最终统计表中统计的时间都会装换到皮秒。( 1 秒= 1000000000000 皮秒)

zjy @performance_schema 11 : 05 : 12 > select * from setup_timers;

+ -----------+-------------+

| NAME      | TIMER_NAME  |

+ -----------+-------------+

| idle      | MICROSECOND |

| wait      | CYCLE       |

| stage     | NANOSECOND  |

| statement | NANOSECOND  |

+ -----------+-------------+

instance

1 cond_instances :条件等待对象实例

表中记录了系统中使用的条件变量的对象, OBJECT_INSTANCE_BEGIN 为对象的内存地址。

2 file_instances :文件实例

表中记录了系统中打开了文件的对象,包括 ibdata 文件, redo 文件, binlog 文件,用户的表文件等, open_count 显示当前文件打开的数目,如果重来没有打开过,不会出现在表中。

zjy @performance_schema 11 : 20 : 04 > select * from file_instances limit 2 , 5 ;

+ ---------------------------------+--------------------------------------+------------+

| FILE_NAME                        | EVENT_NAME                           | OPEN_COUNT |

+ ---------------------------------+--------------------------------------+------------+

| / var / lib / mysql / mysql / plugin.frm | wait / io / file / sql / FRM                 |           |

| / var / lib / mysql / mysql / plugin.MYI | wait / io / file / myisam / kfile            |           1 |

| / var / lib / mysql / mysql / plugin.MYD | wait / io / file / myisam / dfile            |            1 |

| / var / lib / mysql / ibdata1          | wait / io / file / innodb / innodb_data_file |           2 |

| / var / lib / mysql / ib_logfile0      | wait / io / file / innodb / innodb_log_file  |           2 |

+ ---------------------------------+--------------------------------------+------------+

3 mutex_instances 互斥同步对象实例

表中记录了系统中使用互斥量对象的所有记录,其中 name 为: wait/synch/mutex/* LOCKED_BY_THREAD_ID 显示哪个线程正持有 mutex ,若没有线程持有,则为 NULL

4 rwlock_instances   读写锁同步对象实例

表中记录了系统中使用读写锁对象的所有记录,其中 name wait/synch/rwlock/* WRITE_LOCKED_BY_THREAD_ID 为正在持有该对象的 thread_id ,若没有线程持有,则为 NULL READ_LOCKED_BY_COUNT 为记录了同时有多少个读者持有读锁。(通过 events_waits_current 表可以知道,哪个线程在等待锁;通过 rwlock_instances 知道哪个线程持有锁。 rwlock_instances 的缺陷是,只能记录持有写锁的线程,对于读锁则无能为力)。

5 socket_instances 活跃会话对象实例
表中记录了 thread_id,socket_id,ip port ,其它表可以通过 thread_id socket_instance 进行关联,获取 IP-PORT 信息,能够与应用对接起来。
event_name
主要包含 3 类:
wait/io/socket/sql/server_unix_socket
,服务端 unix 监听 socket
wait/io/socket/sql/server_tcpip_socket
,服务端 tcp 监听 socket
wait/io/socket/sql/client_connection
,客户端 socket

Wait

1 events_waits_current :记录了当前线程等待的事件

2 events_waits_history :记录了每个线程最近等待的 10 个事件

3 events_waits_history_long :记录了最近所有线程产生的 10000 个事件

表结构定义如下:

CREATE TABLE `events_waits_current` (

  `THREAD_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 线程 ID' ,

  `EVENT_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 当前线程的事件 ID ,和 THREAD_ID 确定唯一 ' ,

  `END_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 当事件开始时,这一列被设置为 NULL 。当事件结束时,再更新为当前的事件 ID' ,

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `SOURCE` varchar ( 64 ) DEFAULT NULL COMMENT ' 该事件产生时的源码文件 ' ,

  `TIMER_START` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件开始时间(皮秒) ' ,

  `TIMER_END` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件结束结束时间(皮秒) ' ,

  `TIMER_WAIT` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件等待时间(皮秒) ' ,

  `SPINS` int ( 10 ) unsigned DEFAULT NULL COMMENT '' ,

  `OBJECT_SCHEMA` varchar ( 64 ) DEFAULT NULL COMMENT ' 库名 ' ,

  ` OBJECT_NAME ` varchar ( 512 ) DEFAULT NULL COMMENT ' 文件名、表名、 IP:SOCK ' ,

  `OBJECT_TYPE` varchar ( 64 ) DEFAULT NULL COMMENT 'FILE TABLE TEMPORARY TABLE' ,

  `INDEX_NAME` varchar ( 64 ) DEFAULT NULL COMMENT ' 索引名 ' ,

  `OBJECT_INSTANCE_BEGIN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 内存地址 ' ,

  `NESTING_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 该事件对应的父事件 ID' ,

  `NESTING_EVENT_TYPE` enum( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT ' 父事件类型 (STATEMENT, STAGE, WAIT)' ,

  `OPERATION` varchar ( 32 ) NOT NULL COMMENT ' 操作类型( lock, read, write ' ,

  `NUMBER_OF_BYTES` bigint ( 20 ) DEFAULT NULL COMMENT '' ,

  `FLAGS` int ( 10 ) unsigned DEFAULT NULL COMMENT ' 标记 '

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

Stage  

1 events_stages_current :记录了当前线程所处的执行阶段

2 events_stages_history :记录了当前线程所处的执行阶段 10 条历史记录

3 events_stages_history_long :记录了当前线程所处的执行阶段 10000 条历史记录

表结构定义如下:

CREATE TABLE `events_stages_current` (

  `THREAD_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 线程 ID' ,

  `EVENT_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件 ID' ,

  `END_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 结束事件 ID' ,

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `SOURCE` varchar ( 64 ) DEFAULT NULL COMMENT ' 源码位置 ' ,

  `TIMER_START` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件开始时间(皮秒) ' ,

  `TIMER_END` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件结束结束时间(皮秒) ' ,

  `TIMER_WAIT` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件等待时间(皮秒) ' ,

  `NESTING_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 该事件对应的父事件 ID' ,

  `NESTING_EVENT_TYPE` enum( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT ' 父事件类型 (STATEMENT, STAGE, WAIT)'

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

Statement

1 events_statements_current :通过 thread_id+event_id 可以唯一确定一条记录。 Statments 表只记录最顶层的请求, SQL 语句或是 COMMAND ,每条语句一行。 event_name 形式为 statement/sql/* ,或 statement/com/*

2 events_statements_history

3 events_statements_history_long

表结构定义如下:

CREATE TABLE `events_statements_current` (

  `THREAD_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 线程 ID' ,

  `EVENT_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件 ID' ,

  `END_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 结束事件 ID' ,

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `SOURCE` varchar ( 64 ) DEFAULT NULL COMMENT ' 源码位置 ' ,

  `TIMER_START` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件开始时间(皮秒) ' ,

  `TIMER_END` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件结束结束时间(皮秒) ' ,

  `TIMER_WAIT` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 事件等待时间(皮秒) ' ,

  `LOCK_TIME` bigint ( 20 ) unsigned NOT NULL COMMENT ' 锁时间 ' ,

  `SQL_TEXT` longtext COMMENT ' 记录 SQL 语句 ' ,

  `DIGEST` varchar ( 32 ) DEFAULT NULL COMMENT ' SQL_TEXT MD5 产生的 32 位字符串 ' ,

  `DIGEST_TEXT` longtext COMMENT ' 将语句中值部分用问号代替,用于 SQL 语句归类 ' ,

  `CURRENT_SCHEMA` varchar ( 64 ) DEFAULT NULL COMMENT ' 默认的数据库名 ' ,

  `OBJECT_TYPE` varchar ( 64 ) DEFAULT NULL COMMENT ' 保留字段 ' ,

  `OBJECT_SCHEMA` varchar ( 64 ) DEFAULT NULL COMMENT ' 保留字段 ' ,

  ` OBJECT_NAME ` varchar ( 64 ) DEFAULT NULL COMMENT ' 保留字段 ' ,

  `OBJECT_INSTANCE_BEGIN` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 内存地址 ' ,

  `MYSQL_ERRNO` int ( 11 ) DEFAULT NULL COMMENT '' ,

  `RETURNED_SQLSTATE` varchar ( 5 ) DEFAULT NULL COMMENT '' ,

  `MESSAGE_TEXT` varchar ( 128 ) DEFAULT NULL COMMENT ' 信息 ' ,

  `ERRORS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 错误数目 ' ,

  `WARNINGS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 警告数目 ' ,

  `ROWS_AFFECTED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 影响的数目 ' ,

  `ROWS_SENT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 返回的记录数 ' ,

  `ROWS_EXAMINED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 读取扫描的记录数目 ' ,

  `CREATED_TMP_DISK_TABLES` bigint ( 20 ) unsigned NOT NULL COMMENT ' 创建磁盘临时表数目 ' ,

  `CREATED_TMP_TABLES` bigint ( 20 ) unsigned NOT NULL COMMENT ' 创建临时表数目 ' ,

  `SELECT_FULL_JOIN` bigint ( 20 ) unsigned NOT NULL COMMENT 'join 时,第一个表为全表扫描的数目 ' ,

  `SELECT_FULL_RANGE_JOIN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 引用表采用 range 方式扫描的数目 ' ,

  `SELECT_RANGE` bigint ( 20 ) unsigned NOT NULL COMMENT 'join 时,第一个表采用 range 方式扫描的数目 ' ,

  `SELECT_RANGE_CHECK` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `SELECT_SCAN` bigint ( 20 ) unsigned NOT NULL COMMENT 'join 时,第一个表位全表扫描的数目 ' ,

  `SORT_MERGE_PASSES` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `SORT_RANGE` bigint ( 20 ) unsigned NOT NULL COMMENT ' 范围排序数目 ' ,

  `SORT_ROWS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 排序的记录数目 ' ,

  `SORT_SCAN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 全表排序数目 ' ,

  `NO_INDEX_USED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 没有使用索引数目 ' ,

  `NO_GOOD_INDEX_USED` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `NESTING_EVENT_ID` bigint ( 20 ) unsigned DEFAULT NULL COMMENT ' 该事件对应的父事件 ID' ,

  `NESTING_EVENT_TYPE` enum( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT ' 父事件类型 (STATEMENT, STAGE, WAIT)'

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

Connection

1 users :记录用户连接数信息

2 hosts :记录了主机连接数信息

3 accounts :记录了用户主机连接数信息

zjy @performance_schema 12 : 03 : 27 > select * from users;

+ ------------------+---------------------+-------------------+

| USER              | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

+ ------------------+---------------------+-------------------+

| debian - sys - maint |                    |                 36 |

| zjy              |                    1 |              22285 |

| dchat_php        |                    |              37864 |

| dxyslave          |                    2 |                  9 |

| nagios           |                    |              10770 |

| dchat_data       |                  140 |            2233023 |

| NULL              |                    |              15866 |

| dchat_api        |                   160 |            2754212 |

| mha_data         |                    1 |                 36 |

| backup            |                    |                 15 |

| cacti            |                    |               4312 |

| kol              |                    10 |             172414 |

+ ------------------+---------------------+-------------------+

12 rows in set ( 0.00 sec)

 

zjy @performance_schema 12 : 03 : 34 > select * from hosts;

+ -----------------+---------------------+-------------------+

| HOST             | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

+ -----------------+---------------------+-------------------+

| 192.168 . 100.218 |                  150 |            2499422 |

| 192.168 . 100.240 |                   10 |             172429 |

| 192.168 . 100.139 |                     |                698 |

| 192.168 . 100.21   |                    |                  2 |

| 192.168 . 100.220 |                  150 |            2526136 |

| 192.168 . 100.25   |                    1 |                  7 |

| NULL             |                     |              15867 |

| 192.168 . 100.241 |                    |              21558 |

| 192.168 . 100.191 |                    1 |                 34 |

| localhost       |                    |              10807 |

| 192.168 . 100.118 |                    1 |                  2 |

| 192.168 . 100.251 |                    |               4312 |

| 192.168 . 100.23   |                    1 |                 31 |

| 192.168 . 100.193 |                    |                 15 |

+ -----------------+---------------------+-------------------+

14 rows in set ( 0.01 sec)

 

zjy @performance_schema 12 : 05 : 21 > select * from accounts;

+ ------------------+-----------------+---------------------+-------------------+

| USER              | HOST            | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

+ ------------------+-----------------+---------------------+-------------------+

| cacti            | 192.168 . 100.251 |                    |               4313 |

| debian - sys - maint | localhost       |                    |                 36 |

| backup            | 192.168 . 100.193 |                    |                 15 |

| dchat_api        | 192.168 . 100.220 |                   80 |            1382585 |

| dchat_php        | 192.168 . 100.220 |                    |              20292 |

| zjy              | 192.168 . 100.139 |                    |                698 |

| zjy              | 192.168 . 100.241 |                    |              21558 |

| mha_data         | 192.168 . 100.191 |                    1 |                 34 |

| dxyslave         | 192.168 . 100.118 |                    1 |                  2 |

| kol              | 192.168 . 100.240 |                   10 |             172431 |

| dxyslave         | 192.168 . 100.25   |                    1 |                  7 |

| dchat_data       | 192.168 . 100.218 |                    70 |            1109974 |

| zjy              | 192.168 . 100.23   |                    1 |                 31 |

| dchat_php        | 192.168 . 100.218 |                    |              17572 |

| dchat_data       | 192.168 . 100.220 |                   70 |            1123306 |

| NULL              | NULL             |                    |              15868 |

| mha_data         | 192.168 . 100.21   |                    |                  2 |

| dchat_api        | 192.168 . 100.218 |                   80 |            1371918 |

| nagios           | localhost       |                    |              10771 |

+ ------------------+-----------------+---------------------+-------------------+

View Code

七: Summary   Summary 表聚集了各个维度的统计信息包括表维度,索引维度,会话维度,语句维度和锁维度的统计信息

1 events_waits_summary_global_by_event_name :按等待事件类型聚合,每个事件一条记录

CREATE TABLE `events_waits_summary_global_by_event_name` (

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `COUNT_STAR` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件计数 ' ,

  `SUM_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的等待时间 ' ,

  `MIN_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最小等待时间 ' ,

  `AVG_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 平均等待时间 ' ,

  `MAX_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最大等待时间 '

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

2 events_waits_summary_by_instance :按等待事件对象聚合,同一种等待事件,可能有多个实例,每个实例有不同的内存地址,因此
event_name+object_instance_begin
唯一确定一条记录。

CREATE TABLE `events_waits_summary_by_instance` (

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `OBJECT_INSTANCE_BEGIN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 内存地址 ' ,

  `COUNT_STAR` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件计数 ' ,

  `SUM_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的等待时间 ' ,

  `MIN_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最小等待时间 ' ,

  `AVG_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 平均等待时间 ' ,

  `MAX_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最大等待时间 '

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

3 events_waits_summary_by_thread_by_event_name :按每个线程和事件来统计, thread_id+event_name 唯一确定一条记录。

CREATE TABLE `events_waits_summary_by_thread_by_event_name` (

  `THREAD_ID` bigint ( 20 ) unsigned NOT NULL COMMENT ' 线程 ID' ,

  `EVENT_NAME` varchar ( 128 ) NOT NULL COMMENT ' 事件名称 ' ,

  `COUNT_STAR` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件计数 ' ,

  `SUM_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的等待时间 ' ,

  `MIN_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最小等待时间 ' ,

  `AVG_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 平均等待时间 ' ,

  `MAX_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最大等待时间 '

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

4 events_stages_summary_global_by_event_name :按事件阶段类型聚合,每个事件一条记录,表结构同上。

5 events_stages_summary_by_thread_by_event_name :按每个线程和事件来阶段统计,表结构同上。

6 events_statements_summary_by_digest :按照事件的语句进行聚合。

CREATE TABLE `events_statements_summary_by_digest` (

  `SCHEMA_NAME` varchar ( 64 ) DEFAULT NULL COMMENT ' 库名 ' ,

  `DIGEST` varchar ( 32 ) DEFAULT NULL COMMENT ' SQL_TEXT MD5 产生的 32 位字符串。如果为 consumer 表中没有打开 statement_digest 选项,则为 NULL' ,

  `DIGEST_TEXT` longtext COMMENT ' 将语句中值部分用问号代替,用于 SQL 语句归类。如果为 consumer 表中没有打开 statement_digest 选项,则为 NULL ' ,

  `COUNT_STAR` bigint ( 20 ) unsigned NOT NULL COMMENT ' 事件计数 ' ,

  `SUM_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的等待时间 ' ,

  `MIN_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最小等待时间 ' ,

  `AVG_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 平均等待时间 ' ,

  `MAX_TIMER_WAIT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 最大等待时间 ' ,

  `SUM_LOCK_TIME` bigint ( 20 ) unsigned NOT NULL COMMENT ' 锁时间总时长 ' ,

  `SUM_ERRORS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 错误数的总 ' ,

  `SUM_WARNINGS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 警告的总数 ' ,

  `SUM_ROWS_AFFECTED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 影响的总数目 ' ,

  `SUM_ROWS_SENT` bigint ( 20 ) unsigned NOT NULL COMMENT ' 返回总数目 ' ,

  `SUM_ROWS_EXAMINED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的扫描的数目 ' ,

  `SUM_CREATED_TMP_DISK_TABLES` bigint ( 20 ) unsigned NOT NULL COMMENT ' 创建磁盘临时表的总数目 ' ,

  `SUM_CREATED_TMP_TABLES` bigint ( 20 ) unsigned NOT NULL COMMENT ' 创建临时表的总数目 ' ,

  `SUM_SELECT_FULL_JOIN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 第一个表全表扫描的总数目 ' ,

  `SUM_SELECT_FULL_RANGE_JOIN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 总的采用 range 方式扫描的数目 ' ,

  `SUM_SELECT_RANGE` bigint ( 20 ) unsigned NOT NULL COMMENT ' 第一个表采用 range 方式扫描的总数目 ' ,

  `SUM_SELECT_RANGE_CHECK` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `SUM_SELECT_SCAN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 第一个表位全表扫描的总数目 ' ,

  `SUM_SORT_MERGE_PASSES` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `SUM_SORT_RANGE` bigint ( 20 ) unsigned NOT NULL COMMENT ' 范围排序总数 ' ,

  `SUM_SORT_ROWS` bigint ( 20 ) unsigned NOT NULL COMMENT ' 排序的记录总数目 ' ,

  `SUM_SORT_SCAN` bigint ( 20 ) unsigned NOT NULL COMMENT ' 第一个表排序扫描总数目 ' ,

  `SUM_NO_INDEX_USED` bigint ( 20 ) unsigned NOT NULL COMMENT ' 没有使用索引总数 ' ,

  `SUM_NO_GOOD_INDEX_USED` bigint ( 20 ) unsigned NOT NULL COMMENT '' ,

  `FIRST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT ' 第一次执行时间 ' ,

  `LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT ' 最后一次执行时间 '

) ENGINE = PERFORMANCE_SCHEMA DEFAULT CHARSET = utf8

7 events_statements_summary_global_by_event_name :按照事件的语句进行聚合。表结构同上。

8 events_statements_summary_by_thread_by_event_name :按照线程和事件的语句进行聚合,表结构同上。

9 file_summary_by_instance :按事件类型统计( 物理 IO 维度

10 file_summary_by_event_name :具体文件统计( 物理 IO 维度

9 10 一起说明:

统计 IO 操作: COUNT_STAR SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

统计读       COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

统计写       COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

统计其他 IO 事件,比如 create delete open close 等: COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC

11 table_io_waits_summary_by_table :根据 wait/io/table/sql/handler ,聚合每个表的 I/O 操作( 逻辑 IO 纬度

统计 IO 操作: COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT  

统计读       COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ

              COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH

统计写       COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE

INSERT 统计,相应的还有 DELETE UPDATE 统计: COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT

12 table_io_waits_summary_by_index_usage table_io_waits_summary_by_table 类似,按索引维度统计

13 table_lock_waits_summary_by_table :聚合了表锁等待事件,包括 internal lock external lock

internal lock 通过 SQL 层函数 thr_lock 调用, OPERATION 值为:
read normal read with shared locks read high priority read no insert write allow write write concurrent insert write delayed write low priority write normal
external lock 则通过接口函数 handler::external_lock 调用存储引擎层, OPERATION 列的值为: read external write external

14 Connection Summaries account user host

events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name 
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name 
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name

15 socket_summary_by_instance socket_summary_by_event_name socket 聚合统计表。

:其他相关表

1 performance_timers :系统支持的统计时间单位

2 threads :监视服务端的当前运行的线程

统计应用:

      关于 SQL 维度的统计信息主要集中在 events_statements_summary_by_digest 表中,通过将 SQL 语句抽象出 digest ,可以统计某类 SQL 语句在各个维度的统计信息

1 ,哪个 SQL 执行最多:

zjy @performance_schema 11 : 36 : 22 > SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1 \G

*************************** 1 . row ***************************

      SCHEMA_NAME : dchat

      DIGEST_TEXT : SELECT ...

       COUNT_STAR : 1161210102

    SUM_ROWS_SENT: 1161207842

SUM_ROWS_EXAMINED:

       FIRST_SEEN : 2016 - 02 - 17 00 : 36 : 46

        LAST_SEEN : 2016 - 03 - 07 11 : 36 : 29

各个字段的注释可以看上面的表结构说明:从 2 17 号到 3 7 号该 SQL 执行了 1161210102 次。

2 ,哪个 SQL 平均响应时间最多:

zjy @performance_schema 11 : 36 : 28 > SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1 \G

*************************** 1 . row ***************************

      SCHEMA_NAME : dchat

      DIGEST_TEXT : SELECT ...

       COUNT_STAR: 1

   AVG_TIMER_WAIT : 273238183964000

    SUM_ROWS_SENT: 50208

SUM_ROWS_EXAMINED: 5565651

       FIRST_SEEN : 2016 - 02 - 22 13 : 27 : 33

        LAST_SEEN : 2016 - 02 - 22 13 : 27 : 33

各个字段的注释可以看上面的表结构说明:从 2 17 号到 3 7 号该 SQL 平均响应时间 273238183964000 皮秒( 1000000000000 皮秒 =1 秒)

3 ,哪个 SQL 扫描的行数最多:

SUM_ROWS_EXAMINED

4 ,哪个 SQL 使用的临时表最多:

SUM_CREATED_TMP_DISK_TABLES SUM_CREATED_TMP_TABLES

5 ,哪个 SQL 返回的结果集最多:

SUM_ROWS_SENT

6 ,哪个 SQL 排序数最多:

SUM_SORT_ROWS

通过上述指标我们可以间接获得某类 SQL 的逻辑 IO(SUM_ROWS_EXAMINED) CPU 消耗 (SUM_SORT_ROWS) ,网络带宽 (SUM_ROWS_SENT) 的对比。

通过 file_summary_by_instance 表,可以获得系统运行到现在,哪个文件 ( ) 物理 IO 最多,这可能意味着这个表经常需要访问磁盘 IO

7 ,哪个表、文件逻辑 IO 最多(热数据):

zjy @performance_schema 12 : 16 : 18 > SELECT FILE_NAME ,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2 \G

*************************** 1 . row ***************************

                FILE_NAME : / var / lib / mysql / ibdata1  # 文件

               EVENT_NAME: wait / io / file / innodb / innodb_data_file

               COUNT_READ: 544

 SUM_NUMBER_OF_BYTES_READ: 10977280

              COUNT_WRITE: 3700729

SUM_NUMBER_OF_BYTES_WRITE: 1433734217728

*************************** 2 . row ***************************

                FILE_NAME : / var / lib / mysql / dchat / fans.ibd   #

               EVENT_NAME: wait / io / file / innodb / innodb_data_file

               COUNT_READ: 9370680

 SUM_NUMBER_OF_BYTES_READ: 153529188352

              COUNT_WRITE: 67576376

SUM_NUMBER_OF_BYTES_WRITE: 1107815432192

8 ,哪个索引使用最多:

zjy @performance_schema 12 : 18 : 42 > SELECT OBJECT_NAME , INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit 1 ;

+ -------------+------------+-------------+--------------+--------------+--------------+

| OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |

+ -------------+------------+-------------+--------------+--------------+--------------+

| fans         | PRIMARY     | 29002695158 |             |     296373434 |             |

+ -------------+------------+-------------+--------------+--------------+--------------+

1 row in set ( 0.29 sec)

通过 table_io_waits_summary_by_index_usage 表,可以获得系统运行到现在,哪个表的具体哪个索引 ( 包括主键索引,二级索引 ) 使用最多。

9 ,哪个索引没有使用过:

zjy @performance_schema 12 : 23 : 22 > SELECT OBJECT_SCHEMA, OBJECT_NAME , INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA, OBJECT_NAME ;

10 ,哪个等待事件消耗的时间最多:

zjy @performance_schema 12 : 25 : 22 > SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT 1 ;

11 ,类似 profiling 功能:

分析具体某条 SQL ,该 SQL 在执行各个阶段的时间消耗,通过 events_statements_xxx 表和 events_stages_xxx 表,就可以达到目的。两个表通过 event_id nesting_event_id 关联, stages 表的 nesting_event_id 为对应 statements 表的 event_id ;针对每个 stage 可能出现的锁等待,一个 stage 会对应一个或多个 wait ,通过 stage_xxx 表的 event_id 字段与 waits_xxx 表的 nesting_event_id 进行关联。如:

比如分析包含 count( * ) 的某条 SQL 语句,具体如下:

 

SELECT

EVENT_ID,

sql_text

FROM events_statements_history

WHERE sql_text LIKE '%count(*)%' ;

+ ----------+--------------------------------------+

| EVENT_ID | sql_text |

+ ----------+--------------------------------------+

| 1690 | select count ( * ) from chuck.test_slow |

+ ----------+--------------------------------------+

首先得到了语句的 event_id 1690 ,通过查找 events_stages_xxx nesting_event_id 1690 的记录,可以达到目的。

 

a. 查看每个阶段的时间消耗:

SELECT

event_id,

EVENT_NAME,

SOURCE,

TIMER_END - TIMER_START

FROM events_stages_history_long

WHERE NESTING_EVENT_ID = 1690 ;

+ ----------+--------------------------------+----------------------+-----------------------+

| event_id | EVENT_NAME | SOURCE | TIMER_END - TIMER_START |

+ ----------+--------------------------------+----------------------+-----------------------+

| 1691 | stage / sql / init | mysqld.cc: 990 | 316945000 |

| 1693 | stage / sql / checking permissions | sql_parse.cc: 5776 | 26774000 |

| 1695 | stage / sql / Opening tables | sql_base.cc: 4970 | 41436934000 |

| 2638 | stage / sql / init | sql_select.cc: 1050 | 85757000 |

| 2639 | stage / sql / System lock | lock.cc: 303 | 40017000 |

| 2643 | stage / sql / optimizing | sql_optimizer.cc: 138 | 38562000 |

| 2644 | stage / sql / statistics | sql_optimizer.cc: 362 | 52845000 |

| 2645 | stage / sql / preparing | sql_optimizer.cc: 485 | 53196000 |

| 2646 | stage / sql / executing | sql_executor.cc: 112 | 3153000 |

| 2647 | stage / sql / Sending data | sql_executor.cc: 192 | 7369072089000 |

| 4304138 | stage / sql / end | sql_select.cc: 1105 | 19920000 |

| 4304139 | stage / sql / query end | sql_parse.cc: 5463 | 44721000 |

| 4304145 | stage / sql / closing tables | sql_parse.cc: 5524 | 61723000 |

| 4304152 | stage / sql / freeing items | sql_parse.cc: 6838 | 455678000 |

| 4304155 | stage / sql / logging slow query | sql_parse.cc: 2258 | 83348000 |

| 4304159 | stage / sql / cleaning up | sql_parse.cc: 2163 | 4433000 |

+ ----------+--------------------------------+----------------------+-----------------------+

通过间接关联,我们能分析得到 SQL 语句在每个阶段的时间消耗,时间单位以皮秒表示。这里展示的结果很类似 profiling 功能,有了 performance schema ,就不再需要 profiling 这个功能了。另外需要注意的是,由于默认情况下 events_stages_history 表中只为每个连接记录了最近 10 条记录,为了确保获取所有记录,需要访问 events_stages_history_long

 

b. 查看某个阶段的锁等待情况

针对每个 stage 可能出现的锁等待,一个 stage 会对应一个或多个 wait events_waits_history_long 这个表容易爆满 [ 默认阀值 10000] 。由于 select count ( * ) 需要 IO( 逻辑 IO 或者物理 IO) ,所以在 stage / sql / Sending data 阶段会有 io 等待的统计。通过 stage_xxx 表的 event_id 字段与 waits_xxx 表的 nesting_event_id 进行关联。

SELECT

event_id,

event_name,

source,

timer_wait,

object_name ,

index_name,

operation,

nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 2647 ;

+ ----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

| event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |

+ ----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

| 190607 | wait / io / table / sql / handler | handler.cc: 2842 | 1845888 | test_slow | idx_c1 | fetch | 2647 |

| 190608 | wait / io / table / sql / handler | handler.cc: 2842 | 1955328 | test_slow | idx_c1 | fetch | 2647 |

| 190609 | wait / io / table / sql / handler | handler.cc: 2842 | 1929792 | test_slow | idx_c1 | fetch | 2647 |

| 190610 | wait / io / table / sql / handler | handler.cc: 2842 | 1869600 | test_slow | idx_c1 | fetch | 2647 |

| 190611 | wait / io / table / sql / handler | handler.cc: 2842 | 1922496 | test_slow | idx_c1 | fetch | 2647 |

+ ----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

通过上面的实验,我们知道了 statement,stage,wait 的三级结构,通过 nesting_event_id 进行关联,它表示某个事件的父 event_id

 

( 2 ). 模拟 innodb 行锁等待的例子

会话 A 执行语句 update test_icp set y = y + 1 where x = 1 (x primary key ) ,不 commit ;会话 B 执行同样的语句 update test_icp set y = y + 1 where x = 1 ,会话 B 堵塞,并最终报错。通过连接连接查询 events_statements_history_long events_stages_history_long ,可以看到在 updating 阶段花了大约 60s 的时间。这主要因为实例上的 innodb_lock_wait_timeout 设置为 60 ,等待 60s 后超时报错了。

 

SELECT

statement.EVENT_ID,

stages.event_id,

statement.sql_text,

stages.event_name,

stages.timer_wait

FROM events_statements_history_long statement

join events_stages_history_long stages

on statement.event_id = stages.nesting_event_id

WHERE statement.sql_text = 'update test_icp set y=y+1 where x=1' ;

+ ----------+----------+-------------------------------------+--------------------------------+----------------+

| EVENT_ID | event_id | sql_text | event_name | timer_wait |

+ ----------+----------+-------------------------------------+--------------------------------+----------------+

| 5816 | 5817 | update test_icp set y = y + 1 where x = 1 | stage / sql / init | 195543000 |

| 5816 | 5819 | update test_icp set y = y + 1 where x = 1 | stage / sql / checking permissions | 22730000 |

| 5816 | 5821 | update test_icp set y = y + 1 where x = 1 | stage / sql / Opening tables | 66079000 |

| 5816 | 5827 | update test_icp set y = y + 1 where x = 1 | stage / sql / init | 89116000 |

| 5816 | 5828 | update test_icp set y = y + 1 where x = 1 | stage / sql / System lock | 218744000 |

| 5816 | 5832 | update test_icp set y = y + 1 where x = 1 | stage / sql / updating | 6001362045000 |

| 5816 | 5968 | update test_icp set y = y + 1 where x = 1 | stage / sql / end | 10435000 |

| 5816 | 5969 | update test_icp set y = y + 1 where x = 1 | stage / sql / query end | 85979000 |

| 5816 | 5983 | update test_icp set y = y + 1 where x = 1 | stage / sql / closing tables | 56562000 |

| 5816 | 5990 | update test_icp set y = y + 1 where x = 1 | stage / sql / freeing items | 83563000 |

| 5816 | 5992 | update test_icp set y = y + 1 where x = 1 | stage / sql / cleaning up | 4589000 |

+ ----------+----------+-------------------------------------+--------------------------------+----------------+

查看 wait 事件:

SELECT

event_id,

event_name,

source,

timer_wait,

object_name ,

index_name,

operation,

nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 5832 ;

*************************** 1 . row ***************************

event_id: 5832

event_name: wait / io / table / sql / handler

source: handler.cc: 2782

timer_wait: 6005946156624

object_name : test_icp

index_name: PRIMARY

operation: fetch

从结果来看, waits 表中记录了一个 fetch 等待事件,但并没有更细的 innodb 行锁等待事件统计。

 

( 3 ). 模拟 MDL 锁等待的例子

会话 A 执行一个大查询 select count ( * ) from test_slow ,会话 B 执行表结构变更 alter table test_slow modify c2 varchar ( 152 ); 通过如下语句可以得到 alter 语句的执行过程,重点关注 stage / sql / Waiting for table metadata lock” 阶段。

 

SELECT

statement.EVENT_ID,

stages.event_id,

statement.sql_text,

stages.event_name as stage_name,

stages.timer_wait as stage_time

FROM events_statements_history_long statement

left join events_stages_history_long stages

on statement.event_id = stages.nesting_event_id

WHERE statement.sql_text = 'alter table test_slow modify c2 varchar(152)' ;

+ -----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

| EVENT_ID | event_id | sql_text | stage_name | stage_time |

+ -----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

| 326526744 | 326526745 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / init | 216662000 |

| 326526744 | 326526747 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / checking permissions | 18183000 |

| 326526744 | 326526748 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / checking permissions | 10294000 |

| 326526744 | 326526750 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / init | 4783000 |

| 326526744 | 326526751 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / Opening tables | 140172000 |

| 326526744 | 326526760 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / setup | 157643000 |

| 326526744 | 326526769 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / creating table | 8723217000 |

| 326526744 | 326526803 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / After create | 257332000 |

| 326526744 | 326526832 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / Waiting for table metadata lock | 1000181831000 |

| 326526744 | 326526835 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / After create | 33483000 |

| 326526744 | 326526838 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / Waiting for table metadata lock | 1000091810000 |

| 326526744 | 326526841 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / After create | 17187000 |

| 326526744 | 326526844 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / Waiting for table metadata lock | 1000126464000 |

| 326526744 | 326526847 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / After create | 27472000 |

| 326526744 | 326526850 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / Waiting for table metadata lock | 561996133000 |

| 326526744 | 326526853 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / After create | 124876000 |

| 326526744 | 326526877 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / System lock | 30659000 |

| 326526744 | 326526881 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / preparing for alter table | 40246000 |

| 326526744 | 326526889 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / altering table | 36628000 |

| 326526744 | 326528280 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / end | 43824000 |

| 326526744 | 326528281 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / query end | 112557000 |

| 326526744 | 326528299 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / closing tables | 27707000 |

| 326526744 | 326528305 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / freeing items | 201614000 |

| 326526744 | 326528308 | alter table test_slow modify c2 varchar ( 152 ) | stage / sql / cleaning up | 3584000 |

+ -----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

从结果可以看到,出现了多次 stage / sql / Waiting for table metadata lock 阶段,并且间隔 1s ,说明每隔 1s 钟会重试判断。找一个该阶段的 event_id, 通过 nesting_event_id 关联,确定到底在等待哪个 wait 事件。

SELECT

event_id,

event_name,

source,

timer_wait,

object_name ,

index_name,

operation,

nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 326526850 ;

+ -----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

| event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |

+ -----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

| 326526851 | wait / synch / cond / sql / MDL_context::COND_wait_status | mdl.cc: 1327 | 562417991328 | NULL | NULL | timed_wait | 326526850 |

| 326526852 | wait / synch / mutex / mysys / my_thread_var::mutex | sql_class.h: 3481 | 733248 | NULL | NULL | lock | 326526850 |

+ -----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

通过结果可以知道,产生阻塞的是条件变量 MDL_context::COND_wait_status ,并且显示了代码的位置。

View Code

总结:

本文通过对 Performance Schema 数据库的介绍,主要用于收集数据库服务器性能参数:①提供进程等待的详细信息,包括锁、互斥变量、文件信息;②保存历史的事件汇总信息,为提供 MySQL 服务器性能做出详细的判断;③对于新增和删除监控事件点都非常容易,并可以改变 mysql 服务器的监控周期,例如( CYCLE MICROSECOND )。通过该库得到数据库运行的统计信息,更好分析定位问题和完善监控信息。类似的监控还有:

打开标准的 innodb 监控:

CREATE TABLE innodb_monitor (a INT ) ENGINE = INNODB;

打开 innodb 的锁监控:

CREATE TABLE innodb_lock_monitor (a INT ) ENGINE = INNODB;

打开 innodb 表空间监控:

CREATE TABLE innodb_tablespace_monitor (a INT ) ENGINE = INNODB;

打开 innodb 表监控:

CREATE TABLE innodb_table_monitor (a INT ) ENGINE = INNODB;

参考文章:

https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

http://www.cnblogs.com/cchust/p/5022148.html

http://www.cnblogs.com/cchust/p/5057498.html

http://www.cnblogs.com/cchust/p/5061131.html

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14377/viewspace-2564217/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-09-30

  • 博文量
    191
  • 访问量
    286650