[MySQL 5.6 ] Performance Schema学习:命名规范、状态变量及其他(2)

PS Instrument命名规范

 

PS instrument的命名类似于树形结构,最高层次的是instrument的类型,总共四种:idle/wait/stage/statement;再下一层的命名可能是一个子模块名(例如sync,io)等,再往下一层,例如sync,又可以划分成mutex/cond/rwlock,之后也许就是具体的某个同步锁对象,或者下一层的模块。

1.1.idle:
idle对象表示socket空闲信息,在setup_instrument表里只包含一列,名字就是idle。它生产的对应事件在socket_instances中.

mysql> select count(*) from setup_instruments where name like ‘idle%';

+———-+
| count(*) |
+———-+
|        1 |
+———-+
1 row in set (0.00 sec)
1.2.stage
stage的命名规则为stage/code_area/stage_name
其中code_area值为sql/mysys
stage_name表示执行语句过程中的各个阶段,例如storing result in query cache、Waiting for global read lock 等等.

mysql> select count(*) from setup_instruments where name like ‘stage%';

+———-+
| count(*) |
+———-+
|      108 |
+———-+
1 row in set (0.00 sec)
1.3.statement
其命名规则为statement/SQL或者COM
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 

1.4.wait
wait类型的instrument应该是我们比较关注的部分,因为mysql本身的并发等待是非常值得关注的部分,也一般是导致服务器异常的罪魁祸首.另外wait类型还包括io相关instrument.

wait/io  
包括对文件的操作时间统计(wait/io/file/),socket操作(wait/io/socket);
另外还有表的IO操作(wait/io/table/sql/handler),包括对持久表和临时表的行级别操作,那些影响到行的操作(fetch,insert,delete..).和其他wait对象不同的是,表的wait对象可能包含其他等待时间,例如,表的I/O可能包含文件I/O或内存操作。因此在表 events_waits_current 中对表的IO信息可能还包括wait/io/file对象,应该包含两行数据

wait/lock
就一个wait/lock/table/sql/handler ,表上的锁操作

wait/synch
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在 events_statements_summary_by_digest表中聚集在一起,SQL中的数据部分被“?”所代替,并调整空白部分,一些标示,例如表名和库名被保留。这有点和我们内部使用的myawr功能类似,将相似的SQL聚合起来展现。在statement对应的event表中,DIGEST列存储了SQL的md5值,DIGEST_TEXT存储了被处理过的SQL。
例如,执行如下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` < ?
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 3477357000
             MIN_TIMER_WAIT: 340011000
             AVG_TIMER_WAIT: 1738678000
             MAX_TIMER_WAIT: 3137346000
              SUM_LOCK_TIME: 284000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 28
          SUM_ROWS_EXAMINED: 28
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: 2
     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
                 FIRST_SEEN: 2013-03-29 16:55:01
                  LAST_SEEN: 2013-03-29 16:55:04

DIEGEST_TEXT列的长度为1024,超过了就以字符串“…”代替。而在events_statements_currentevents_statements_history events_statements_history_long这三个表中记录了具体的SQL,而非聚合的结果

events_statements_summary_by_digest表有固定的大小,由参数performance_schema_digests_size控制,默认为10000条记录。当该表的记录满时,有一个特殊的列,其SCHEMA_NAME和DIGEST列设置为NULL,记录被加入到这个特殊的列中,如果观察该行记录的counter明显很高时,可能需要调整这个表的size。

上一篇:访问外网 ML2 的配置 - 每天5分钟玩转 OpenStack(103)


下一篇:RedHat 6配置DNS服务实现主从同步与正反向解析