2017-05-15

mysql5.6之performance schema初探

摘要：对比mysql5.6和5.7中的performance schema

Performance Schema是什么

MySQL Performance Schema是一个在底层监控MySQL Server的特性,下文简称PFS。它具有如下特点：
1、提供了一种对运行中的服务执行内部检查的方法，主要搜集性能数据，数据存储在performance_schema数据库中，该库中的表的存储引擎为PERFORMANCE_SCHEMA。它和INFORMATION_SCHEMA不同，INFORMATION_SCHEMA提供访问数据库元数据,关于MySQL服务器的信息（如数据库或表的名称,数据类型的列,或者访问权限等）
2、可以监控并搜集任何服务器的事件信息。通常一个事件可能是一个函数调用、一个操作系统等待、一个SQL执行阶段（如解析或排序或整条语句或一组语句）。还有几个存储引擎服务器的同步调用（如互斥对象）文件和表的I/O、表锁等事件搜集信息。
3、PFS事件和写入binlog的事件是不同的，写入binlog的事件是用来记录数据的修改情况。和事件调度器也不同（属于定时任务程序）。
4、PFS事件针对特定的MySQL服务器的实例。是属于本地服务器的，PFS事件的变化是不会写入二进制日志的，因此是不会被复制到从库的。
5、可以对当前事件及历史事件的记录并汇总，这样可以了解活动的时间和次数。可以显示特定活动线程或特定的对象或文件。
6、PFS存储引擎收集事件数据使用的是源码中的instrumentation points。
7、搜集到的事件被存储在PFS数据库中，这些表可以像其他表一样用SELECT语句进行查询。
7、PFS的配置可以通过SQL语句动态修改performance_schema数据库中的表。配置更改立即影响数据收集。
8、PFS中的表是内存表，他们是没有持久化存储到磁盘的。表的数据会随着服务的启动而增加，随着服务的关闭而丢失。
9、支持对所有平台的MySQL进行监控。
10、数据搜集可以通过修改源码增加instrumentation。
PFS旨在提供关于服务器执行的有用信息,同时对服务器性能产生最小的影响。实现这些的设计目标如下:
1、开启PFS不会导致服务器发生变化。比如：不会改变线程调度，也不会改变执行计划。
2、不需要重新调整之前已经分配好的内存大小。
3、对服务器不断的监控只需要很小的性能开销，开启PFS并不会导致服务器不可用。
4、没有新的关键词或语句，解析器就不变。
5、很容易添加新的instrumentation points（检测点）。

PFS配置

查看运行中的MySQL服务器是否开启了PFS相关变量值：

[root@mysql ~]# /opt/mysql/bin/mysqladmin variables|grep "performance_schema"
| performance_schema                                     | ON                                                                      
| performance_schema_accounts_size                       | 100                                                                     
| performance_schema_digests_size                        | 10000                                                                   
| 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                                                                      
| performance_schema_hosts_size                          | 100                                                                     
| performance_schema_max_cond_classes                    | 80                                                                      
| performance_schema_max_cond_instances                  | 137172                                                                  
| performance_schema_max_file_classes                    | 50                                                                      
| performance_schema_max_file_handles                    | 32768                                                                   
| performance_schema_max_file_instances                  | 302782                                                                  
| performance_schema_max_mutex_classes                   | 200                                                                     
| performance_schema_max_mutex_instances                 | 665360                                                                  
| performance_schema_max_rwlock_classes                  | 30                                                                      
| performance_schema_max_rwlock_instances                | 396616                                                                  
| performance_schema_max_socket_classes                  | 10                                                                      
| performance_schema_max_socket_instances                | 3020                                                                    
| performance_schema_max_stage_classes                   | 150                                                                     
| performance_schema_max_statement_classes               | 175                                                                     
| performance_schema_max_table_handles                   | 131072                                                                  
| performance_schema_max_table_instances                 | 81920                                                                   
| performance_schema_max_thread_classes                  | 50                                                                      
| performance_schema_max_thread_instances                | 3100                                                                    
| performance_schema_session_connect_attrs_size          | 512                                                                     
| performance_schema_setup_actors_size                   | 100                                                                     
| performance_schema_setup_objects_size                  | 100                                                                     
| performance_schema_users_size                          | 100                                                                     
[root@mysql ~]#

如果没有PFS的话需要重新编译MySQL加入：-DWITH_PERFSCHEMA_STORAGE_ENGINE=1。
MySQL5.6上的PFS默认是开启的，也可以显式的在my.cnf中设置关闭：

[mysqld]
performance_schema=OFF

查看PFS存储引擎：

root@(none) 03:09:19>show engines\G;
... ...
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)
... ...

监控表说明

1、setup_actors: How to initialize monitoring for new foreground threads 。监控什么样的用户（主机/用户/角色）
2、setup_consumers: The destinations to which event information can be sent and stored 。监控什么事件（哪些事件可监控，有：事件阶段/事件语句/事件事务/事件等待/…）
3、setup_instruments: The classes of instrumented objects for which events can be collected 。监控什么仪表（哪类仪表可监控）
4、setup_objects: Which objects should be monitored 。监控数据库的对象（表/函数/存储过程/事件/触发器）
5、setup_timers: The current event timer 。监控哪类事件计时器（空闲/等待/语句/事务/阶段）

监控策略

1、全系统监控: 监控项全部开启（便于分析不确定的问题，影响系统性能）
2、定制监控: 监控项部分开启（便于分析确定的问题，影响系统性能较少）
如：
禁止所有的文件类仪表:
UPDATE setup_instruments SET ENABLED = ‘NO’
WHERE NAME LIKE ‘wait/io/file/%’;

监控示例

系统“卡”住了，怎么办？关注：
mutex_instances.LOCKED_BY_THREAD_ID
rwlock_instances.WRITE_LOCKED_BY_THREAD_ID
1、假定线程1被卡住（thread 1 is stuck waiting for a mutex ）
SELECT FROM events_waits_current WHERE THREAD_ID = thread_1;
2、查询结果显示此线程在等待mutex A，则应该找出是谁持有mutex A
SELECT FROM mutex_instances WHERE OBJECT_INSTANCE_BEGIN = mutex_A;
3、查询结果显示此线程2在持有mutex A ，则应该查线程2在做什么事情
SELECT * FROM events_waits_current WHERE THREAD_ID = thread_2;
更多详见官网

本文标题:mysql5.6之performance schema初探

文章作者:一条大河

发布时间:2017-05-15, 15:19:03

最后更新:2017-05-16, 14:13:04

原始链接:http://riverdba.github.io/2017/05/15/mysql-performance-schema/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。