MySQL5.7新增Performance Schema表

在前面有几篇博客我们已经介绍过MySQL5.6的Performance Schema,详细可点击博客1,博客2,博客3。在MySQL5.6里这些PS表已经包含了足够丰富的信息,帮助我们来分析MySQL的内部运行状态;另外由MySQL官方开发人员写的ps_helper是一组相当好用的ps配套工具,就算对Performance Schema不熟悉的同学,也能读懂其中的信息,感兴趣的同学可以自行谷歌下载。

当然本文的重点不在Performance Schema的使用上,主要是记录下MySQL5.7里新增的一些PS表,也是做个备忘,便于以后翻阅
 .
.

1.内存监控(MySQL5.7.2)

MySQL5.7.2开始支持内存监控的Performance Schema,包括分配内存所属的模块,操作的次数等等;通过这些信息我们可以看到内存究竟消耗在哪些地方;
与其他的类似,都可以通过配置表来动态打开/关闭
root@performance_schema 07:33:59>select count(*) from setup_instruments where name like ‘%memory%’;
+———-+
| count(*) |
+———-+
|      211 |
+———-+
1 row in set (0.00 sec)
总共增加了211个监控事件项,分为SQL/performance_schema/client/vio/mysys/sql/myisam/csv/memory/myisammrg/archive/blackhole/这几个模块,主要模块是SQL模块,用于监控Server层(共143个)
数据汇总结果表包含:
root@performance_schema 07:46:39>show tables like ‘%memory%’;
+—————————————–+
| Tables_in_performance_schema (%memory%) |
+—————————————–+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+—————————————–+
5 rows in set (0.00 sec)
和其他PS构件一样,都包含这几类,分别根据帐号,Host, user, 事件名等来进行分类,具体的自行点击官方文档:
这些汇总表,主要包含的列为:
COUNT_ALLOC/COUNT_FREE : 内存分配和释放的次数
SUM_NUMBER_OF_BYTES_ALLOC/SUM_NUMBER_OF_BYTES_FREE, 总的分配和释放的内存字节数
CURRENT_COUNT_USED: 等价于COUNT_ALLOC – COUNT_FREE ,表示当前正在使用的分配的内存次数
CURRENT_NUMBER_OF_BYTES_USED:等价于SUM_NUMBER_OF_BYTES_ALLOC – SUM_NUMBER_OF_BYTES_FREE, 表示当前尚未释放的内存字节数
LOW_COUNT_USED/HIGH_COUNT_USED :相对CURRENT_COUNT_USED的高低水位
–当分配内存时,如果CURRENT_COUNT_USED是一个新的最大值,则更新HIGH_COUNT_USED
–当释放内存时,如果CURRENT_COUNT_USED是一个新的最小值,则更新CURRENT_COUNT_USED
LOW_NUMBER_OF_BYTES_USED/HIGH_NUMBER_OF_BYTES_USED: 相对CURRENT_NUMBER_OF_BYTES_USED的高低水位
和上述类似
可以通过truncate 这些表的方式来重置统计信息;
关于高低水位这块,看的不是很明白,文档也语焉不详,先放一边,有空再看看
另外经常看到一些统计项的值为负数,这是有可能发生的,例如A线程分配的内存,被cache下来,被B线程使用并释放掉;这种情况就很难去跟踪这些内存分配的归属。
从memory_summary_global_by_event_name 输出来看,在纯写入负载下,binlog的内存分配是最频繁的;
root@performance_schema 09:49:49>select * from  memory_summary_global_by_event_name order by COUNT_ALLOC desc limit 4\G
*************************** 1. row ***************************
EVENT_NAME: memory/sql/Log_event
COUNT_ALLOC: 1099049
COUNT_FREE: 1099044
SUM_NUMBER_OF_BYTES_ALLOC: 460773085
SUM_NUMBER_OF_BYTES_FREE: 460772560
LOW_COUNT_USED: 3
CURRENT_COUNT_USED: 5
HIGH_COUNT_USED: 203
LOW_NUMBER_OF_BYTES_USED: 505
CURRENT_NUMBER_OF_BYTES_USED: 525
HIGH_NUMBER_OF_BYTES_USED: 166205
*************************** 2. row ***************************
EVENT_NAME: memory/sql/Sid_map::Node
COUNT_ALLOC: 547325
COUNT_FREE: 547303
SUM_NUMBER_OF_BYTES_ALLOC: 8757204
SUM_NUMBER_OF_BYTES_FREE: 8756848
LOW_COUNT_USED: -36483
CURRENT_COUNT_USED: 22
HIGH_COUNT_USED: 36941
LOW_NUMBER_OF_BYTES_USED: -583724
CURRENT_NUMBER_OF_BYTES_USED: 356
HIGH_NUMBER_OF_BYTES_USED: 591060
*************************** 3. row ***************************
EVENT_NAME: memory/sql/MYSQL_LOCK
COUNT_ALLOC: 274928
COUNT_FREE: 274827
SUM_NUMBER_OF_BYTES_ALLOC: 13196544
SUM_NUMBER_OF_BYTES_FREE: 13191696
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 101
HIGH_COUNT_USED: 101
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 4848
HIGH_NUMBER_OF_BYTES_USED: 4848
*************************** 4. row ***************************
EVENT_NAME: memory/sql/my_bitmap_map
COUNT_ALLOC: 274807
COUNT_FREE: 274799
SUM_NUMBER_OF_BYTES_ALLOC: 1099228
SUM_NUMBER_OF_BYTES_FREE: 1099196
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 8
HIGH_COUNT_USED: 100
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 32
HIGH_NUMBER_OF_BYTES_USED: 400
4 rows in set (0.01 sec)
在纯CPU Bound场景下,频繁的内存分配是比较昂贵的操作(内存分配释放本身在glibc层可能是有锁的,经常可以从pstack输出看到),通过内存监控,我们就可以有针对性的进行改进,例如对于频繁分配的内存,尽量cache重用。
.
.

2.Performance Schema开始支持存储过程,存储函数,触发器以及事件调度器(MySQL5.7.2)

表setup_instruments新增的监控事件项包括:statement/scheduler/eventstatement/sp/%
可以通过setup_objects表配置新的对象类型:PROCEDURE/EVENT/FUNCTION/TRIGGER
几个statement表中,增加了新列NESTING_LEVEL  来表示嵌套层次
增加了新的汇总表events_statements_summary_by_program 来聚合上述集中类型的统计信息
官方文档:
.
.

3.复制信息表(MySQL5.7.2)

主要增加了这几个表:
root@performance_schema 10:48:10>show tables like ‘replication%’;
+———————————————+
| Tables_in_performance_schema (replication%) |
+———————————————+
| replication_connection_configuration        |
| replication_connection_status               |
| replication_execute_configuration           |
| replication_execute_status                  |
| replication_execute_status_by_coordinator   |
| replication_execute_status_by_worker        |
+———————————————+
6 rows in set (0.00 sec)
当前的复制配置信息,包括host,user,port, ssl等信息
root@performance_schema 10:50:02>select * from replication_connection_configuration\G
*************************** 1. row ***************************
HOST: 10.238.88.35
PORT: 13316
USER: xx
NETWORK_INTERFACE:
AUTO_POSITION: 0
SSL_ALLOWED: NO
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
1 row in set (0.00 sec)
备库IO线程连接的状态信息:
root@performance_schema 10:50:32>select * from replication_connection_status\G
*************************** 1. row ***************************
SOURCE_UUID: 5f8b0702-5b00-11e3-9443-90b11c540089
THREAD_ID: NULL
SERVICE_STATE: OFF
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 2003
LAST_ERROR_MESSAGE: error connecting to master ‘xx@10.238.88.35:13316’ – retry-time: 60  retries: 151
LAST_ERROR_TIMESTAMP: 2013-12-28 21:50:48
1 row in set (0.00 sec)
replication_execute_configuration :当前是否配置了delay复制
replication_execute_status  上表对应的状态信息;
replication_execute_status_by_coordinator :用于展示分发线程的状态信息
mysql> select * from replication_execute_status_by_coordinator\G
*************************** 1. row ***************************
THREAD_ID: 50
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)
replication_execute_status_by_worker :当前所有worker线程的工作状态
这里我只开了4个worker线程
mysql> select * from replication_execute_status_by_worker\G
*************************** 1. row ***************************
WORKER_ID: 1
THREAD_ID: 51
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
WORKER_ID: 2
THREAD_ID: 52
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
WORKER_ID: 3
THREAD_ID: 53
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
WORKER_ID: 4
THREAD_ID: 54
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 954d1ddf-24c6-11e3-b2aa-74867ad41eb0:1262
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
4 rows in set (0.00 sec)
感觉这部分功能还有待完善,每个worker线程更具体的信息并没有打印出来,我们也很难通过这些表来判定各个worker线程的负载是否均衡
.
.

4.支持metadata lock监控(MySQL5.7.3)

自从MySQL5.5引入MDL后,就广为人诟病,尤其是mysqldump或者xtrabackup这些备份工具,执行FLUSH TABLES WITH READ LOCK时,从show processlist里看到的mdl信息;但我们又很难知道到底是哪些线程持有mdl,导致FTWRL跑不下去。
MySQL5.7.3引入的相关表可以有助于对类似问题进行troubleshouting
开启:
root@performance_schema 06:10:00>select * from setup_instruments where name like ‘wait/lock/metadata/sql/mdl’;
+—————————-+———+——-+
| NAME                       | ENABLED | TIMED |
+—————————-+———+——-+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+—————————-+———+——-+
1 row in set (0.00 sec)
主要引入了两个数据表:
存储mdl锁信息,例如BEGIN事务,并执行一条SELECT,则输出为:
root@performance_schema 06:38:34>select * from metadata_locks\G
*************************** 1. row ***************************            //另外一个session
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sbtest
OBJECT_NAME: sbtest1
OBJECT_INSTANCE_BEGIN: 48016660856256
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5542
OWNER_THREAD_ID: 32
OWNER_EVENT_ID: 20
*************************** 2. row ***************************                //当前线程
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: performance_schema
OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 48016862659792
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5542
OWNER_THREAD_ID: 34
OWNER_EVENT_ID: 103
2 rows in set (0.00 sec)
各个列的含义分别对应:
OBJECT_TYPE:mdl锁对象对应的子系统,包括GLOBALSCHEMATABLEFUNCTION,PROCEDURETRIGGEREVENT,COMMIT
OBJECT_SCHEMA及OBJECT_NAME:mdl锁对应的库名及表名;
LOCK_TYPE:锁的类型,包括INTENTION_EXCLUSIVESHARED,SHARED_HIGH_PRIOSHARED_READSHARED_WRITESHARED_UPGRADABLESHARED_NO_WRITE,SHARED_NO_READ_WRITE,  EXCLUSIVE
LOCK_DURATION:锁的持久化类型,当值为STATEMENT 或者TRANSACTION时,分别表示SQL结束或者事务结束时释放;当值为EXPLICIT 时,表示需要显式的释放mdl。
LOCK_STATUS:分别对应锁的不同状态(GRANTED/PENDING/VICTIM/TIMEOUT/KILLED)
SOURCE:该锁定义的代码行
OWNER_THREAD_ID:拥有该MDL的线程ID
OWNER_EVENT_ID:请求MDL的事件ID;
用于展示表锁,主要包括SERVER层及存储引擎层,所有打开的表都会显示在table_handles表中,例如:
root@performance_schema 07:05:38>select * from table_handles where OWNER_EVENT_ID != 0\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sbtest
OBJECT_NAME: sbtest1
OBJECT_INSTANCE_BEGIN: 48016660799584
OWNER_THREAD_ID: 36
OWNER_EVENT_ID: 21
INTERNAL_LOCK: WRITE
EXTERNAL_LOCK: WRITE EXTERNAL
1 row in set (0.00 sec)
该记录是对表sbtest1执行了LOCK TABLE sbtest1 write后的输出结果,其中:
INTERNAL_LOCK: READREAD WITH SHARED LOCKSREAD HIGH PRIORITYREAD NO INSERTWRITE ALLOW WRITEWRITE CONCURRENT INSERTWRITE LOW PRIORITYWRITE
---可以阅读 include/thr_lock.h中的注释来理解这些锁类型含义
EXTERNAL_LOCK :READ EXTERNAL,WRITE EXTERNAL
.
.

5.新的instrument类型:transaction(MySQL5.7.3)

在该版本之前,只支持stages/statements/waits , 5.7.3进行了扩展,对事务级别的事件进行监控
类似的,可以通过 setup_instruments, setup_consumers 以及 setup_timers 进行控制
新增的transaction表:
root@performance_schema 07:34:47>show tables like ‘%transaction%’;
+——————————————————+
| Tables_in_performance_schema (%transaction%)         |
+——————————————————+
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+——————————————————+
8 rows in set (0.00 sec)
那么,transaction表里会监控哪些内容呢,举一个简单的例子,开启一个事务,执行一条UPDATE, 不提交:
root@performance_schema 07:38:25>select * from events_transactions_current where thread_id=37 \G
*************************** 1. row ***************************
THREAD_ID: 37
EVENT_ID: 19
END_EVENT_ID: NULL
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: NULL
XID: NULL
XA_STATE: NULL
SOURCE: transaction.cc:150
TIMER_START: 5432419171316000
TIMER_END: NULL
TIMER_WAIT: NULL
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 18
NESTING_EVE
主要几个字段:
STATE:事务状态,包括ACTIVE ,COMMITTED , ROLLED BACK
XID/XID_STATE:XA事务信息
TIMER_START/TIMER_END/TIMER_WAIT:事务的耗时信息
ACCESS_MODE:READ ONLY/READ WRITE. 分别表示事务是只读的还是读写的
ISOLATION_LEVEL:事务的隔离级别
具体阅读官方文档:

原创文章,转载请注明: 转载自Simple Life

本文链接地址: MySQL5.7新增Performance Schema表

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *