Performance Schema介绍
Performance Schema提供了有关MySQL服务器内部运行的操作上的底层指标。为了解释清楚Performance Schema的工作机制,先介绍两个概念。
第一个概念是程序插桩(instrument)。程序插桩在MySQL代码中插入探测代码,以获取我们想了解的信息。例如,如果想收集关于元数据锁的使用情况,需要启用wait/lock/meta-data/sql/mdl这个插桩。
第二个概念是消费者表(consumer),指的是存储关于程序插桩代码信息的表。如果我们为查询模块添加插桩,相应的消费者表将记录诸如执行总数、未使用索引的次数、花费的时间等信息。大多数人都将消费者表与Performance Schema紧密联系在一起。
当应用程序用户连接到MySQL并执行被测量的插桩指令时,performance_schema将每个检查的调用封装到两个宏中,然后将结果记录在相应的消费者表中。这里的要点是,启用插桩会调用额外的代码,这意味着插桩会消耗CPU资源。
插桩元件
在performance_schema中,setup_instruments表包含所有支持的插桩的列表。所有插桩的名称都由用斜杠分隔的部件组成。下面的例子展示了插桩的命名规则:
● statement/sql/select
● wait/synch/mutex/innodb/autoinc_mutex
插桩名称的最左边部分表示插桩的类型。因此,statement表示插桩类型是statement,wait表示插桩类型是wait,以此类推。
名称字段中的其余部分从左至右依次表示从通用到特定的子系统。select是sql子系统的一部分,属于statement类型。或者autoinc_mutex属于innodb,它是更通用的插桩类mutex的一部分,而mutex又是更通用的插桩类型wait的sync插桩的一部分。
大多数插桩名称是自描述型的。与示例中一样,statement/sql/select是一个SELECT查询,wait/synch/mutex/innodb/autoinc_mutex是InnoDB在自增列上设置的一个互斥体。setup_instruments表中还有一个DOCUMENTATION列,其中包含更多详细信息:
SELECT * FROM performance_schema.setup_instruments WHERE DOCUMENTATION IS NOT NULL
performance_schema的消费者表
存放事件的表名包含如下结尾:
● _current 当前服务器上进行中的事件。
● _history 每个线程最近完成的10个事件。
● _history_long 从全局来看,每个线程最近完成的10000个事件。注:_history和history_long表的大小是可配置的。
● events_waits:底层服务器等待,例如获取互斥对象。
● events_statements_* SQL查询语句。
● events_stages_* 配置文件信息,例如创建临时表或发送数据。
● events_transactions_* 事务相关。
● memory_summary_by_thread_by_event_name表保存了用户连接或任何后台线程的每个MySQL线程的聚合内存使用情况。
SQL的分析过程
重点关注的插桩元件
原始SQL
select film_id,film.description from sakila.film inner join (select film_id from sakila.film order by title limit 50,5) as lim using(film_id)
执行结果
51 A Insightful Panorama of a Forensic Psychologist And a Mad Cow who must Build a Mad Scientist in The First Manned Space Station
52 A Thrilling Documentary of a Composer And a Monkey who must Find a Feminist in California
53 A Epic Drama of a Madman And a Cat who must Face a A Shark in An Abandoned Amusement Park
54 A Awe-Inspiring Drama of a Car And a Pastry Chef who must Chase a Crocodile in The First Manned Space Station
55 A Awe-Inspiring Story of a Feminist And a Cat who must Conquer a Dog in A Monastery
查看运行过程
select * from `performance_schema`.events_statements_history where sql_text like 'select film_id%'\G
运行结果
mysql> select * from `performance_schema`.events_statements_history where sql_text like 'select film_id%'\G
*************************** 1. row ***************************
THREAD_ID: 124
EVENT_ID: 43
END_EVENT_ID: 44
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:95
TIMER_START: 2680897723222415000
TIMER_END: 2680897723733954000
TIMER_WAIT: 511539000
LOCK_TIME: 224000000
SQL_TEXT: select film_id,film.description from sakila.film inner join (select film_id from sakila.film order by title limit 50,5) as lim using(film_id)
DIGEST: f65da42ebf50607dfda40f3b31304775b671fb71122f0860f312ff1d330de99f
DIGEST_TEXT: SELECT `film_id` , `film` . `description` FROM `sakila` . `film` INNER JOIN ( SELECT `film_id` FROM `sakila` . `film` ORDER BY `title` LIMIT ?, ... ) AS `lim` USING ( `film_id` )
CURRENT_SCHEMA: sakila
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 5
ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 1
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 2
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 1311
要找出哪些语句需要优化,可以选择上述列中的任何一列,并将其与0进行比较。例如,要找到所有没有使用合适索引的查询,可运行以下命令:
SELECT *
FROM
`performance_schema`.events_statements_history_long
WHERE
NO_INDEX_USED > 0
OR NO_GOOD_INDEX_USED > 0
要查询所有创建了临时表的查询,可运行:
SELECT *
FROM
`performance_schema`.events_statements_history_long
WHERE
CREATED_TMP_DISK_TABLES > 0
OR CREATED_TMP_TABLES > 0
可以使用这些列值单独显示潜在的问题。例如,要查找所有返回错误的查询,可以使用条件WHERE errors > 0;要找到所有执行时间超过5秒的查询,可以使用条件WHERE TIMER_WAIT > 5000000000,等等。
挖掘可能存在问题的SQL
SELECT
*
FROM
`performance_schema`.events_statements_history_long
WHERE
ROWS_EXAMINED > ROWS_SENT
OR ROWS_EXAMINED > ROWS_AFFECTED
OR ERRORS > 0
OR CREATED_TMP_DISK_TABLES > 0
OR CREATED_TMP_TABLES > 0
OR SELECT_FULL_JOIN > 0
OR SELECT_FULL_RANGE_JOIN > 0
OR SELECT_RANGE > 0
OR SELECT_RANGE_CHECK > 0
OR SELECT_SCAN > 0
OR SORT_MERGE_PASSES > 0
OR SORT_RANGE > 0
OR SORT_ROWS > 0
OR SORT_SCAN > 0
OR NO_INDEX_USED > 0
OR NO_GOOD_INDEX_USED > 0