PS Instrument命名规范
PS instrument的命名类似于树形结构,最高层次的是instrument的类型,总共四种:idle/wait/stage/statement;再下一层的命名可能是一个子模块名(例如sync,io)等,再往下一层,例如sync,又可以划分成mutex/cond/rwlock,之后也许就是具体的某个同步锁对象,或者下一层的模块。
idle对象表示socket空闲信息,在setup_instrument表里只包含一列,名字就是idle。它生产的对应事件在中. mysql> select count(*) from setup_instruments where name like ‘idle%';
stage的命名规则为
stage/code_area
/stage_name
stage_name表示执行语句过程中的各个阶段,例如
storing result in query cache、Waiting for global read lock 等等. mysql> select count(*) from setup_instruments where name like ‘stage%';
SQL的下一级表示不同的SQL类型,例如statement/sql/xa_commit、statement/sql/rollback 另外文档标明statement/sql/select 用于CREATE DATABASE 和SELECT语句,暂未证实 COM则对应
enum_server_command 中的服务器command类型,例如statement/com/Ping 代表COM_PING wait类型的instrument应该是我们比较关注的部分,因为mysql本身的并发等待是非常值得关注的部分,也一般是导致服务器异常的罪魁祸首.另外wait类型还包括io相关instrument. 包括对文件的操作时间统计(wait/io/file/),socket操作(wait/io/socket); 另外还有表的IO操作(wait/io/table/sql/handler),包括对持久表和临时表的行级别操作,那些影响到行的操作(fetch,insert,delete..).和其他wait对象不同的是,表的wait对象可能包含其他等待时间,例如,表的I/O可能包含文件I/O或内存操作。因此在表
中对表的IO信息可能还包括wait/io/file对象, 就一个wait/lock/table/sql/handler ,表上的锁操作 synch的对象比较多,包括条件变量(wait/synch/cond)、mutex(wait/synch/mutex)、读写锁(wait/synch/rwlock) PS状态变量
PS提供了一些信息来显示由于内存限制导致某些统计信息没有计入PS中。 mysql> SHOW STATUS LIKE ‘perf%';
| Variable_name | Value | | Performance_schema_accounts_lost | 0 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_digest_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_hosts_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_session_connect_attrs_lost | 0 | | Performance_schema_socket_classes_lost | 0 | | Performance_schema_socket_instances_lost | 0 | | Performance_schema_stage_classes_lost | 0 | | Performance_schema_statement_classes_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Performance_schema_users_lost | 0 | 23 rows in set (0.00 sec) mysql> show variables like ‘%perf%'; +——————————————————–+——–+ | Variable_name | Value | +——————————————————–+——–+ | 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 | 20900 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 100824 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 35000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 12800 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | 10020 | | performance_schema_max_stage_classes | 150 | | performance_schema_max_statement_classes | 167 | | performance_schema_max_table_handles | 4000 | | performance_schema_max_table_instances | 12500 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 10100 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | 100 | | performance_schema_setup_objects_size | 100 | | performance_schema_users_size | 100 | +——————————————————–+——–+各个选项配置的文档见:http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html
我们也可以通过SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G 来看当前PS的内存占用 详细介绍,如果你的内存足够大,可以适当调整这些参数来存储更多收集的信息 另外还可以通过选项performance_schema_instrument = ‘%=on’在启动时打开所有的instrument
STATEMENT_DIGEST
当打开statements_digest时,PS会将相同类型的SQL在 表中聚集在
一起,SQL中的数据部分被“?”所代替,并调整空白部分,一些标示,例如表名和库名被保留。这有点和我们内部使用的myawr功能类似,将相似的SQL聚合起来展现。在statement对应的event表中,DIGEST列存储了SQL的md5值,DIGEST_TEXT存储了被处理过的SQL。 select * from sbtest where id < 10; select * from sbtest where id < 20; SCHEMA_NAME: sbtest
DIGEST: 4c3d9d47ee42d768152f70ee27f8e067 DIGEST_TEXT: SELECT * FROM `sbtest` WHERE `id` < ? SUM_TIMER_WAIT: 3477357000 MIN_TIMER_WAIT: 340011000 AVG_TIMER_WAIT: 1738678000 MAX_TIMER_WAIT: 3137346000 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2013-03-29 16:55:01 LAST_SEEN: 2013-03-29 16:55:04 DIEGEST_TEXT列的长度为1024,超过了就以字符串“…”代替。而在, 和
这三个表中记录了具体的SQL,而非聚合的结果
表有固定的大小,由参数performance_schema_digests_size控制,默认为10000条记录。当该表的记录满时,有一个特殊的列,其SCHEMA_NAME和DIGEST列设置为NULL,记录被加入到这个特殊的列中,如果观察该行记录的counter明显很高时,可能需要调整这个表的size。