本文共 10844 字，大约阅读时间需要 36 分钟。
在前面有几篇博客我们已经介绍过MySQL5.6的Performance Schema，详细可点击,,。在MySQL5.6里这些PS表已经包含了足够丰富的信息，帮助我们来分析MySQL的内部运行状态；另外由MySQL官方开发人员写的ps_helper是一组相当好用的ps配套工具，就算对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)
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)
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)
NESTING_LEVEL 来表示嵌套层次 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)
： 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)
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)
上表对应的状态信息； 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)
：当前所有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)
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)
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)
GLOBAL, SCHEMA, TABLE, FUNCTION, PROCEDURE, TRIGGER, EVENT,COMMIT OBJECT_SCHEMA及OBJECT_NAME：mdl锁对应的库名及表名； LOCK_TYPE：锁的类型，包括 INTENTION_EXCLUSIVE, SHARED, SHARED_HIGH_PRIO, SHARED_READ, SHARED_WRITE, SHARED_UPGRADABLE, SHARED_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； 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)
READ, READ WITH SHARED LOCKS, READ HIGH PRIORITY, READ NO INSERT, WRITE ALLOW WRITE, WRITE CONCURRENT INSERT, WRITE LOW PRIORITY, WRITE ---可以阅读 include/thr_lock.h中的注释来理解这些锁类型含义 , 以及 进行控制 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)
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
ACTIVE , COMMITTED , ROLLED BACK 转载地址：http://ygqyx.baihongyu.com/