performance_schema

PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数
插桩名称的最左边部分表示插桩类型，其余部分从左到右依次表示到特定的子系统

mysql> select * from performance_schema.setup_instruments where documentation is not null limit 5,5\G;
*************************** 1. row ***************************
         NAME: wait/synch/mutex/refcache/refcache_channel_mutex
      ENABLED: NO
        TIMED: NO
   PROPERTIES: 
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: A mutex to guard access to the channels list

1. 当前和历史数据

1.1 存放事件的表名

1）当前服务器上进行的事件：*_current
2)每个线程最近完成的10个事件：*_history
3)从全局看，每个线程最近完成的10000个事件：*_history_long

1.2 当前和历史数据

1）event_waits:底层服务器等待，例如获取互斥对象
2）event_statements:sql查询语句
3）event_stages:配置文件信息，例如创建临时表或发送数据
4）event_transactions:事务

--开启插桩
call sys.ps_setup_enable_instrument('statement/sql/select');
select * from events_statements_history\G;

1.3 查看没有合适索引的查询sql

select THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED from performance_schema.events_statements_history_long where NO_INDEX_USED > 0 or NO_GOOD_INDEX_USED > 0;

1.4 查询创建临时表的查询sql

select THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED from performance_schema.events_statements_history_long where CREATED_TMP_TABLES > 0 or CREATED_TMP_DISK_TABLES > 0;

1.5 查询执行报错的sql

select THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES  from performance_schema.events_statements_history_long where ERRORS > 0;

1.6 查询执行时间大于 5s的sql

select THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES  from performance_schema.events_statements_history_long where TIMER_WAIT > 5000000000;

2. 启用预处理语句检查

 select * from performance_schema.setup_instruments where documentation is not null\G;
--启用插桩
call sys.ps_setup_enable_instrument('statement/sql/prepare_sql');
call sys.ps_setup_enable_instrument('statement/sql/execute_sql');
call sys.ps_setup_enable_instrument('statement/com/Prepare');
call sys.ps_setup_enable_instrument('statement/com/Execute');

prepare stmt from 'sql_text'
set @xx='xxx';
execute stmt using @xx;

3. 查看语句延时情况

select EVENT_NAME,count(EVENT_NAME),sum(lock_time/1000000) as latency_ms from performance_schema.events_statements_history group by EVENT_NAME order by latency_ms desc;

4. 元数据锁

--开启插桩
call sys.ps_setup_enable_instrument('wait/lock/meta-data/sql/dml');

--查询元数据锁
select PROCESSLIST_ID,OBJECT_TYPE,LOCK_TYPE,LOCK_STATUS,SOURCE from metadata_locks join reheads on (OWNER_THREAD_ID=THREAD_ID) where OBJECT_SCHEMA='xxx' and OBJECT_NAME='xxx';

5. 内存使用查询

select * from sys.memory_global_total;
select thread_id tid,user, current_allocated ca,total_allocated from sys.memory_by_thread_by_current_bytes limit 9;

6. 查询线程状态

 select * from performance_schema.status_by_thread;