定位性能瓶颈

• 定位性能瓶颈
  • 1. Performance Schema
    • 1.1 Performance Schema表的分类
      • 1.1.1 语句事件记录表
      • 1.1.2 等待事件记录表
      • 1.1.3 阶段事件记录表
      • 1.1.4 事务事件记录表
      • 1.1.5 监视文件系统层调用的表
      • 1.1.6 监视内存使用的表
      • 1.1.7 动态对 performance_schema 进行配置的配置表
    • 1.2 启用 Performance Schema
    • 1.3 使用 Performance Schema
  • 2. 慢查询日志
    • 2.1 启用慢查询日志
    • 2.2 分析慢查询日志

1. Performance Schema

MySQL的Performance Schema是一套内存表, 用于跟踪MySQL的性能指标。它实际上使用PERFORMANCE_SCHEMA存储引擎, 用户操作performance_schema数据库中的表, Performance Schema仅对本地服务器有效, 所有的更改不会复制到其它的服务器.

Performance Schema的表大致可以分为6个组, 配置、当前事件、历史、实例、摘要, 及其它. 用户可以通过修改setup_%表的内容配置Performance Schema:

• setup_actors: 检测有哪些前台线程
• setup_instruments: 收集哪些服务器度量指标, 生产者
• setup_consumers: 监控的事件存储在哪里, 消费者
• setup_objects: 检测有哪些对象
• setup_threads: 检测有哪些线程分类

Performance Schema中存在大量的检测项目, 检测项目对应MySQL服务器的源代码检测点, 检测项目名称由一系列部件组成采用“/”分割, 从左到右, 从通用到具体:

wait/io/file/myisam/log stage/sql/closing tables

• idle: 检测的空闲事件
• error: 检测的错误事件
• memory: 检测的内存事件
• stage: 检测的阶段事件
• statement: 检测的语句事件
• transaction: 检测的事务事件
• wait: 检测的等待事件

1.1 Performance Schema表的分类

1.1.1 语句事件记录表

记录语句事件信息的表，包括:

• events_statements_current(当前语句事件表)
• events_statements_history(历史语句事件表)
• events_statements_history_long(长语句历史事件表)
• summary 表(聚合后的摘要表)。其中，summary表还可以根据账号(account)、主机(host)、程序(program)、线程(thread)、用户(user)和全局(glo`bal) 再进行细分。

show tables like 'events_statement%';

1.1.2 等待事件记录表

与语句事件记录表类似。

show tables like 'events_wait%';

1.1.3 阶段事件记录表

记录语句执行阶段事件的表，与语句事件记录表类似。

show tables like 'events_stage%';

1.1.4 事务事件记录表

记录与事务相关的事件的表，与语句事件记录表类似。

show tables like 'events_transaction%';

1.1.5 监视文件系统层调用的表

show tables like '%file%';

1.1.6 监视内存使用的表

show tables like '%memory%';

1.1.7 动态对 performance_schema 进行配置的配置表

show tables like '%setup%';

1.2 启用 Performance Schema

确保在mysql.cnf配置文件中启用, 并重启mysql:

[mysqld]
performance_schema=ON

重启后查看是否已经启用:

SHOW VARIABLES LIKE 'performance_schema';

1.3 使用 Performance Schema

USE performance_schema;

-- 查询最近10分钟的SQL执行次数
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_exec_time_sec FROM events_statements_summary_by_digest WHERE LAST_SEEN >= (NOW() - INTERVAL 10 MINUTE) ORDER BY COUNT_STAR DESC LIMIT 20;
-- 哪类的SQL执行最多?
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL的平均响应时间最多?
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL排序记录数最多?
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL扫描记录数最多?
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL使用临时表最多?
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL返回结果集最多?
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪个表物理IO最多?
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
-- 哪个表逻辑IO最多?
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
-- 哪个索引访问最多?
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
-- 哪个索引从来没有用过?
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
-- 哪个等待事件消耗时间最多?
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
-- 剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
-- 查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
-- 查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_long WHERE nesting_event_id = 1553;

2. 慢查询日志

2.1 启用慢查询日志

确保在mysql.cnf配置文件中启用, 并重启mysql:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

查看是否已经启用慢查询日志功能:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

2.2 分析慢查询日志

使用 pt-query-digest 工具 (Percona Toolkit 提供) 来解析慢查询日志并生成统计报告:

pt-query-digest /var/log/mysql/mysql-slow.log > slow-query-report.txt