数据库内核月报 - 2019 / 11

MySQL · 最佳实践 · 今天你并行了吗?---洞察PolarDB 8.0之并行查询

前言

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

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

准备工作

打开并行查询

image.png

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

打开performance_schema

image.png

注意: 打开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上的输出结果 image.png

找到你关注的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:级别的层数。

举例来说,
13.png
14.png
其中:

  • 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:级别的层数。

15.png

在本例中

  • 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*/

16.png
其中:

  • 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




    其中:

  1. memory/performance_schema/parallel_query_class指为parallel_query事件类分配的内存,目前只有3种parallel_query事件类型,role/leader,role/worker及operator/gather,主要用于保存事件类的各种属性。
  2. memory/performance_schema/parallel_query_objects指为记录并行查询执行过程中的运行时信息所需对象所分配的内存,以page为单位分配,每个page包含128个parallel_query对象,此内存一经分配,除非系统重新启动,不能被释放。
  3. memory/performance_schema/parallel_operator_objects指为记录并行算子执行过程中的运行时信息所需对象所分配的内存,以page为单位分配,每个page包含128个parallel_operator对象,此内存一经分配,除非系统重新启动,不能被释放。
  4. memory/performance_schema/parallel_query_history指为保存历史并行查询的运行时信息所分配的内存,此处只为其分配存储指针的内存,默认每个线程10条历史记录,与线程对象同时分配。此内存一经分配,除非系统重新启动,不能被释放。
  5. COUNT_ALLOC指分配的次数,对于事件相关对象是可变的,当新分配page时,自动加1,此数值只能单调递增。
  6. 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有更多更好的自研特性出来,为客户提供更美好的用户体验。