数据库内核月报－ 2019/11 - MySQL · 最佳实践 · 今天你并行了吗？—-洞察PolarDB 8.0之并行查询 - 《数据库内核月报》

前言
准备工作
性能分析
（1）检测是否并行
总结

前言

今天你并行了吗？在PolarDB 8.0中，我们领先MySQL官方版本，率先支持了SQL的并行执行，充分利用硬件多核多CPU的优势，大幅提高了类OLAP的查询性能，体现了自研PolarDB数据库极高的性价比。

如何深入分析SQL的并行执行情况，以协助DBA解决可能存在的性能瓶颈，是一个迫在眉睫的问题，因此PolarDB 8.0在Performance Schema中添加了对并行查询的支持，可以帮助DBA洞察并行执行过程中的各种情况，为后续的查询优化提供丰富的情报。下面我们就详细了解一下并行查询的分析过程。

准备工作

打开并行查询

若是等于0，意味着关闭并行模式。
当max_parallel_degree大于0时，表示允许最多有多少个worker参与并行执行，建议设置为实例cpu 核数的一半。比如对于8core32g 的实例，建议设置为4，对于16core128g的实例，建议设置为8，对于2核或4核的实例，建议关掉并行查询功能。

打开performance_schema

注意：打开performance_schema 需要重启实例，因此用户请在合适时间进行设置

开启并行查询相关的消费者consumer

后续操作都是由实例的超级用户，或者库performance_schema 已经授权给当前用户

update performance_schema.setup_consumers
set ENABLED= 'YES'
where name like 'events_parallel_query_current' ;
update performance_schema.setup_consumers
set ENABLED= 'YES'
where name like 'events_parallel_operator_current' ;
update performance_schema.setup_consumers
set ENABLED= 'YES'
where name like 'events_parallel_query_history' ;
update performance_schema.setup_consumers
set ENABLED= 'YES'
where name like 'events_parallel_operator_history' ;

启动时自动开启对并行查询相关事件的监测。

在my.cnf中添加以下行:

performance-schema-instrument='parallel_query%=ON'

性能分析

（1）检测是否并行

  当出现性能问题时，首先我们要确定查询是否真的是并行执行。虽然通过查看执行计划，我们可以看到执行计划是并行的执行计划，但这并不意味着在执行时一定会并行执行，不能并行执行的原因通常是资源不足，CPU、内存、线程等资源的限制都要求我们不能无限制的并行。如果并行不受限制的执行，可能会导致一个并行查询就耗光了所有的资源，从而导致其它所有任务都被堵塞，可用性也受到极大影响。
    因此针对这个问题，系统提供了一个参数max\_parallel\_workers来限制同时执行的并行数量。max\_parallel\_workers指在同一时刻用于并行执行的worker线程的最大数目（系统硬性设置为实例cpu数的4倍）。当超过max\_parallel\_workers时，之后就不会再产生并行执行计划，即使这些查询的执行计划中并行执行计划最优。
    我们不能实时的去查询每个执行计划，因此当查询执行完成后，需要一些方法去确定刚刚执行完成的查询是否以并行方式执行。在performance schema中的events\_statements\_current/events\_statements\_history可以查看某条查询是否以并行方式执行。
  如果发现当前情况下， 原本预期应该是并行的查询，但变成非并行查询， 需要进一步分析

运行的SQL 是否可以并行，查询并行查询文档，检查 “并行查询的限制” 一节，比如当前表记录数小于20000行，无法满足并行查询
检查当前系统负荷是否比较重，如果当前系统负荷，比如内存利用率已经接近实例上限或cpu负荷较重时，会退化为非并行查询。

查询当前连接的内部thread_id:

select thread_id from performance_schema.threads where processlist_id=connection_id();

然后执行以下SQL:

select th.thread_id,
       processlist_id,
       SQL_TEXT,
       PARALLEL
       from performance_schema.events_statements_history stmth,
       performance_schema.threads th
       where stmth.thread_id= th.thread_id
       order by PARALLEL desc;

下图是在控制台dms上的输出结果

找到你关注的SQL 的thread_id, PARALLEL 状态。

注意：

查询出来的thread_id 很重要，后续会大量依赖这个thread_id
直接从events_statements_xxx查到的thread_id与通过show processlist查到ID是两个完全不同的ID，processlist中的ID是connection_id，也可以认为是session_id，它也可以通过内部函数connection_id()查询到。如果想通过connection_id查询thread_id，可以通过以下SQL得到。

select thread_id from performance_schema.threads where processlist_id=connection_id();

events_statements_current表只显示最近执行的一条SQL的执行信息，因此不能在当前session中查询，因为如果在当前sesssion中查询的话，只能查询到当前这条查询语句的执行信息。但可以在其它session中查询，并通过thread_id定位到需要查询的SQL的执行信息。

如想查询在session-1上执行的并行查询信息：
1）在session-1上执行以下语句，得到session-1的connection_id=2143；
select connection_id();
2) 在session-1上执行并行查询
select sum(c3), c4 from test.t1 where c3 > 5000 group by c4;
3) 在session-2上查询session-1刚刚执行的并行查询的执行信息

select processlist_id,
       SQL_TEXT,
       PARALLEL
       from performance_schema.events_statements_current stmt,
       performance_schema.threads th
       where stmt.thread_id= th.thread_id
       and processlist_id= 2143\G

结果如下所示：

（2）查看并行查询的运行时信息

当我们确定一个查询以并行方式执行时，也许更想知道到底这个查询的并行执行过程是怎样的？有多少个并行算子？有多个并行执行的线程？每个线程都做了什么，扫描多少数据，又返回了多少数据？……
通过查询performance schema中的events_parallel_query_xxx和events_parallel_operator_xxx可以为你提供以上的所有信息。

events_parallel_query_xxx中保存并行查询在执行过程中的运行时信息，参与执行的每个线程，包括session线程和worker线程，每个线程都有自己的并行执行信息。因此一条并行查询会有多条并行执行信息。

events_parallel_query_current表中保存最近一次执行的并行查询的执行信息；
events_parallel_query_history表中保存最近执行的并行查询的历史执行信息，默认是每个session最近10条并行查询的历史信息。

当并行查询执行正在执行或执行完成后，可以通过events_parallel_query_xxx来查看并行执行的相关信息。

select ph.thread_id,
ph.event_id,
sh.parallel,
ph.timer_wait/1000000000000 as exec_time,
sh.sql_text,
ph.state,
ph.*
from performance_schema.events_parallel_query_history ph,
performance_schema.events_statements_history sh
where ph.thread_id= sh.thread_id
and ph.thread_id= 87267   /* 替换为前面查询的thread_id */
and ph.nesting_event_id = sh.event_id
\G

表events_parallel_query_xxx中包含以下内容：

THREAD_ID：指当前线程ID
EVENT_ID: 指当前事件ID，在每个线程中事件ID单调递增。
END_EVENT_ID: 指当前事件完成时的最大事件ID，当为NULL时表示当前事务尚在进行中。
EVENT_NAME：指当前正在进行中的事件，并行相关的事件以parallel_query开始，role表示执行角色，最后是角色名，可以为leader或worker。
PARENT_THREAD_ID：父线程ID，只有worker线程有父线程ID，leader线程的父线程ID为NULL。
OPERATOR_ID：与当前worker线程相关的并行算子ID，若是leader线程，则为NULL
WORKER_ID：Worker ID，如果是leader，则为NULL
STATE：执行状态，COMPLETED表示已完成，STARTED表示正在运行中
SOURCE：表示此事件嵌入代码的位置；
TIMER_START：表示此事件开始时刻，单位是皮秒picoseconds (trillionths of a second).
TIMER_END：事件结束的时刻
TIMER_WAIT：事件持续的时间
MYSQL_ERRNO：执行出错时输出错误码
RETURNED_SQLSTATE：执行出错时输出错误状态
MESSAGE_TEXT：执行出错时输出错误消息
ERRORS：是否正确执行的标志，出错时输出为1，否则为0
WARNINGS：警告消息的个数
ROWS_AFFECTED：受影响的行数
ROWS_SENT：返回的行数
ROWS_EXAMINED：检查的行数，不包含worker扫描的行数，通常等于所有worker返回的行数总和
CREATED_TMP_DISK_TABLES：在磁盘上创建的临时表个数
CREATED_TMP_TABLES：在内存中创建的临时表个数
SELECT_FULL_JOIN：JOIN时全表扫描的数目，此处不为0，应检查索引的合理性
SELECT_FULL_RANGE_JOIN：JOIN时RANGE扫描的数目
SELECT_RANGE：JOIN时第一个表采用RANGE扫描的数目
SELECT_RANGE_CHECK： RANGE扫描检查的数目
SELECT_SCAN：第一个表采用全表扫描的JOIN数目
SORT_MERGE_PASSES：归并排序的次数
SORT_RANGE：RANGE排序次数
SORT_ROWS：排序的行数
SORT_SCAN：通过全表扫描完成的排序数目
NO_INDEX_USED：若未使用索引扫描，为1；否则为0
NO_GOOD_INDEX_USED：若未找到好的能用的索引，为1；否则为0
NESTING_EVENT_ID：关联的上级事件的ID，可以通过此ID查询父事件即并行查询事件
NESTING_EVENT_TYPE：关联的上级事件的类型
NESTING_EVENT_LEVEL：级别的层数。

举例来说，

其中：

EVENT_NAME= parallel_query/role/leader, 表示此事件是并行查询事件，角色是leader
PARENT_THREAD_ID=NULL，仅对worker有效
OPERATOR_ID= NULL，仅对worker有效
WORKER_ID= NULL，仅对worker有效
STATE= COMPLETED，表示此并行查询已执行完成
TIMER_WAIT= 50230309628000，表示执行的时间，单位是皮秒，转成秒应该是50230309628000/1000000000000= 50.2303
ROWS_SENT= 4，表示此查询返回4行数据；
ROWS_EXAMINED= 8，表示leader线程共检查了74行数据
CREATED_TMP_TABLES= 1，表示创建了1个内存临时表
NESTING_EVENT_ID= 70，表示与其关联的父事件ID为，可通过此ID从events_statements_xxx表中得到此查询的SQL文本；
NESTING_EVENT_TYPE= STATEMENT，表示与其关联的父事件类型
NESTING_EVENT_LEVEL= 1，级别事件层级为1

（3）查看并行算子的运行时信息

events_parallel_operator_xxx表中提供并行查询中并行算子的相关信息，所谓并行算子，指可以由多个线程并行执行完成的任务，比如gather，gather会创建一组worker线程，由一组worker线程共同来完成扫描任务。

events_parallel_operator_current表中保存session中最近一次执行的并行查询相关的并行算子的执行信息。
events_parallel_operator_history表中保存session中最近执行的并行查询相关的并行算子的历史信息，默认是每个session最近10条并行查询的并行算子信息。

并行算子在执行计划中也可以查看到，如下所示：
explain select sum(c3), c4 from test.t1 where c3 > 5000 group by c4;

其中就是一个并行算子，它会执行Parallel scan，计划由32workers来完成。当然受限于资源的限制，实际上worker的数量可能会少于计划的32个workers.
当查询执行计划过程中或完成后，可以通过events_parallel_operator_current查看最近执行语句的并行算子相关信息。

select *
from performance_schema.events_parallel_operator_history
where thread_id= xxx /* 替换为前面查询的thread_id*/

表events_parallel_operator_xxx中包含以下内容：

THREAD_ID：指当前线程ID
EVENT_ID: 指当前事件ID，在每个线程中事件ID单调递增。
END_EVENT_ID: 指当前事件完成时的最大事件ID，当为NULL时表示当前事务尚在进行中。
EVENT_NAME：指当前正在进行中的事件，并行相关的事件以parallel_query开始，operator表示并行算子，最后是具体的算子名称，目前只有一种gather类型。
OPERATOR_ID：并行算子ID，每个并行查询可能有1个或多个并行算子，ID单调递增。
OPERATOR_TYPE：并行算子类型，目前只有一种类型gather。
STATE：执行状态，COMPLETED表示已完成，STARTED表示正在运行中。
SOURCE：表示此事件嵌入代码的位置；
TIMER_START：表示此事件开始时刻，单位是皮秒picoseconds (trillionths of a second).
TIMER_END：事件结束的时刻
TIMER_WAIT：事件持续的时间
PLANNED_DOP：并行计划的并行度，即计划最多可以有多少worker线程
ACTUAL_DOP：实际执行时的并行度，即实际执行时的worker线程数
PARTITIONED_OBJECT：并行执行的目标对象
NUMBER_OF_PARTITIONS：目标对象的内部分片数，与表的分区不同，不分区的表也可以内部分片，每个分片由一个worker来执行扫描，分片越多，并行度也越高，分片越少，并行度越低。
MYSQL_ERRNO：执行出错时输出错误码
RETURNED_SQLSTATE：执行出错时输出错误状态
MESSAGE_TEXT：执行出错时输出错误消息
ERRORS：是否正确执行的标志，出错时输出为1，否则为0
WARNINGS：警告消息的个数
ROWS_SENT：返回的行数
ROWS_EXAMINED：检查的行数，不包含worker扫描的行数，通常等于所有worker返回的行数总和
CREATED_TMP_DISK_TABLES：在磁盘上创建的临时表个数
CREATED_TMP_TABLES：在内存中创建的临时表个数
NESTING_EVENT_ID：关联的上级事件的ID，可以通过此ID查询父事件即并行查询事件
NESTING_EVENT_TYPE：关联的上级事件的类型
NESTING_EVENT_LEVEL：级别的层数。

在本例中

Event_name是parallel_query/operator/gather，表示是并行算子gather类型的事件
OPERATOR_ID=0，表示是本并行查询中的第1个并行算子
OPERATOR_TYPE = GATHER，表示是GATHER
STATE= COMPLETED表示此并行算子的执行已经完成
TIMER_WAIT= 50229944318000，是此算子的执行时间，单位是皮秒，转成秒应该是50229944318000/1000000000000= 50.2299s
PLANNED_DOP=32，表示计划的并行度为32
ACTUAL_DOP=32，表示实际执行时的并行度为32，此值有时会小于计划的并行度 – 注意此处真实情况下是否
PARTITIONED_OBJECT=lineitem：表示此gather执行并行扫描的表是lineitem表。
NUMBER_OF_PARTITIONS=2760，表示表lineitem内部有2760个分片，这些分片会依次分配到不同的worker上执行扫描，worker当一个分片扫描完成后，会自动申请下一个分片，直到所有分片全部处理完毕。
ROWS_SENT= 1，表示gather算子执行完成后，会产生1行数据。
ROWS_EXAMINED = 128，表示gather算子共检查了128行数据，这些数据由worker返回，因为每个worker上会优先执行已经下推的where条件。
CREATED_TMP_DISK_TABLES=0，表示没有在磁盘上产生临时表。
CREATED_TMP_TABLES=1，表示产生1个内存临时表。
NESTING_EVENT_ID=72，表示与此并行算子事件关联的父事件ID是72，这个ID可用于指定查询并行执行的主事件信息；
NESTING_EVENT_TYPE= PARALLEL_QUERY，表示与此并行牌子事件关联的父事件类型是PARALLEL_QUERY，即并行查询事件；
NESTING_EVENT_LEVEL=2，表示嵌套事件层级为2

（4）查看worker的运行时信息

每个并行算子可以有多个worker，每个worker独立完成部分子任务，如扫描某个表的若干个分片。每个worker也都有自己的运行时信息，这些信息也是保存在events\_parallel\_query\_xxx中，只是角色是worker而已。  
    因为worker是关联到并行算子的，所以可以通过并行算子的event\_id和thread\_id来过滤查询，

/纪君祥，原文中，有/

select *
from performance_schema.events_parallel_query_current
where parent_thread_id= xxx /*前面查询的thread_id*/
UNION
select *
from performance_schema.events_parallel_query_history
where parent_thread_id= xxx \G; /*前面查询的thread_id*/

其中：

EVENT_NAME= parallel_query/role/worker，表示是worker事件
PARENT_THREAD_ID= 100927，表示其父线程ID是100927
OPERATOR_ID= 0，表示此worker所属并行算子ID为0
WORKER_ID= 1，表示此worker的worker ID为1
TIMER_WAIT= 49935536065000，此worker的运行时间为49935536065000/1000000000000=49.94s
ROWS_SENT= 4，表示此worker返回4条数据
ROWS_EXAMINED= 18413405，表示此worker共扫描了18413405行数据；
CREATED_TMP_TABLES= 1，表示此worker创建了1个内存临时表
NESTING_EVENT_ID= 73，表示此worker的父事件ID为73，即其关联的并行算子的event_id为73
NESTING_EVENT_TYPE= PARALLEL_OPERATOR，表示worker的父事件类型是并行算子
NESTING_EVENT_LEVEL= 3，表示级联事件层级为3。

（5）查看内存消耗

select *
from performance_schema.memory_summary_global_by_event_name
where event_name like '%parallel%' \G

其中：

memory/performance_schema/parallel_query_class指为parallel_query事件类分配的内存，目前只有3种parallel_query事件类型，role/leader，role/worker及operator/gather，主要用于保存事件类的各种属性。
memory/performance_schema/parallel_query_objects指为记录并行查询执行过程中的运行时信息所需对象所分配的内存，以page为单位分配，每个page包含128个parallel_query对象，此内存一经分配，除非系统重新启动，不能被释放。
memory/performance_schema/parallel_operator_objects指为记录并行算子执行过程中的运行时信息所需对象所分配的内存，以page为单位分配，每个page包含128个parallel_operator对象，此内存一经分配，除非系统重新启动，不能被释放。
memory/performance_schema/parallel_query_history指为保存历史并行查询的运行时信息所分配的内存，此处只为其分配存储指针的内存，默认每个线程10条历史记录，与线程对象同时分配。此内存一经分配，除非系统重新启动，不能被释放。
COUNT_ALLOC指分配的次数，对于事件相关对象是可变的，当新分配page时，自动加1，此数值只能单调递增。
CURRENT_NUMBER_OF_BYTES_USED指当前已经分配的内存，单位是byte。

注意：

所有performance schema所需的内存，如果开启了performance schema，大部分内存是在系统初始化时就已经预先分配好的，与事件相关对象的内存可以按需分配，但只能按page增加，每个page包含若干对象，一经分配，除非系统重启，不能释放。
所有事件相关的对象按page分配后，将由container来统一管理，对象可以重用，只有当container中没有可用的对象时，才会申请分配新的page。
此处没有memory/performance_schema/parallel_operator_history，是因为所有并行算子都链接在leader对象，而所有的worker对象都链接在并行算子对象上。因此历史记录只需要保存leader对象的指针即可，不需要单独保存并行算子的历史记录。

（6）查看parallel query 对象

每个并行查询的并行度可能都不一样，每个并行查询包含的并行算子个数可能都不一样，因此每个并行查询所需记录运行时信息的parallel query对象和operator对象并不确定，为防止无限制的使用内存，系统做了以下限制：

每个并行查询最多只记录8个并行算子的运行时信息，多余的并行算子的运行时信息将被丢弃；
每个并行算子最多只记录32个worker的运行时信息，多余的worker运行时信息将被丢弃；
可以配置参数performance_schema_max_parallel_query_objects来限制parallel query的对象数目，以限制内存使用；
可以配置参数performance_schema_max_parallel_operator_objects来限制parallel operator的对象数目，以限制内存使用。
每个session中超出performance_schema_events_parallel_query_history_size的parallel query对象及parallel operator对象将被释放给container，以供将来重用。
当session退出时，当前session中记录的所有parallel query对象及parallel operator对象将被释放给container，以供将来重用。

此外，可以查看并行对象的使用情况，如
show status like '%objects_used';

总结

首先，我们需要了解的是，并行并不一定是最好的执行计划，在许多场景下，单线程顺序执行也许效率更高，执行更快。如表数据比较少，或能分配到worker上执行的任务比较少，或资源不足等。另外目前对并行的支持还有很多需要提升的地方，不是每个场景都适合并行执行。所以不要因为开启了并行，就期待对所有场景都有极大的提升，性能的提升还是需要DBA不断的去分析、去优化，并行执行只是其中的一个手段而已。

并行查询的支持只是一个开始，让我们期待PolarDB有更多更好的自研特性出来，为客户提供更美好的用户体验。