关于数据库:OLAP系统场景中GaussDBfor-MySQL借助PQNDP让性能提升高达百倍

44次阅读

共计 6306 个字符,预计需要花费 16 分钟才能阅读完成。

1. 背景
OLAP(On-Line Analytical Processing)联机剖析解决,利用在数据仓库,应用对象是决策者。OLAP 零碎强调的是数据分析,响应速度要求没那么高。

OLAP 数据量大,因为 OLAP 反对的是动静查问,所以用户兴许要通过将很多数据的统计后能力失去想要晓得的信息,例如工夫序列剖析等等,所以解决的数据量很大。对于海量数据的查问剖析,尽管不要求及时出数据分析后果,能尽量的产后后果也显得比拟重要了。如果一个 SQL 一天出不了后果,那决策者必定也会不想用。为了进步 SQL 解决效率,GaussDB(for MySQL)提供了并行查问的形式。

2. 并行查问
那什么是并行查问呢,官网介绍如下:

并行查问的根本实现原理是将查问工作进行切分并散发到多个 CPU 核上进行计算,充分利用 CPU 的多核计算资源来缩短查问工夫。并行查问的性能晋升倍数实践上与 CPU 的核数正相干,也就是说并行度越高可能应用的 CPU 核数就越多,性能晋升的倍数也就越高。

比方一个表 count(*)的执行过程,如下图:


3. 分布式海量数据进步查问效率
在海量数据场景下,个别会应用到分库分表,读写拆散,业务拆分,数据库分布式部署,各种中间件,各种缓存技术。对于曾经落地的数据而言,如果要剖析数据,如何进步查问效率呢,这又是一种比拟辣手的问题。对于此种问题,GaussDB(for MySQL)提出了一种新的解决形式,就是 NDP(Near Data Processing)。

NDP 次要针对是数据密集型查问,将提取列、条件过滤、聚合运算等操作向下推送给 GaussDB(for MySQL)的分布式存储层的多个节点并行执行。通过计算下推,晋升并行处理能力,缩小网络流量和计算节点的压力,晋升查询处理执行效率。

NDP 目前反对如下三类:

3.1、Projection
列裁剪,只有须要用到的相干列才被发送到查问引擎;

3.2、Aggregate
典型的聚合操作包含:count、sum、avg、max、min,只发送聚合后果(而不是所有元组)到查问引擎,count (*)是一个最常见的场景;

3.3、Select – where 子句过滤
常见的条件表达式:Compare(>=, <=,<,>,==)、Between、In、And/Or、Like。

将过滤表达式下推送到存储层,只有满足条件的行才会发送到查问引擎。

3.4、反对范畴
以后反对 InnoDB 表进行计算下推;

以后反对 COMPACT 或 DYNAMIC 行格局的表;

以后反对对 Primary Key 或 BTREE Index 计算下推,HASH Index 或 Full-Text Index 不反对计算下推;

以后只反对 SELECT 查问操作进行计算下推,其余 DML 语句不反对计算下推,INSERT INTO SELECT 也不反对计算下推;SELECT 加锁查问 (如 SELECT FOR SHARE/UPDATE) 不反对计算下推;

汇集操作下推以后反对 COUT/SUM/AVG/MAX/MIN 函数,带 GROUP BY 语句的汇集操作暂不反对下推;

表达式下推反对反对数值类型,日志和工夫类型和局部字符串类型(CHAR, VARCHAR),反对 utf8mb4, utf8 字符集;

表达式下推谓词反对比拟运算(<,>,=,<=,>=,!=), IN, NOT IN, LIKE, NOT LIKE, BETWEEN AND, AND/OR 等操作符。

NDP 官网架构图如下:

4. 测试环境

4.1、开启并行(PQ)
GaussDB(for MySQL) 开启并行;

全局参数 force_parallel_execute 来管制是否强制启用并行执行;

应用全局参数 parallel_default_dop 来管制应用多少线程并行执行;

应用全局参数 parallel_cost_threshold 来管制当数据规模为多大时开启并行执行。

mysql> explain

\-> select /*+ PQ(8) */

\-> l_returnflag,

\-> l_linestatus,

\-> sum(l_quantity) as sum_qty,

\-> sum(l_extendedprice) as sum_base_price,

\-> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

\-> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

\-> avg(l_quantity) as avg_qty,

\-> avg(l_extendedprice) as avg_price,

\-> avg(l_discount) as avg_disc,

\-> count(*) as count_order

\-> from

\-> lineitem t1

\-> where

\-> l_shipdate <= date‘1998-12-01’- interval‘90’day

\-> group by

\-> l_returnflag,

\-> l_linestatus

\-> order by

\-> l_returnflag,

\-> l_linestatus \\G



*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table:

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Parallel execute (8 workers, tpch.t1)

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Using where; Using temporary; Using filesort



2 rows in set, 1 warning (0.00 sec)

或用 hint 开启并行。

mysql> explain

\-> select /*+ PQ(8) */

\-> l_returnflag,

\-> l_linestatus,

\-> sum(l_quantity) as sum_qty,

\-> sum(l_extendedprice) as sum_base_price,

\-> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

\-> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

\-> avg(l_quantity) as avg_qty,

\-> avg(l_extendedprice) as avg_price,

\-> avg(l_discount) as avg_disc,

\-> count(*) as count_order

\-> from

\-> lineitem t1

\-> where

\-> l_shipdate <= date‘1998-12-01’- interval‘90’day

\-> group by

\-> l_returnflag,

\-> l_linestatus

\-> order by

\-> l_returnflag,

\-> l_linestatus \\G



*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table:

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Parallel execute (8 workers, tpch.t1)

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Using where; Using temporary; Using filesort



2 rows in set, 1 warning (0.00 sec)

4.2、开启 NDP
GaussDB(for MySQL)查看 NDP 是否开启。mysql> show variables like‘ndp_mode’;



±--------------±------+

| Variable_name | Value |

±--------------±------+

| ndp_mode | OFF |

±--------------±------+



1 row in set (0.00 sec)

GaussDB(for MySQL)开启 NDP 查问。mysql> set ndp_mode=on;



Query OK, 0 rows affected (0.00 sec)



mysql> explain select

\-> l_returnflag,

\-> l_linestatus,

\-> sum(l_quantity) as sum_qty,

\-> sum(l_extendedprice) as sum_base_price,

\-> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

\-> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

\-> avg(l_quantity) as avg_qty,

\-> avg(l_extendedprice) as avg_price,

\-> avg(l_discount) as avg_disc,

\-> count(*) as count_order

\-> from

\-> lineitem

\-> where

\-> l_shipdate <= date‘1998-12-01’- interval‘90’day

\-> group by

\-> l_returnflag,

\-> l_linestatus

\-> order by

\-> l_returnflag,

\-> l_linestatus\\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: lineitem

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Using pushed NDP condition ((`tpch`.`lineitem`.`L_SHIPDATE` <= ((DATE’1998-12-01’- interval‘90’day)))); Using pushed NDP columns; Using temporary; Using filesort



1 row in set, 1 warning (0.00 sec)

或者用 hint 开启 NO_NDP_PUSHDOWN,NDP_PUSHDOWN。

如下:

mysql> explain

\-> select /*+ NDP_PUSHDOWN(t1) */

\-> l_returnflag,

\-> l_linestatus,

\-> sum(l_quantity) as sum_qty,

\-> sum(l_extendedprice) as sum_base_price,

\-> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,

\-> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,

\-> avg(l_quantity) as avg_qty,

\-> avg(l_extendedprice) as avg_price,

\-> avg(l_discount) as avg_disc,

\-> count(*) as count_order

\-> from

\-> lineitem t1

\-> where

\-> l_shipdate <= date‘1998-12-01’- interval‘90’day

\-> group by

\-> l_returnflag,

\-> l_linestatus

\-> order by

\-> l_returnflag,

\-> l_linestatus \\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 555253035

filtered: 33.33

Extra: Using pushed NDP condition ((`tpch`.`t1`.`L_SHIPDATE` <= ((DATE’1998-12-01’- interval‘90’day)))); Using pushed NDP columns; Using temporary; Using filesort



1 row in set, 1 warning (0.00 sec)

测试的数据是通过 tpch 工具导入数据库中,100G 数据。

TPC-H 是业界罕用的一套 Benchmark,由 TPC 委员会制订公布,用于评测数据库的剖析型查问能力。TPC-H 查问蕴含 8 张数据表、22 条简单的 SQL 查问,大多数查问蕴含若干表 Join、子查问和 Group-by 聚合等等。Q17、Q20 不反对,所以没测。NDP 适宜全内存场景,大数据 IO 场景有成果。

以下为 64 并行数据,因为 NDP 不显著,暂意外。
Q13 pq_msg_queue_size=67108864, 为 64M.
Q18 optimizer_switch =‘subquery_to_derived=ON’.

5. 总结
GaussDB(for MySQL) 比官网 MySQL8.0 快,Q9 快的达到了 100 多倍,当然有些 SQL 差不多,比方第 q13、q21、q22。PQ+NDP 还有待优化,PQ 与 PQ+NDP 性能差不多。

开 64 并行有的 SQL 比开 30 并行要快,但并不是所有 SQL 都会晋升,有的反而变慢,比方 Q2、Q11 等,可能会有资源争用问题。

总的来说,GaussDB(for MySQL)NDP 和 PQ 的性能有了很大的得升,期待当前反对更多的 SQL 场景。

墨天轮原文链接:https://www.modb.pro/db/18859…(复制到浏览器或者点击这里立刻查看)

对于作者
黄江平,云和恩墨 MySQL DBA, Oracle OCP。现服务于金融证券行业,负责 MySQL 数据库 SQL 优化、数据库故障解决、备份复原、迁级降级、性能优化,有 10 年的数据库运维教训。

正文完
 0