MySQL--performance schema学习
启用performance schema
在MySQL 5.6.6版本后，performance schema被默认打开 通常MySQL的二进制版本都默认支持PS, 如果使用编译源码安装，在cmake时需要使用参数DWITH_PERFSCHEMA_STORAGE_ENGINE=1来支持PS performance schema 是以存储引擎的方式实现的,因此可以使用以下两种方式确定PS是否可用 ## 检查方式1 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='PERFORMANCE_SCHEMA'\G ## 检查方式2 show engines \G 在MySQL配置文件中，可以使用performance_schema=ON来设置自动启用并初始化performance schema performance schema初始化完成后，可以像访问正常数据库一样使用performance_schema库。
配置performance schema
##=============================================================================## performance_schema库中以setup为前缀的表存放相关配置信息： setup_actors：配置用户纬度的监控，默认监控所有用户。 setup_consumers：配置events的消费者类型，即收集的events写入到哪些统计表中。 setup_instruments：配置具体的instrument，主要包含4大类：idle、stage/xxx、statement/xxx、wait/xxx： setup_objects：配置监控对象，默认对mysql，performance_schema和information_schema中的表都不监控，而其它DB的所有表都监控。 setup_timers：配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。（1秒＝1000000000000皮秒） ##=============================================================================## 更新表setup_consumers中数据后会立即生效，但不会持久化保存， 因此如果需要永久生效，需要在配置文件中进行配置，如： [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_XX存在层级关系，当上层被禁用后，即使下层开启，仍无法生效。 表setup_consumers里面的值有个层级关系： LEVEL1: global_instrumentation LEVEL2: thread_instrumentation,statements_digest LEVEL3: events_stages_current,events_statements_current,events_waits_current LEVEL4: events_stages_history,events_statements_history,events_waits_history LEVEL5: events_stages_history_long,events_statements_history_long,events_waits_history_long 以_current后缀的存放当前事件信息， 以_history和_history_long为后缀的表存放的是_current表的历史记录， _history表默认存放最近等待的10个事件，而_history_long默认存放最近1000个等待事件 _history表存放等待事件的数量可以通过参数来控制： SHOW VARIABLES LIKE 'performance_schema%history%size'; ##=============================================================================## 当PS启用后，并不是所有事件都会收集，可以查看setup_instruments表来查看那些事件被收集。 SELECT * FROM setup_instruments; 参考：https://dev.mysql.com/doc/refman/5.6/en/setup-instruments-table.html 对于setup_instruments表中记录，只有当ENABLED和ENABLED同时被激活状态下，才会收集事件 setup_instruments表中包含4大类数据：idle、stage/xxx、statement/xxx、wait/xxx. idle表示socket空闲的时间， stage类表示语句的每个执行阶段的统计， statement类统计语句维度的信息， wait类统计各种等待事件，比如IO，mutux，spin_lock,condition等。 ##=============================================================================## 参考连接： http://www.cnblogs.com/zhoujinyi/p/5236705.html http://keithlan.github.io/2015/07/17/22_performance_schema/
performance schema对象
##=============================================================================## cond_instances：条件等待对象实例 表中记录了系统中使用的条件变量的对象，OBJECT_INSTANCE_BEGIN为对象的内存地址。 ##=============================================================================## file_instances：文件实例 表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。 ## 查看打开次数较高的文件 SELECT * FROM file_instances ORDER BY OPEN_COUNT DESC LIMIT 10\G ##=============================================================================## mutex_instances：互斥同步对象实例 表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/*。 LOCKED_BY_THREAD_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。 ##=============================================================================## 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的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力。 ##=============================================================================## 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
performance schema表
##=============================================================================##
wait相关表 1.events_waits_current：记录了当前线程等待的事件 2.events_waits_history：记录了每个线程最近等待的10个事件 3.events_waits_history_long：记录了最近所有线程产生的10000个事件 ##=============================================================================##
stage相关表 1.events_stages_current：记录了当前线程所处的执行阶段 2.events_stages_history：记录了当前线程所处的执行阶段10条历史记录 3.events_stages_history_long：记录了当前线程所处的执行阶段10000条历史记录 ##=============================================================================## 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 ##=============================================================================## Connection相关表 1.users：记录用户连接数信息 2.hosts：记录了主机连接数信息 3.accounts：记录了用户主机连接数信息 ##=============================================================================## Summary 表： Summary表聚集了各个维度的统计信息包括表维度，索引维度，会话维度，语句维度和锁维度的统计信息 1，events_waits_summary_global_by_event_name：按等待事件类型聚合，每个事件一条记录 2，events_waits_summary_by_instance：按等待事件对象聚合，同一种等待事件，可能有多个实例，每个实例有不同的内存地址，因此 event_name+object_instance_begin唯一确定一条记录。 3，events_waits_summary_by_thread_by_event_name：按每个线程和事件来统计，thread_id+event_name唯一确定一条记录。 4，events_stages_summary_global_by_event_name：按事件阶段类型聚合，每个事件一条记录，表结构同上。 5，events_stages_summary_by_thread_by_event_name：按每个线程和事件来阶段统计，表结构同上。 6，events_statements_summary_by_digest：按照事件的语句进行聚合。 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维度） 11，table_io_waits_summary_by_table：根据wait/io/table/sql/handler，聚合每个表的I/O操作（逻辑IO纬度） 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 15，socket_summary_by_instance、socket_summary_by_event_name：socket聚合统计表。
