MySQL Performance_schema数据字典

1.setup tables

setup_actors

配置用户纬度的监控，默认监控所有用户

setup_consumers

配置events的消费者类型，即收集的events写入到哪些统计表中

mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | YES     |
| events_stages_history            | YES     |
| events_stages_history_long       | YES     |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | YES     |
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | YES     |
| 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)

setup_instruments

配置具体的instrument，主要包含6大类：idle、stage/xxx、statement/xxx、wait/xxx、memory/xxx、transaction.

mysql> select name,count(*) from setup_instruments group by LEFT(name,5);
+-------------------------------------------+----------+
| name                                      | count(*) |
+-------------------------------------------+----------+
| idle                                      |        1 |
| memory/performance_schema/mutex_instances |      380 |
| stage/sql/After create                    |      132 |
| statement/sql/select                      |      194 |
| transaction                               |        1 |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc |      320 |
+-------------------------------------------+----------+
6 rows in set (0.00 sec)

setup_objects

配置监控对象，默认对mysql，performance_schema和information_schema中的表都不监控，而其它DB的所有表都监控

示例

-- 假设setup_objects包含以下行:
+-------------+---------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+---------------+-------------+---------+-------+
| TABLE       | db1           | t1          | YES     | YES   |
| TABLE       | db1           | t2          | YES     | NO    |
| TABLE       | db2           | %           | YES     | YES   |
| TABLE       | db3           | %           | YES     | NO    |
| TABLE       | %             | %           | YES     | YES   |
+-------------+---------------+-------------+---------+-------+
-- 如果一个与表相关的仪表在setup_instruments的TIMED值为NO,该仪表产生事件没有时间信息。如果TIMED值为yes,事件计时情况如下:
-- db1.t1 事件被计时
-- db1.t2 事件不被计时
-- db2.t3事件被计时
-- db3.t4事件不被计时
-- db4.t5事件被计时

setup_timers

配置每种类型指令的统计时间单位.MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒，关于每种类型的具体含义，可以参考performance_timer这个表。由于wait类包含的都是等待事件，单个SQL调用次数比较多，因此选择代价最小的度量单位cycle。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。

示例

mysql> select * from setup_timers;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+
5 rows in set (0.00 sec)

​ 对于wait类型，最重要的是减少开销,所以选择CYCLE类型，相应的代价是损失计时精度。Statement，stage，transaction的执行时间总的来说，相比wait要高一个数量级。为了给statement计时，最重要的是原则是要有一个精确的衡量，并且不受处理器频率影响，因此默认的为NANOSECOND，其额外的‘OVERHEAD’相比CYCLE TIMER并不明显，因为调用一个timer两次的开销（一次是statement开始，一次是statement结束）相比statement执行本身的CPU时间要小很多个数量级。如果使用CYCLE，只有坏处，没有好处。

​ cycle计数器的精度依赖于CPU的速度，使用CYCLE 计数器实际上比使用标准gettimeofday的开销要小，后者的一次调用可能产生上百次cycle。

2.instance tables

cond_instances

条件同步对象实例

file_instances

文件实例

mutex_instances

互斥锁同步对象实例

rwlock_instances

锁同步对象实例

socket_instances

活跃连接实例实例

3.Wait event tables

events_waits_current

当前等待的事件

events_waits_history

每个线程的最近等待时间的表结构同events_waits_current

events_waits_history_long

所有的最近等待时间的表结构同events_waits_current

4.Stage tables

events_stages_current

当前的阶段时间

events_stages_history

每个线程的最近阶段事件表结构同events_stages_current

events_stages_history_long

所有的最近阶段事件表结构同events_stages_current

5.Statement tables

events_statements_current

当前的语句事件

示例

​ 语句事件表(events_statements_current, events_statements_history, 和 events_statements_history_long)有DIGEST和DIGEST_TEXT列包含文摘MD5值和相应的标准化的语句文本字符串。

​ 一个events_statements_summary_by_digest表提供语句文摘的聚合信息。标准化语句有一个固定的长度。DIGEST_TEXT最大长度是1024个字节。

mysql> show variables like '%max_digest_length%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| max_digest_length                    | 1024  |
| performance_schema_max_digest_length | 1024  |
+--------------------------------------+-------+

​ 没有选项来更改这个最大值。如果标准化生产一个语句,超过这个长度,文本以“…”结束。长语句差别只发生在“…”的部分，被认为是相同的。考虑这些语句:

SELECT * FROM mytable WHERE cola = 10 AND colb = 20
SELECT * FROM mytable WHERE cola = 10 AND colc = 20
-- 如果截取碰巧就在右边的and 的部分,两个语句会有这种标准化形式:
SELECT * FROM mytable WHERE cola = ? AND ...
-- 在这种情况下,这种在第二列的名字差异被失去了和两个语句被认为是相同的。

-- 执行查询：select * from test where col1='a';
mysql> select * from events_statements_current\G;
*************************** 2. row ***************************
......
​               SQL_TEXT: select * from test where col1='a'
​            DIGEST_TEXT: SELECT * FROM `test` WHERE `col1` = ? 
......
-- 执行查询：select * from test where col1='c';
mysql> select * from events_statements_current\G;
*************************** 2. row ***************************
......
​               SQL_TEXT: select * from test where col1='c'
​            DIGEST_TEXT: SELECT * FROM `test` WHERE `col1` = ? 
......

events_statements_history

每个线程最近的语句事件表结构同events_statements_current

events_statements_history_long

所有的最近的语句事件表结构同events_statements_current

示例

-- 实例：分析具体某条SQL，该SQL在执行各个阶段的时间消耗，通过events_statements_xxx表和events_stages_xxx表，就可以达到目的。
mysql> select count(*) from lt.apsm;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (10.13 sec) 
mysql> SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
+----------+----------------------------------------------------------------------------+
| EVENT_ID | sql_text                                                                   |
+----------+----------------------------------------------------------------------------+
|    38645 | select count(*) from lt.apsm                                               |
|  1063453 | SELECT EVENT_ID,sql_text FROM events_statements_current WHERE sql_text LIKE '%count(*)%' |
|    38184 | select count(*) from lt.test                                               |
+----------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec) 
mysql> SELECT event_id,EVENT_NAME,SOURCE,sys.format_time(TIMER_END - TIMER_START) FROM events_stages_history_long WHERE NESTING_EVENT_ID = 38645;
+----------+--------------------------------+--------------------------+------------------------------------------+
| event_id | EVENT_NAME                     | SOURCE                   | sys.format_time(TIMER_END - TIMER_START) |
+----------+--------------------------------+--------------------------+------------------------------------------+
|    38646 | stage/sql/starting             | socket_connection.cc:97  | 90.54 us                                  |
|    38651 | stage/sql/checking permissions | sql_authorization.cc:835 | 5.35 us                                  |
|    38653 | stage/sql/Opening tables       | sql_base.cc:5649         | 151.09 us                                |
|    38665 | stage/sql/init                 | sql_select.cc:121        | 16.76 us                                  |
|    38668 | stage/sql/System lock          | lock.cc:323              | 11.35 us                                  |
|    38675 | stage/sql/optimizing           | sql_optimizer.cc:151     | 10.12 s                                  |
|  1063438 | stage/sql/executing            | sql_executor.cc:119      | 13.77 us                                  |
|  1063439 | stage/sql/end                  | sql_select.cc:199        | 2.85 us                                  |
|  1063440 | stage/sql/query end            | sql_parse.cc:5012        | 13.71 us                                  |
|  1063444 | stage/sql/closing tables       | sql_parse.cc:5064        | 15.05 us                                  |
|  1063448 | stage/sql/freeing items        | sql_parse.cc:5638        | 24.55 us                                  |
|  1063450 | stage/sql/cleaning up          | sql_parse.cc:1901        | 2.28 us                                  |
+----------+--------------------------------+--------------------------+------------------------------------------+12 rows in set (0.01 sec)

prepared_statements_instances

准备的语句实例和统计信息

示例

mysql> select * from prepared_statements_instances\G
Empty set (0.01 sec)

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; 
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> select * from prepared_statements_instances\G;
*************************** 1. row ***************************
​      OBJECT_INSTANCE_BEGIN: 386397440
​               STATEMENT_ID: 1
​             STATEMENT_NAME: stmt1
​                   SQL_TEXT: SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
​            OWNER_THREAD_ID: 276
​             OWNER_EVENT_ID: 2757
​          OWNER_OBJECT_TYPE: NULL
​        OWNER_OBJECT_SCHEMA: NULL
​          OWNER_OBJECT_NAME: NULL
​              TIMER_PREPARE: 12436401000
​            COUNT_REPREPARE: 0
​              COUNT_EXECUTE: 1
​          SUM_TIMER_EXECUTE: 199554000
​          MIN_TIMER_EXECUTE: 199554000
​          AVG_TIMER_EXECUTE: 199554000
​          MAX_TIMER_EXECUTE: 199554000
​              SUM_LOCK_TIME: 0
​                 SUM_ERRORS: 0
​               SUM_WARNINGS: 0
​          SUM_ROWS_AFFECTED: 0
​              SUM_ROWS_SENT: 1
​          SUM_ROWS_EXAMINED: 0
​SUM_CREATED_TMP_DISK_TABLES: 0
​     SUM_CREATED_TMP_TABLES: 0
​       SUM_SELECT_FULL_JOIN: 0
​ SUM_SELECT_FULL_RANGE_JOIN: 0
​           SUM_SELECT_RANGE: 0
​     SUM_SELECT_RANGE_CHECK: 0
​            SUM_SELECT_SCAN: 0
​      SUM_SORT_MERGE_PASSES: 0
​             SUM_SORT_RANGE: 0
​              SUM_SORT_ROWS: 0
​              SUM_SORT_SCAN: 0
​          SUM_NO_INDEX_USED: 0
​     SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_variables_by_thread;
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|       276 | a             | 3              |
|       276 | b             | 4              |
+-----------+---------------+----------------+
2 rows in set (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)

6.transaction tables

events_transactions_current

当前的事务事件

events_transactions_history

表结构同events_transactions_current

events_transactions_history_long

表结构同events_transactions_current

7.connection tables

Accounts

每个客户端连接统计表

-- 使用案例：使用连接池创建10个连接
mysql> select * from accounts;
+------+--------------+---------------------+-------------------+
| USER | HOST         | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+--------------+---------------------+-------------------+
| NULL | NULL         |                  49 |               122 |
| root | localhost    |                   2 |               190 |
| root | 10.41.168.11 |                  10 |                11 |**
+------+--------------+---------------------+-------------------+
3 rows in set (0.00 sec)

-- 释放连接
mysql> select * from accounts;
+------+--------------+---------------------+-------------------+
| USER | HOST         | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+--------------+---------------------+-------------------+
| NULL | NULL         |                  49 |               122 |
| root | localhost    |                   2 |               190 |
| root | 10.41.168.11 |                   0 |                11 |**
+------+--------------+---------------------+-------------------+

hosts

每个客户端连接主机统计

users

每个客户端连接用户名统计

8.connection Attribute Tables

session_account_connect_attrs

当前会话的连接属性

session_connect_attrs

所有会话的连接属性

mysql> select * from session_connect_attrs;
+----------------+------------------+----------------------+------------------+
| PROCESSLIST_ID | ATTR_NAME        | ATTR_VALUE           | ORDINAL_POSITION |
+----------------+------------------+----------------------+------------------+
|            224 | _os              | Linux                |                0 |
|            224 | _client_name     | libmysql             |                1 |
|            224 | _pid             | 10020                |                2 |
|            224 | _client_version  | 5.7.17               |                3 |
|            224 | _platform        | x86_64               |                4 |
|            224 | program_name     | mysql                |                5 |
|            225 | _os              | Linux                |                0 |
|            225 | _client_name     | libmysql             |                1 |
|            225 | _pid             | 38805                |                2 |
|            225 | _client_version  | 5.7.17               |                3 |
|            225 | _platform        | x86_64               |                4 |
|            225 | program_name     | mysql                |                5 |
|            226 | _os              | Linux                |                0 |
|            226 | _client_name     | libmysql             |                1 |
|            226 | _pid             | 4957                 |                2 |
|            226 | _client_version  | 5.7.17               |                3 |
|            226 | _platform        | x86_64               |                4 |
|            226 | program_name     | mysql                |                5 |
|            229 | _os              | Win64                |                0 |
|            229 | _client_name     | libmysql             |                1 |
|            229 | _pid             | 119544               |                2 |
|            229 | _thread          | 118952               |                3 |
|            229 | _platform        | x86_64               |                4 |
|            229 | program_name     | mysql                |                5 |
|            229 | _client_version  | 5.7.18               |                6 |
|            230 | _runtime_version | 1.8.0_144            |                0 |
|            230 | _client_version  | 5.1.31               |                1 |
|            230 | _client_name     | MySQL Connector Java |                2 |
|            230 | _client_license  | GPL                  |                3 |
|            230 | _runtime_vendor  | Oracle Corporation   |                4 |
+----------------+------------------+----------------------+------------------+
30 rows in set (0.00 sec)

_client_name：客户端名称（客户端库的libmysql）
_client_version：客户端库版本
_os：操作系统（例如Linux，Win64）
_pid：客户端进程ID
_platform：机器平台（例如，x86_64）
_thread：客户端线程ID（仅限Windows）

MySQL Connector / J定义了这些属性：
_client_license：连接器许可证类型
_runtime_vendor：Java运行时环境（JRE）供应商
_runtime_version：Java运行时环境（JRE）版本

9.User Variable Tables

user_variables_by_thread

10.replication tables

replication_applier_configuration

从上的事务应用程序的配置参数

表列和show slave status列对应关系：

replication_applier_status

从上的事务应用程序的当前状态

表列和show slave status列对应关系：

replication_applier_status_by_coordinator

协调器线程的状态（空，除非从机为多线程）

mysql> select * from replication_applier_status_by_coordinator\G;
*************************** 1. row ***************************
​        CHANNEL_NAME: 
​           THREAD_ID: 52
​       SERVICE_STATE: ON
   LAST_ERROR_NUMBER: 0
  LAST_ERROR_MESSAGE: 
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.01 sec)

mysql> select * from threads where thread_id='52'\G;
*************************** 1. row ***************************
​          THREAD_ID: 52
​               NAME: thread/sql/slave_sql --SQL线程
​               TYPE: FOREGROUND
​     PROCESSLIST_ID: 2
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
​     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
   PROCESSLIST_TIME: 3502
  PROCESSLIST_STATE: Slave has read all relay log; waiting for more updates
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
​               ROLE: NULL
​       INSTRUMENTED: YES
​            HISTORY: YES
​    CONNECTION_TYPE: NULL
​       THREAD_OS_ID: 17031
1 row in set (0.00 sec)

表列和show slave status列对应关系：

replication_applier_status_by_worker

如果从是多线程的，则应用程序线程或工作线程的状态

实例

mysql> select * from replication_applier_status_by_worker limit 1\G;
*************************** 1. row ***************************
​         CHANNEL_NAME: 
​            WORKER_ID: 1
​            THREAD_ID: 53
​        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: d93f98a0-660a-11e7-b3a9-744aa40209ee:131
​    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE: 
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

和show slave status对应关系

replication_connection_configuration

用于连接到主机的配置参数

实例

mysql> select * from replication_connection_configuration\G;
*************************** 1. row ***************************
​                 CHANNEL_NAME: 
​                         HOST: 192.168.1.104
​                         PORT: 15518
​                         USER: repl
​            NETWORK_INTERFACE: 
​                AUTO_POSITION: 0
​                  SSL_ALLOWED: NO
​                  SSL_CA_FILE: 
​                  SSL_CA_PATH: 
​              SSL_CERTIFICATE: 
​                   SSL_CIPHER: 
​                      SSL_KEY: 
SSL_VERIFY_SERVER_CERTIFICATE: NO
​                 SSL_CRL_FILE: 
​                 SSL_CRL_PATH: 
​    CONNECTION_RETRY_INTERVAL: 60
​       CONNECTION_RETRY_COUNT: 86400
​           HEARTBEAT_INTERVAL: 30.000
​                  TLS_VERSION: 
1 row in set (0.00 sec)

和show slave status对应关系

replication_connection_status

与主机的连接的当前状态

实例

mysql> select * from replication_connection_status\G;
*************************** 1. row ***************************
​             CHANNEL_NAME: 
​               GROUP_NAME: 
​              SOURCE_UUID: d93f98a0-660a-11e7-b3a9-744aa40209ee
​                THREAD_ID: 103
​            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 8910
 LAST_HEARTBEAT_TIMESTAMP: 2017-09-21 17:08:29
 RECEIVED_TRANSACTION_SET: 
​        LAST_ERROR_NUMBER: 0
​       LAST_ERROR_MESSAGE: 
​     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

和show slave status对应关系

replication_group_member_stats

为组成员提供网络和状态信息

replication_group_members

提供有关组参与者的组成员和事务的统计信息

11.Lock Tables

metadata_locks

元数据锁被保存并被请求

打开元数据锁

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'；

实例

mysql>  select * from metadata_locks\G;
*************************** 1. row ***************************
​          OBJECT_TYPE: TABLE
​        OBJECT_SCHEMA: performance_schema
​          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 380052496
​            LOCK_TYPE: SHARED_READ
​        LOCK_DURATION: TRANSACTION
​          LOCK_STATUS: GRANTED
​               SOURCE: sql_parse.cc:6038
​      OWNER_THREAD_ID: 274
​       OWNER_EVENT_ID: 5024
1 row in set (0.00 sec)

锁类型

目前MDL有如下锁模式，锁之间的兼容性可见源码mdl.cc：

关于lock_status

1. 当请求并立即获取元数据锁时，将插入状态为GRANTED的行。

2. 当请求元数据锁并且不立即获取时，将插入状态为PENDING的行。

3. 当先前请求的元数据锁被授予时，其行状态更新为GRANTED。

4. 当元数据锁被释放时，它的行被删除。

5. 当死锁检测器取消挂起的锁定请求以打破死锁（ER_LOCK_DEADLOCK）时，其行状态将从PENDING更新为VICTIM。

6. 当待处理的锁定请求超时（ER_LOCK_WAIT_TIMEOUT）时，其行状态将从PENDING更新为TIMEOUT。

7. 当授予锁定或挂起的锁定请求被杀死时，其行状态从GRANTED或PENDING更新为KILLED。

   VICTIM，TIMEOUT和KILLED状态值很简短，表示锁行即将被删除。

table_handles

表锁被保存并被请求

实例

mysql> lock tables test read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_handles where object_name='test'\G;
*************************** 7. row ***************************
​          OBJECT_TYPE: TABLE
​        OBJECT_SCHEMA: lt
​          OBJECT_NAME: test
OBJECT_INSTANCE_BEGIN: 367946800
​      OWNER_THREAD_ID: 275
​       OWNER_EVENT_ID: 2541
​        INTERNAL_LOCK: NULL
​        EXTERNAL_LOCK: READ EXTERNAL

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

12.System Variable Tables

global_variables

全局系统变量。只需要全局值的应用程序应该使用此表。

session_variables

当前会话的系统变量。希望自己会话的所有系统变量值的应用程序应该使用此表,它包括其会话的会话变量，以及没有会话对应的全局变量的值。

variables_by_thread

每个活动会话的会话系统变量,想要了解特定会话的会话变量值的应用程序应该使用此表。它仅包含会话变量，由线程ID标识。

13.Summary Tables

Wait Event Summaries

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

每个事件名称等待事件

实例：列出Top N 个 class等待event by latency

能解决什么问题：站在更高的角度来衡量，主要是table的io，文件的io，表的lock的wait事件延迟。

SELECT SUBSTRING_INDEX(event_name,'/', 3) AS event_class,
       SUM(COUNT_STAR) AS total,
       sys.format_time(SUM(sum_timer_wait)) AS total_latency,
       sys.format_time(MIN(min_timer_wait)) min_latency,
       sys.format_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency,
       sys.format_time(MAX(max_timer_wait)) AS max_latency
  FROM performance_schema.events_waits_summary_global_by_event_name
 WHERE sum_timer_wait > 0
   AND event_name != 'idle'
 GROUP BY SUBSTRING_INDEX(event_name,'/', 3)
 ORDER BY SUM(sum_timer_wait) DESC;
+-------------------+-----------+---------------+-------------+-------------+-------------+
| event_class       | total     | total_latency | min_latency | avg_latency | max_latency |
+-------------------+-----------+---------------+-------------+-------------+-------------+
| wait/io/table     | 730785513 | 14.18 h       | 704.70 ns   | 69.84 us    | 51.03 s     |
| wait/synch/cond   |   1063712 | 5.83 h        | 1.46 us     | 19.72 ms    | 5.08 s      |
| wait/synch/sxlock | 126045676 | 2.99 h        | 46.98 ns    | 85.36 us    | 511.15 ms   |
| wait/synch/mutex  | 482116669 | 2.99 h        | 24.36 ns    | 22.31 us    | 911.73 ms   |
| wait/io/file      |   9874913 | 29.96 m       | 0 ps        | 182.05 us   | 1.06 s      |
| wait/io/socket    |  35572378 | 13.38 m       | 0 ps        | 22.57 us    | 134.82 ms   |
| wait/lock/table   |  31248958 | 1.37 m        | 267.96 ns   | 2.63 us     | 5.28 ms     |
| wait/synch/rwlock |  22496810 | 4.30 s        | 40.02 ns    | 190.94 ns   | 4.90 ms     |
+-------------------+-----------+---------------+-------------+-------------+-------------+
8 rows in set (0.02 sec)

Stage Summaries

events_stages_summary_by_account_by_event_name

表结构同events_waits_summary_by_account_by_event_name

events_stages_summary_by_host_by_event_name

表结构同events_waits_summary_by_host_by_event_name

events_stages_summary_by_thread_by_event_name

表结构同events_waits_summary_by_thread_by_event_name

events_stages_summary_by_user_by_event_name

表结构同events_waits_summary_by_user_by_event_name

events_stages_summary_global_by_event_name

表结构同events_waits_summary_global_by_event_name

Statement Summaries

(重要)events_statements_summary_by_digest

使用说明

(1).哪类SQL执行最多？

可以看到执行次数最多的？

COUNT_STAR，FIRST_SEEN和LAST_SEEN分别显示了语句第一次执行和最后一次执行的时间点。

(2).哪类SQL的平均响应时间最多？

AVG_TIMER_WAIT

(3).哪类SQL排序记录数最多？

SUM_SORT_ROWS

(4).哪类SQL扫描记录数最多？

SUM_ROWS_EXAMINED

(5).哪类SQL使用临时表最多？

SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES

(6).哪类SQL返回结果集最多？

SUM_ROWS_SENT

(7).找出有error 或者 warning 的 SQL

SUM_ERRORS,SUM_WARNINGS

(8).找出全表扫描的SQL

SUM_NO_INDEX_USED,SUM_NO_GOOD_INDEX_USED

通过上述指标我们可以间接获得某类SQL的逻辑IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)的对比，但还无法得到某类SQL的物理IO消耗，以及某类SQL访问数据的buffer命中率。

SQL维度统计信息使用实例

1.列出top N 个SQL的详细情况，以latency降序排列

SELECT sys.format_statement(DIGEST_TEXT) AS query,
        SCHEMA_NAME AS db,
        IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
        COUNT_STAR AS exec_count,
        SUM_ERRORS AS err_count,
        SUM_WARNINGS AS warn_count,
        sys.format_time(SUM_TIMER_WAIT) AS total_latency,
        sys.format_time(MAX_TIMER_WAIT) AS max_latency,
        sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
        sys.format_time(SUM_LOCK_TIME) AS lock_latency,
        SUM_ROWS_SENT AS rows_sent,
        ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
        SUM_ROWS_EXAMINED AS rows_examined,
        ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0))  AS rows_examined_avg,
        SUM_CREATED_TMP_TABLES AS tmp_tables,
        SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
        SUM_SORT_ROWS AS rows_sorted,
        SUM_SORT_MERGE_PASSES AS sort_merge_passes,
        DIGEST AS digest,
        FIRST_SEEN AS first_seen,
        LAST_SEEN as last_seen
   FROM performance_schema.events_statements_summary_by_digest
 ORDER BY SUM_TIMER_WAIT DESC limit 10;
*************************** 2. row ***************************
            query: UPDATE `sbtest1` SET `k` = `k` + ? WHERE `id` = ? 
               db: lt
        full_scan: 
       exec_count: 1734843 --SQL执行的总次数
        err_count: 0 --SQL执行失败的总次数
       warn_count: 0 
    total_latency: 8.23 h  --总延时
      max_latency: 922.08 ms
      avg_latency: 17.07 ms
     lock_latency: 5.62 m
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 1735053
rows_examined_avg: 1
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: b301230939993a6f01020a8bc9cb16c3
       first_seen: 2017-09-19 11:49:46
        last_seen: 2017-09-19 14:13:05
2 rows in set (0.00 sec)

实例2：找出排序延迟对多的SQL

SELECT sys.format_statement(DIGEST_TEXT) AS query,
        SCHEMA_NAME db,
        COUNT_STAR AS exec_count,
        sys.format_time(SUM_TIMER_WAIT) AS total_latency,
        SUM_SORT_MERGE_PASSES AS sort_merge_passes,
        SUM_SORT_SCAN AS sorts_using_scans,
        SUM_SORT_RANGE AS sort_using_range,
        SUM_SORT_ROWS AS rows_sorted,
        FIRST_SEEN as first_seen,
        LAST_SEEN as last_seen,
        DIGEST AS digest
   FROM performance_schema.events_statements_summary_by_digest
  WHERE SUM_SORT_ROWS > 0 and SCHEMA_NAME not in ('mysql','sys')
  ORDER BY SUM_TIMER_WAIT DESC;
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-------------------+------------------+-------------+---------------------+---------------------+----------------------------------+

| query                                                             | db   | exec_count | total_latency | sort_merge_passes | sorts_using_scans | sort_using_range | rows_sorted | first_seen          | last_seen           | digest                           |

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

| SELECT DISTINCTROW `c` FROM `s ...  BETWEEN ? AND ? ORDER BY `c`  | lt   |    1735356 | 4.35 h        |                 0 |           1735448 |                0 |   173541400 | 2017-09-19 11:49:46 | 2017-09-19 14:13:05 | 23fee108669d316718c9b5c78d7b9f73 |

| SELECT `c` FROM `sbtest1` WHER ...  BETWEEN ? AND ? ORDER BY `c`  | lt   |    1735595 | 1.90 h        |                 0 |                 0 |          1735691 |   173568300 | 2017-09-19 11:49:46 | 2017-09-19 14:13:05 | 4c5fc311dca5c840c9d8267ffbbb5d7e |

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

events_statements_summary_by_program

events_statements_summary_by_account_by_event_name

events_statements_summary_by_host_by_event_name

events_statements_summary_by_thread_by_event_name

events_statements_summary_by_user_by_event_name

events_statements_summary_global_by_event_name

Transaction Summaries

events_transactions_summary_by_account_by_event_name

events_transactions_summary_by_host_by_event_name

events_transactions_summary_by_thread_by_event_name

events_transactions_summary_by_user_by_event_name

events_transactions_summary_global_by_event_name

Object Wait Summaries

objects_summary_global_by_type

对象摘要

使用说明

能解决什么问题：

1. 从table的角度来衡量DB的压力。
2. 和file_summary_by_instance类似，file_summary_by_instance考虑的是文件，是物理IO。而objects_summary_global_by_type更多的是上层的压力分布。
3. 除了能解决file_summary_by_instance的问题外，还可以发现并发的问题。比如：如果这里table的total很低，但是total_latency 很高，这就能很好的说明，80%是由于这个file的并发访问造成的high latency
4. 它还能做一件非常niubility的事情，那就是查看哪些表已经不被使用，已经下线了。其实很多开发也会问哪些表不被使用了，这下就可以派上用场了。好处：a）优化业务 b）减少磁盘空间。 c）减少备份的压力。

mysql>  SELECT object_schema AS table_schema, 
    ->              object_name AS table_name,
    ->              count_star AS total,
    ->              sys.format_time(sum_timer_wait) as total_latency,
    ->              sys.format_time(sum_timer_wait / count_star) as avg_latency,
    ->              sys.format_time(max_timer_wait) as max_latency
    ->   FROM performance_schema.objects_summary_global_by_type
    ->        ORDER BY sum_timer_wait DESC limit 10;
+--------------+---------------+-----------+---------------+-------------+-------------+
| table_schema | table_name    | total     | total_latency | avg_latency | max_latency |
+--------------+---------------+-----------+---------------+-------------+-------------+
| lt           | sbtest1       | 762034158 | 14.16 h       | 66.91 us    | 922.13 ms   |
| lt           | test          |       258 | 2.27 m        | 528.48 ms   | 51.03 s     |
| sys          | format_path   |       749 | 279.48 ms     | 373.13 us   | 587.36 us   |
| sys          | format_bytes  |      6072 | 54.96 ms      | 9.05 us     | 62.73 us    |
| lt           | apsm          |        43 | 18.10 ms      | 420.83 us   | 17.86 ms    |
| sys          | format_time   |       450 | 4.08 ms       | 9.06 us     | 39.30 us    |
| mysql        | gtid_executed |         0 | 0 ps          | NULL        | 0 ps        |
| mysql        | server_cost   |         0 | 0 ps          | NULL        | 0 ps        |
| mysql        | user          |         0 | 0 ps          | NULL        | 0 ps        |
| mysql        | db            |         0 | 0 ps          | NULL        | 0 ps        |
+--------------+---------------+-----------+---------------+-------------+-------------+
10 rows in set (0.01 sec)

File I/O Summaries

file_summary_by_event_name

使用说明

统计每个事件的IO使用率，单位bytes，counts。 Top Waits By bytes，能解决什么问题？

1. 专注的不再只是文件一个点，包括服务器层和引擎层。
2. 如果relaylog非常大，说明同步有问题。
3. 如果binlog比较大，说明binlog有问题。
4. 如果sql/FRM 比较大，Tune table_open_cache / table_definition_cache
5. 如果sql/file_parse比较大，如果在5.5比较高，那就升级mysql到5.6
6. 如果query_log比较大，那就disable genery log
7. 如果slow log比较大，那就调整slow阈值。
8. 还有很多值得挖掘~~

mysql> SELECT SUBSTRING_INDEX(event_name, '/', -2) event_name,
    ->        count_star AS total,
    ->        sys.format_time(sum_timer_wait) AS total_latency,
    ->        count_read,
    ->        sys.format_bytes(sum_number_of_bytes_read) AS total_read,
    ->        count_write,
    ->        sys.format_bytes(sum_number_of_bytes_write) AS total_written,
    ->        sys.format_bytes(sum_number_of_bytes_write + sum_number_of_bytes_read) AS total_requested
    ->   FROM performance_schema.file_summary_by_event_name
    ->  WHERE event_name LIKE 'wait/io/file/%'
    ->    AND count_star > 0
    ->  ORDER BY sum_number_of_bytes_write + sum_number_of_bytes_read DESC limit 10;
+-------------------------+---------+---------------+------------+------------+-------------+---------------+-----------------+
| event_name              | total   | total_latency | count_read | total_read | count_write | total_written | total_requested |
+-------------------------+---------+---------------+------------+------------+-------------+---------------+-----------------+
| innodb/innodb_data_file | 6181102 | 15.64 m       |    4151635 | 63.33 GiB  |     1893218 | 57.37 GiB     | 120.71 GiB      |
| innodb/innodb_log_file  | 2090529 | 7.39 m        |          8 | 132.50 KiB |     1045255 | 10.43 GiB     | 10.43 GiB       |
| sql/binlog              | 1522702 | 6.88 m        |       1292 | 10.04 MiB  |     1396550 | 4.62 GiB      | 4.63 GiB        |
| sql/io_cache            |   67614 | 1.58 s        |          0 | 0 bytes    |       63407 | 1.92 GiB      | 1.92 GiB        |
| innodb/innodb_temp_file |    1380 | 416.42 ms     |        688 | 688.00 MiB |         688 | 688.00 MiB    | 1.34 GiB        |
| sql/binlog_index        |    7662 | 489.73 ms     |       1103 | 2.83 MiB   |           0 | 0 bytes       | 2.83 MiB        |
| sql/FRM                 |    3631 | 216.27 ms     |       1453 | 695.27 KiB |         509 | 96.54 KiB     | 791.81 KiB      |
| sql/ERRMSG              |       5 | 7.12 ms       |          3 | 74.51 KiB  |           0 | 0 bytes       | 74.51 KiB       |
| mysys/charset           |       3 | 7.02 ms       |          1 | 18.27 KiB  |           0 | 0 bytes       | 18.27 KiB       |
| myisam/dfile            |      38 | 19.98 ms      |         12 | 15.99 KiB  |           0 | 0 bytes       | 15.99 KiB       |
+-------------------------+---------+---------------+------------+------------+-------------+---------------+-----------------+
10 rows in set (0.00 sec)

同样，也可以通过延时的维度进行统计

file_summary_by_instance

使用说明

IO 相关，文件相关的延迟 ， 事件相关的延迟， FILE IO, 使用说明： 统计每个文件的IO使用率, 比如读写次数，读写bytes,能解决什么问题？

1. 可以清楚的知道那个文件是被访问的最频繁，压力最大，延迟最多的文件。
2. 可以实时监控redo log，undo，datafile的变化，从而做针对性的优化和调整。当然，通过这个，也能知道瓶颈。
3. 可以清楚的知道某个库级别，表级别的压力情况，这个比之前count sql数量，观察data size要靠谱，真实的多。
4. 可以做前瞻性的规划，比如：拆库拆表，可以指导如何按照压力负载均衡的做到合理来优化拆分
5. 通过表级别的count监控，还可以用做前端缓存的利用率监控。
6. 可以知道哪些db.table是以只读为主，哪些db.test是以只写为主。从而又进一步做缓存和业务监控。

mysql> SELECT file_name AS file,
    ->        count_read,
    ->        sys.format_bytes(sum_number_of_bytes_read) AS total_read,
    ->        count_write,
    ->        sys.format_bytes(sum_number_of_bytes_write) AS total_written,
    ->        sys.format_bytes(sum_number_of_bytes_read + sum_number_of_bytes_write) AS total
    ->   FROM performance_schema.file_summary_by_instance
    ->  ORDER BY sum_number_of_bytes_read + sum_number_of_bytes_write DESC limit 10;
+------------------------------------------------------+------------+------------+-------------+---------------+-----------+
| file                                                 | count_read | total_read | count_write | total_written | total     |
+------------------------------------------------------+------------+------------+-------------+---------------+-----------+
| /home/data_sas/ltdb/data/data/lt/sbtest1.ibd         |    4149424 | 63.30 GiB  |     1665754 | 27.78 GiB     | 91.08 GiB |
| /home/data_sas/ltdb/data/data/ibdata1                |       1614 | 27.23 MiB  |      214880 | 29.17 GiB     | 29.19 GiB |
| /home/data_sas/ltdb/data/redo/ib_logfile0            |          8 | 132.50 KiB |      623136 | 5.26 GiB      | 5.26 GiB  |
| /home/data_sas/ltdb/data/redo/ib_logfile1            |          0 | 0 bytes    |      422115 | 5.17 GiB      | 5.17 GiB  |
| /home/data_sas/ltdb/data/data/Innodb Merge Temp File |        688 | 688.00 MiB |         688 | 688.00 MiB    | 1.34 GiB  |
| /home/data_sas/ltdb/data/binlog/mysql-bin.000477     |       1284 | 10.02 MiB  |        4038 | 10.02 MiB     | 20.04 MiB |
| /home/data_sas/ltdb/data/data/ibtmp1                 |          2 | 32.00 KiB  |          95 | 13.30 MiB     | 13.33 MiB |
| /home/data_sas/ltdb/data/binlog/mysql-bin.000025     |          0 | 0 bytes    |        1368 | 10.40 MiB     | 10.40 MiB |
| /home/data_sas/ltdb/data/binlog/mysql-bin.000011     |          2 | 16.00 KiB  |        1355 | 10.33 MiB     | 10.35 MiB |
| /home/data_sas/ltdb/data/binlog/mysql-bin.000012     |          0 | 0 bytes    |        1348 | 10.34 MiB     | 10.34 MiB |
+------------------------------------------------------+------------+------------+-------------+---------------+-----------+
10 rows in set (0.00 sec)

除了读写的次数大小作为维度外，也可以使用延时作为维度统计。

Table I/O and Lock Wait Summaries

table_io_waits_summary_by_table

汇总由wait / io / table / sql / handler工具生成的所有表I / O等待事件。分组是按table

使用说明

1. 可以精确到表级别的IOPS，TPS。为诊断问题性能问题提供可靠的粒度。
2. 可以指导通过数据来了解业务并且指导业务开发，为什么IUD很高，为什么S很高。
3. 有些表如果只有IDU，或者甚至只有I，那么这些表根本就不适合放入在线DB，so，可以提供在线DB的架构优化。

重点说明

以performance_schema.table_io_waits_summary_by_table举例

• 几个常用的命令：

show variables like 'perf%';
show status like 'perf%';
show engine performance_schema status;
(table_share_hash).count -- performance_schema 监控 当前表的数量
(table_share_hash).size  -- performance_schema 监控 表打开的最大的数量

• 如果(table_share_hash).size 达到最大限制了会有什么影响？如何解决？
  • 如果达到最大限制，即(table_share_hash).count = (table_share_hash).size ， 之后新的表就不能被统计进来。
  • 原来已经打开的表，任然受监控，不影响。

  如何解决呢？

  • 如果可以停mysql的话，可以调大performance_schema_max_table_instances 值。
  • 如果不能停mysql，可以适当的删除一些表如： drop table xx; 这样做可以减少(table_share_hash).count的数量。
  • 注意： truncate table performance_schema.table_io_waits_summary_by_table 只能reset status = 0 ，不会删除里面的记录，所以这个行不通。

• performance_schema.table_io_waits_summary_by_table 里面的count_fetch 需要注意：

• count_fetch, 指的是数据库底层com_fetch的量，千万别理解成select。

• 哪些操作会导致count_fetch 增长呢？

  • insert 操作不会造成 count_fetch ++ , 但是update 会造成 count_fetch ++。

  • 其实，这也很好理解，因为update语句，会从数据库中fetch数据到server层以及内存，然后在修改。

  • select 操作会造成 count_fetch 增长。

• 一次select，count_fetch 增长 1 么？

  • ​ 显然不是，count_fetch 指的是： 一次底层fetch调用，增长一次。比如：有一张表100条记录。SQL语句为：select * from table; rows_affected: 100rows那么count_fetch 至少是100，而不是1.

  • 为什么说至少是100呢？有可能会从二级索引中fetch 数据，然后再从主键索引中fetch 数据。特别注意： 有的时候count_update 不增长，也没有select 操作，为啥count_fetch 会增长呢？如果理解了count_fetch,你就不难懂了。

• fetch 指的是从数据库文件fetch的fetch 调用。举例子：一条SQL语句如： update table_A set name = ‘test’ where id = 3; 你猜，会怎样？结果是：count_fetch 增加，count_update 没有增加。因为：根本就没有id=3的记录与之匹配，也就是说udpate根本就没有更改任何记录，所以count_update 不会增加，但是有fetch操作，所以count_fetch 由此增长。

验证：

mysql> SELECT object_schema AS table_schema,
    ->             object_name AS table_name,
    ->             count_star AS rows_io_total,
    ->             count_read AS rows_read,
    ->             count_write AS rows_write,
    ->             count_fetch AS rows_fetchs,
    ->             count_insert AS rows_inserts,
    ->             count_update AS rows_updates,
    ->             count_delete AS rows_deletes,
    ->             sys.format_time(sum_timer_fetch)  AS fetch_latency,
    ->             sys.format_time(sum_timer_insert) AS insert_latency,
    ->             sys.format_time(sum_timer_update) AS update_latency,
    ->             sys.format_time(sum_timer_delete) AS delete_latency
    ->      FROM performance_schema.table_io_waits_summary_by_table
    ->         ORDER BY sum_timer_wait DESC limit 10;
+--------------+------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+
| table_schema | table_name | rows_io_total | rows_read | rows_write | rows_fetchs | rows_inserts | rows_updates | rows_deletes | fetch_latency | insert_latency | update_latency | delete_latency |
+--------------+------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+
| lt           | sbtest1    |     730785272 | 723841944 |    6943328 |   723841944 |      1735832 |      3471664 |      1735832 | 5.65 h        | 1.39 h         | 6.79 h         | 18.88 m        |
| lt           | test       |           199 |       177 |         22 |         177 |           14 |            8 |            0 | 2.27 m        | 881.14 us      | 911.04 us      | 0 ps           |
| lt           | apsm       |            40 |        40 |          0 |          40 |            0 |            0 |            0 | 18.08 ms      | 0 ps           | 0 ps           | 0 ps           |
| sys          | sys_config |             0 |         0 |          0 |           0 |            0 |            0 |            0 | 0 ps          | 0 ps           | 0 ps           | 0 ps           |
+--------------+------------+---------------+-----------+------------+-------------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+
4 rows in set (0.00 sec)

table_io_waits_summary_by_index_usage

table_io_waits_summary_by_index_usage表汇总由wait / io / table / sql / handler工具生成的所有表索引I / O等待事件。 分组是按表索引。

使用说明

检测索引

-- 1）哪些索引没有利用？ 2）哪些索引使用率高
mysql> SELECT OBJECT_SCHEMA AS table_schema,
    ->                OBJECT_NAME AS table_name,
    ->                INDEX_NAME as index_name,
    ->                COUNT_FETCH AS rows_fetched,
    ->                sys.format_time(SUM_TIMER_FETCH) AS select_latency,
    ->                COUNT_INSERT AS rows_inserted,
    ->                sys.format_time(SUM_TIMER_INSERT) AS insert_latency,
    ->                COUNT_UPDATE AS rows_updated,
    ->                sys.format_time(SUM_TIMER_UPDATE) AS update_latency,
    ->                COUNT_DELETE AS rows_deleted,
    ->                sys.format_time(SUM_TIMER_INSERT) AS delete_latency
    -> FROM performance_schema.table_io_waits_summary_by_index_usage
    -> WHERE index_name IS NOT NULL
    -> ORDER BY sum_timer_wait DESC;
+--------------+------------+------------+--------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name | index_name | rows_fetched | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+------------+------------+--------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| lt           | sbtest1    | PRIMARY    |    723841944 | 5.65 h         |             0 | 0 ps           |      3471664 | 6.79 h         |      1735832 | 0 ps           |
| lt           | apsm       | PRIMARY    |            0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| lt           | apsm       | apsmactno  |            0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| sys          | sys_config | PRIMARY    |            0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| lt           | sbtest1    | k_1        |            0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
+--------------+------------+------------+--------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
5 rows in set (0.01 sec)

找到全表扫描的表

-- 1）能找到哪些表被全表扫描的多，从而针对性的对这张表优化。
-- 2）优化内存使用率。将full_scann减少，更加能够提高内存利用率。能够让更多合理的数据进入内存，从而进一步的减少了slow
mysql> SELECT object_schema,
    ->        object_name,
    ->        count_read AS rows_full_scanned
    ->   FROM performance_schema.table_io_waits_summary_by_index_usage
    ->  WHERE index_name IS NULL
    ->    AND count_read > 0
    ->  ORDER BY count_read DESC;
+---------------+-------------+-------------------+
| object_schema | object_name | rows_full_scanned |
+---------------+-------------+-------------------+
| lt            | test        |               177 |
| lt            | apsm        |                40 |
+---------------+-------------+-------------------+
2 rows in set (0.00 sec)

找出未使用过的index和schema

mysql>  SELECT object_schema,
    ->         object_name,
    ->         index_name
    ->    FROM performance_schema.table_io_waits_summary_by_index_usage
    ->   WHERE index_name IS NOT NULL
    ->     AND count_star = 0
    ->     AND index_name <> 'PRIMARY'
    ->   ORDER BY object_schema, object_name;
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| lt            | apsm        | apsmactno  |
| lt            | sbtest1     | k_1        |
+---------------+-------------+------------+
2 rows in set (0.00 sec)

table_lock_waits_summary_by_table

table_lock_waits_summary_by_table表汇总由wait / lock / table / sql / handler工具生成的所有表锁等待事件。分组是table

附

An internal lock corresponds to a lock in the SQL layer. This is currently implemented by a call to thr_lock(). In event rows, these locks are distinguished by the OPERATION column, which has one of these values
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
An external lock corresponds to a lock in the storage engine layer. This is currently implemented by a call to handler::external_lock(). In event rows, these locks are distinguished by the OPERATION column, which has one of these values:
read external
write external

Socket Summaries

socket_summary_by_event_name

针对所有套接字I / O操作，每个套接字仪器，由wait / io / socket / *仪器生成的统计计时器和字节计数统计信息

socket_summary_by_instance

对于每个套接字实例的所有套接字I / O操作，wait / io / socket / *工具生成的聚合计时器和字节计数统计信息。当连接终止时，对应于它的socket_summary_by_instance中的行将被删除。

Memory Summaries

memory_summary_by_account_by_event_name

memory_summary_by_account_by_event_name具有USER，HOST和EVENT_NAME列。每行总结了给定帐户（用户和主机组合）和事件名称的事件

使用说明

对于每个线程的统计信息，适用以下规则:
当分配大小为N的检测内存块时，性能模式会对内存汇总表列进行这些更新：
COUNT_ALLOC：增加1
CURRENT_COUNT_USED：增加1
HIGH_COUNT_USED：如果CURRENT_COUNT_USED是新的最大值，则会增加
SUM_NUMBER_OF_BYTES_ALLOC：增加N
CURRENT_NUMBER_OF_BYTES_USED：增加了N
HIGH_NUMBER_OF_BYTES_USED：如果CURRENT_NUMBER_OF_BYTES_USED是新的最高值，则会增加N
 
当取消分配检测到的内存块时，性能模式会对内存汇总表列进行这些更新：
COUNT_FREE：增加1
CURRENT_COUNT_USED：减少1
LOW_COUNT_USED：如果CURRENT_COUNT_USED是新的最低值，则减少
SUM_NUMBER_OF_BYTES_FREE：增加N
CURRENT_NUMBER_OF_BYTES_USED：减少N
LOW_NUMBER_OF_BYTES_USED：如果CURRENT_NUMBER_OF_BYTES_USED是新的最低值，则减少N

对于较高级别的聚合（全局，按帐户，按用户，按主机），相同的规则适用于低和高水位的预期。
LOW_COUNT_USED和LOW_NUMBER_OF_BYTES_USED是较低的估算值。性能架构报告的值保证小于或等于运行时有效使用的最低存储量或大小。
HIGH_COUNT_USED和HIGH_NUMBER_OF_BYTES_USED是较高的估算值。性能架构报告的值保证大于或等于运行时有效使用的内存的最高计数或大小。
对于除memory_summary_global_by_event_name之外的汇总表中的较低估计值，如果内存所有权在线程之间传输，则值可能为负。

这里是一个估计计算的例子。但请注意，估计实施可能会发生变化：
线程1在执行期间使用1MB到2MB的内存，由memory_summary_by_thread_by_event_name表的LOW_NUMBER_OF_BYTES_USED和HIGH_NUMBER_OF_BYTES_USED列报告。
线程2在执行期间使用范围从10MB到12MB的内存，同样报告。

当这两个线程属于相同的用户帐户时，每个帐户的摘要估计这个帐户使用的内存在11MB到14MB之间。也就是说，较高级别聚合的LOW_NUMBER_OF_BYTES_USED是每个LOW_NUMBER_OF_BYTES_USED（假设最差情况）的总和。同样，较高级别汇总的HIGH_NUMBER_OF_BYTES_USED是每个HIGH_NUMBER_OF_BYTES_USED（假设最差情况）的总和。
11MB是一个较低的估计，只有当两个线程同时达到低使用标记时才能发生。
14MB是一个更高的估计，只有当两个线程同时达到高使用标记时才能发生。
该帐户的实际内存使用量可能在11.5MB到13.5MB之间。

memory_summary_by_host_by_event_name

memory_summary_by_host_by_event_name包含HOST和EVENT_NAME列。每行总结给定主机和事件名称的事件

memory_summary_by_thread_by_event_name

memory_summary_by_thread_by_event_name具有THREAD_ID和EVENT_NAME列。每行总结给定线程和事件名称的事件

memory_summary_by_user_by_event_name

memory_summary_by_user_by_event_name具有USER和EVENT_NAME列。每行总结给定用户和事件名称的事件

memory_summary_global_by_event_name

memory_summary_global_by_event_name有一个EVENT_NAME列。每行总结给定事件名称的事件

Status Variable Summaries

status_by_user

status_by_account

status_by_host

status_by_thread

14.Miscellaneous Tables

performance_timers

系统支持的统计时间单位

Threads

服务线程信息

示例

mysql> show processlist;
+-----+------+-----------+--------------------+---------+------+----------+-----------------------------------------+
| Id  | User | Host      | db                 | Command | Time | State    | Info                                    |
+-----+------+-----------+--------------------+---------+------+----------+-----------------------------------------+
| 221 | root | localhost | performance_schema | Query   |    0 | starting | show processlist                        |
| **222 | root | localhost | lt                 | Query   |    3 | updating | update test set col2='a' where col1='a' |**
| 223 | root | localhost | lt                 | Sleep   |    6 |          | NULL                                    |
+-----+------+-----------+--------------------+---------+------+----------+-----------------------------------------+
3 rows in set (0.00 sec)

host_cache

来自内部主机缓存信息

附录1：性能模式状态变量具有以下含义

• Previous
  MySQL推荐修改配置参考
• Next
  漫谈MySQL元数据锁