Mysql之performance Schema
Performance schema是用于监控Mysql执行,具有如下特征:
1.用于在运行时探查Mysql Server的执行过程,是由Performance_schema引擎和 Performance_schema库实现的,侧重于性能数据,而非元数据【INFORMATION_SCHEMA】.
2.Performance Schema监控Server Event.an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements.
3.Performance Schema表的更改不会被写入binLog中。
Performance_schema,默认是开的。
如 :
要想查看Server当前正在干什么,可查看events_waits_current.查询历史最近的events,可查询:events_waits_history,events_waits_history_long.
可配置其保存条数:
[mysqld] performance_schema performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000
Performance Schema 运行时配置:
Performance Schema setup表包含监控配置的相关信息。
查看和更新事件定时器【event timer】,可参考: Performance Schema Runtime Configuration
The setup_objects
table controls whether the Performance Schema monitors particular table and stored program objects.
对setup_objects表的修改会立即生效。如:
会对所有自定义的库,表监控。
threads表记录每一个server线程,包括线程信息及指示monitor是否已经启动。若要使Performance schema监控一个线程,以下必须为TRUE:
setup_consumers表里的thread_instrumentation必须为YES,select * from setup_consumers where name ='thread_instrumentation';
The threads.INSTRUMENTED
column must be YES
.
Wait Instrument Components:
wait/io: wait/io/file wait/io/socket wait/io/table
wait/lock:wait/lock/table wait/lock/metadata/sql/mdl
23.9.7.1 The events_transactions_current Table