标签：Greenplum

Greenplum数据库设计与优化

1 GP表设计

1.1 分布键

GP作为一个MPP数据库，分布键的选择对于后期使用的性能影响非常大。即使GP运行在单机环境，GP数据也是存放在多个segment上，所以必须要选择合适的分布键，特别是从单机数据库迁移到GP上的业务，因为单机数据库 不涉及分布键，所以迁移之前一定要设计好所有表的分布键。每张表必须显式指定分布键，避免使用默认分布。

  1. 尽可能的避免各个节点之间的数据交互以提高性能。两表关联查询时如果关联字段包含分布键，则数据可以在各自节点内完成运算；如果关联字段不包含分布键，则需要进行数据重分布，产生大量网络、内存开销，性能很低。
  2. 尽可能选择能使数据均匀分布在各个节点的分布键。如果数据分布不均匀，性能瓶颈就会出现在数据最多的节点上，导致其他节点很空闲，而都在等待该节点执行完成，造成性能低及资源浪费。

例如人员主题库类的业务，表之间查询、关联都是以人员为单位，而区分人员则使用的是身份ID。并且身份ID 散列度非常高，可以保证数据均匀分布。所以这类业务里的绝大多数表都非常适合使用身份ID字段作为分布键。

可以通过select gp_segment_id,count(*) from table_name group by gp_segment_id检查表数据是否均匀分发到每个segment上。

1.2 表分区

GP只支持range、list两种分区，不支持hash分区。GP使用表继承方式实现分区表，实际上每个分区都是一张单独的表，并且对于列存类型的表，每一列都需要一个单独的文件，每台服务器上的文件数=segment数*表数量*表分区数量*列数，所以为了避免文件系统上文件过多，仅对大表进行分区。

按照某个字段进行分区不会影响数据在节点上的分布，但是合理的选择分区字段可以让查询只扫描需要访问的分区，加快在单个数据节点的查询速度。

例1：

某张明细数据类的表，每天都有明细数据，经常按天查询明细数据，且数据保留N个月。则适合使用业务日期作为表分区键。按天查询数据可以只访问需要查询的分区，不会全表查询。删除过期数据时可以直接删除过期分区。

例2：

某张人员基本信息类的表，业务经常需要按省份进行统计各项指标。则适合使用省级代码作为表分区键。需要更新某省份数据时可以清空该省份分区后导入。并且统计时绝大多数操作都可以在分区内完成。

1.3 表存储方式

行存储是一行的所有列在磁盘上连续存储，所以一次IO可以从磁盘上读取整个行的数据。

列存储是在磁盘上将同一列的只保存在一起。

GP同时支持行存及列存。行列存储的特点和对比网上有很多文章可以参考，这里只列举一下各自适用的场景：

适合使用行存的场景：

  1. 数据经常需要修改
  2. 每次查询的字段占表全部列的大部分

适合使用列存的场景：

  1. 数据不需要修改或极少修改
  2. 表字段数多且经常查询其中少量字段
  3. 表数据量很大，需要压缩来提高IO性能

1.4 索引


GP则是通过索引设计提高数据检索性能 。

  1. 对于经常需要根据条件检索少量数据的高基数列建立索引
  2. 对于经常用于表关联的字段建立索引
  3. 频繁更新的列不要建立索引
  4. GP索引对数据加载性能影响很大，所以在加载大量数据前可以先删除索引，加载结束后重建索引

2 最佳实践

2.1 ANALYZE

ANALYZE用于收集表的统计信息，以优化执行计划。

如果使用INSERT、UPDATE、DELETE操作或修改大量数据之后，或导入数据后，建议执行ANALYZE。

创建新的索引之后也建议执行ANALYZE。

2.2 VACUUM

VACUUM用于回收表空间并可以再次使用，不会申请排他锁，其他会话仍可以正常读写该表。如果加上FULL参数，则会请排他锁，阻塞其他操作。

所以建议对于经常修改或删除的表，定期在闲时执行VACUUM操作，释放空间。不建议使用VACUUM FULL，可以使用create table … as操作，然后删除原表重命名新表。

2.3 加载数据

GP支持COPY方式入库和外部表方式入库。COPY方式所有数据会经过MASTER节点，由MASTER进行分发，性能较低，但是使用方便，对于少量数据可以使用COPY方式入库。外部表方式所有的segment节点都会并行加载数据，需要开启gpfdist服务并创建外部表，但是性能较高，对于大量数据建议使用外部表方式入库。

2.4 执行计划

GP采用基于成本的优化器来评估执行查询的不同策略，并选择成本最低的方法。和其他关系数据库系统的优化器相似，在计算不同执行计划的成本时，GP的优化器会考虑诸如关联表的行数、是否有索引、字段数据的基数等因素。还会考虑到数据的位置，尽可能在segment上完成任务，降低在不同segments间传输的数据量。

在一个查询运行速度比预期慢时，可以查看优选器生成的执行计划以及执行每一步的代价。这有助于确定哪一步最耗资源，进而可以修改查询或者模式，以便生成更好的计划。查看查询计划使用 EXPLAIN 语句。

执行详细描述了GP执行查询的步骤。查询计划是颗节点树，从下向上阅读，每个节点将它的执行结果数据传递给其上的节点。每个节点表示查询计划的一个步骤，每个节点都有一行信息描述了该步骤执行的操作，例如扫描、关联、聚合或者排序操作等，此外还有显示执行该操作的具体方法。例如，扫描操作可能是顺序扫描或者索引扫描，关联操作可能是哈希关联或者嵌套循环关联。

例：

postgres=# explain select datasetid,count(*) from t1 group by datasetid;

                                                      QUERY PLAN                                                       

———————————————————————————————————————–

 Gather Motion 8:1  (slice2; segments: 8)  (cost=8272479.90..8272484.15 rows=340 width=90)

   ->  HashAggregate  (cost=8272479.90..8272484.15 rows=43 width=90)

         Group By: t1.datasetid

         ->  Redistribute Motion 8:8  (slice1; segments: 8)  (cost=8272468.00..8272474.80 rows=43 width=90)

               Hash Key: t1.datasetid

               ->  HashAggregate  (cost=8272468.00..8272468.00 rows=43 width=90)

                     Group By: t1.datasetid

                     ->  Append-only Columnar Scan on t1  (cost=0.00..6662468.00 rows=40250000 width=33)

 Optimizer status: legacy query optimizer

（9 rows)

可以从上面执行计划看到会依次执行：

  1. Append-only Columnar Scan
  2. HashAggregate
  3. Redistribute Motion
  4. HashAggregate
  5. Gather Motion。

即：

  1. 在8个segments上对Append-only表进行列扫描
  2. 因为有汇聚函数，进行hash汇聚
  3. 未使用表分布键进行汇聚，所以需要进行数据重分布
  4. 重分布后进行hash汇聚
  5. master收集各节点结果并返回结果集

分析执行计划时首先要找估算代价较高的步骤。对比估算的行数及代价与操作实际需要处理的行数，以判断是否合理。

如果有分区表，判断分区裁剪是否有效。分区裁剪需要查询条件（WHERE 子句）必须和分区条件一样，不能包含子查询。

如果执行计划顺序不是最优的，检查数据库统计信息是否最新的。这时就要运行ANALYZE，生成最优计划。

注意计算倾斜。当执行诸如哈希聚合和哈希关联等操作符时，若不同Segment执行代价分布不均，就会发生计算倾斜。由于某些Segment比其他Segment使用更多的CPU和内存，性能下降明显。原因可能是关联、排序、聚合字段基数低或者数据分布不均匀。通过EXPLAIN ANALYZE 可以检测计算倾斜。每个节点都含有Segment处理的最多行数和所有Segment的平均行数。如果最大行数比均值大很多，那么至少有一个Segment需要处理更多的工作，因而有计算倾斜的可能性。

例：

postgres=# explain analyze select c1,count(*) from t1 group by c1;

                                                                      QUERY PLAN                                                                      

——————————————————————————————————————————————————

 Gather Motion 8:1  (slice1; segments: 8)  (cost=10821491.42..15184888.40 rows=145149885 width=90)

   Rows out:  311959999 rows at destination with 44285 ms to first row, 125533 ms to end, start offset by 42 ms.

   ->  HashAggregate  (cost=10821491.42..15184888.40 rows=18143736 width=90)

         Group By: c1

         Rows out:  Avg 38994999.9 rows x 8 workers.  Max 39002895 rows (seg2) with 46671 ms to first row, 75735 ms to end, start offset by 44 ms.

         Executor memory:  131464K bytes avg, 131464K bytes max (seg0).

         Work_mem used:  127901K bytes avg, 127901K bytes max (seg0). Workfile: (8 spilling, 0 reused)

         Work_mem wanted: 3020300K bytes avg, 3020885K bytes max (seg2) to lessen workfile I/O affecting 8 workers.

         (seg2)   39002895 groups total in 32 batches; 1 overflows; 40032573 spill groups.

         (seg2)   Hash chain length 4.7 avg, 19 max, using 8568049 of 8650752 buckets.

         ->  Append-only Columnar Scan on t1  (cost=0.00..6662468.00 rows=40250000 width=26)

               Rows out:  Avg 40250000.0 rows x 8 workers.  Max 40257783 rows (seg2) with 80 ms to first row, 10405 ms to end, start offset by 46 ms.

 Slice statistics:

   (slice0)    Executor memory: 413K bytes.

   (slice1)  * Executor memory: 131822K bytes avg x 8 workers, 131822K bytes max (seg0).  Work_mem: 127901K bytes max, 3020885K bytes wanted.

 Statement statistics:

   Memory used: 128000K bytes

   Memory wanted: 3021084K bytes

 Optimizer status: legacy query optimizer

 Total runtime: 144500.021 ms

（20 rows)

可以看出上面的执行计划在各个阶段内存和IO对比中max与avg差别都很小，说明数据分布很好，没有倾斜。

Greenplum高可用及实测

1 高可用方案

1.1 master高可用

master镜像是通过把primary master对应的standby master放置到不同的物理主机实现的。正常情况下只有primary master接受用户连接请求，standby master通过gpsyncagent进程（运行在standby master上）利用事务日志保持与primary master的同步。由于master上不存放任何用户数据，存放在其中的表不会频繁更新，因此同步是实时的。standby master所在主机除了复制进程外，没有正式master服务运行。当primary出现故障，standby master日志复制进程停止，可以手动激活standby master，使它切换成primary master。需要在primary与standby之上再加一层负载均衡或者VIP，应用在master切换后才可以不修改连接配置继续访问数据库。

1.2 segment高可用

segment级别的镜像是通过把primary segment对应的mirror segment放置到不同的物理主机实现的。正常情况下，只有primary segment的instance处于工作状态，所有primary segment上的变化通过文件块的复制技术拷贝到mirror segment。因此，存放mirror segment的主机上只有复制用的进程，而不存在mirror segment instance。一旦primary segment出现故障，mirror segment的复制进程停止，并启动instance，保证数据库的操作继续。

segment的故障检测是通过后台进程ftsprobe实现的，并可以进行自动容错，不需要DBA进行干预。ftsprobe的检测间隔是通过global服务参数gp_fts_probe_interval进行定义的，默认值是1分钟，通常这个参数的设置应该与参数gp_segment_connect_timeout保持一致。一旦ftsprobe进程发现primary segment出现故障，它会在数据字典中标记该segment已经停止。只有管理员对其进行恢复后，才会改变状态。

如果系统没有进行segment级别的镜像，当出现segment故障后，整个系统都将脱机，直到恢复故障segment后，才可以重新启动。

2 高可用搭建及测试

2.1 master高可用搭建

2.1.1 创建standby

1、在master上执行：

gpinitstandby -s lzk-8 -P 2345

2、并在终端中设置standby的数据目录：

Enter standby filespace location for filespace pg_system (default: NA):

> /data0/master_standby/gpseg-1

如出现如下提示，则表示创建成功

gpinitstandby:lzk-8:greenplum-[INFO]:-Successfully created standby master on lzk-8

2.1.2 手动切换

  1. 单独停止master

在master上执行：

gpstop -m

  • 启动standby

在standby上执行

gpactivatestandby -d $MASTER_DATA_DIRECTORY

2.1.3 master崩溃测试

  1. 模拟master崩溃

在master上

kill -9 所有master进程

此时活跃的客户端连接被断开，segments上正在执行的进程并不会停止，继续执行。standby不会自动进行切换，无法连接GPDB服务。

  • 启动standby

在standby上执行：

gpactivatestandby -d $MASTER_DATA_DIRECTORY

启动后，可以在新的端口及IP上接受新的连接。segments上遗留的进程执行完后不会进行提交，而是回滚。

2.1.4 测试结论

  1. 在master崩溃时尚未提交的所有事物都会失败。
  2. Greenplum本身并不会监控master的健康状态及异常恢复。所以集群恢复服务时间取决于发现异常后手动启动standby的时间。

2.2 segment高可用搭建

2.2.1 创建mirrors

1、在master上执行

gpaddmirrors -o mirror_config

会生成一个参考的配置文件，可自行修改

$ cat mirror_config

filespaceOrder=

mirror0=0:lzk-8:56000:57000:58000:/data0/mirror/gpseg0

mirror1=1:lzk-8:56001:57001:58001:/data0/mirror/gpseg1

2、创建mirror，执行：

gpaddmirrors -i mirror_config

如出现如下提示，则表示创建成功：

gpssd-[INFO]:-Mirror segments have been added; data synchronization is in progress.

gpssd-[INFO]:-Data synchronization will continue in the background.

gpssd-[INFO]:-

gpssd-[INFO]:-Use  gpstate -s  to check the resynchronization progress.

可以使用gpstate -s查看同步进度

2.2.2 segment崩溃测试

  1. 模拟segment崩溃

在一个segment上

kill -9 所有segment进程

此时正在执行的sql语句会报错失败，但是连接正常，可以继续使用。同时其他正常segment上正在执行的进程也会退出。异常segment对应的mirror会自动接管，并转为primary。使用gpstate可以看到warning：

[WARNING]:-Total primary segment failures (at master)                = 1

如当前正在执行的sql语句并没有涉及到全部segment，则不涉及到的segment崩溃不会导致当前sql失败。

  • 修复异常的primary segment

执行gprecoverseg

此时异常的primary以mirror身份加入集群，并同步最新的segment数据。primary与mirror身份目前是互换的，如果采用机器间互相备份的方式会影响性能。所以正常后需要恢复原来的身份。

恢复节点原来的身份：

执行gprecoverseg -r

2.2.3 测试结论

  1. segment崩溃时正在运行的事物会全部失败。
  2. mirror会自动接管服务而无需人工干预，接管过程中会有几秒的不可用时间；执行gprecoverseg操作时会有不可用时间，表现为语句执行卡顿；执行gprecoverseg -r操作时需要重启segment，所以也会有不可用时间，表现为语句执行报错。

3 集群扩容

3.1 创建新增segment

  1. 将新加入的host写入hostfile文件中
  2. 自动生成扩容配置文件

gpexpand -f ./etc/hostfile -D lzk，并输入需要增加的节点数及data目录

成功会生成类似配置文件：gpexpand_inputfile_20171211_153009

  • 看一下配置文件内容，可根据需要手动调整：cat gpexpand_inputfile_20171211_153009

lzk-8:lzk-8:50002:/data3/data2/gpseg2:4:2:p

lzk-8:lzk-8:50003:/data4/data2/gpseg3:5:3:p

  • 初始化新的segment

gpexpand -i gpexpand_inputfile_20171211_153009 -D lzk

3.2 数据重分布

  1. 可以查询gpexpand中的数据表来查看需要重分布的表及优先级
  2. 执行重分布命令，-d表示启动重分布命令后持续执行的时间

gpexpand -d 01:00:00 -D lzk

  • 全部完成后清除重分布使用的schema：gpexpand

gpexpand -c -D lzk

3.3 测试结论

重分布过程中严重影响数据库性能，故需必须要在业务闲时进行，如数据量非常大可以通过-d命令控制执行的时间段。集群只能扩容不能进行缩容，如一定要缩容需要进行数据导出、导入。

MySQL到Greenplum迁移分析

1 数据类型对比

  MySQL PostgreSQL comments
数值类型 TINYINT SMALLINT gp中无zerofill属性及unsigned类型，所以为了数据不越界需使用大一精度的数据类型匹配
SMALLINT SMALLINT
MEDIUMINT INTEGER
INT|INTEGER INTEGER
BIGINT BIGINT
TINYINT UNSIGNED SMALLINT
SMALLINT UNSIGNED INTEGER
MEDIUMINT UNSIGNED INTEGER
INT UNSIGNED BIGINT
BIGINT UNSIGNED NUMERIC(20)
BIT BIT
FLOAT REAL
FLOAT UNSIGNED DOUBLE PRECISION
DOUBLE|REAL|DOUBLE PRECISION DOUBLE PRECISION
DECIMAL|DEC|NUMERIC|FIXED NUMERIC
字符类型 CHAR CHARACTER|CHAR  
VARCHAR CHARACTER VARYING|VARCHAR  
TINYTEXT TEXT  
TEXT TEXT  
MEDIUMTEXT TEXT  
LONGTEXT TEXT  
BINARY|CHAR BYTE BYTEA  
VARBINARY BYTEA  
TINYBLOB BYTEA  
BLOB BYTEA  
MEDIUMBLOB BYTEA  
LONGBLOB BYTEA  
时间类型 DATE DATE  
TIME TIME  
YEAR 无  
DATETIME TIMESTAMP  
TIMESTAMP TIMESTAMP  
其他类型 BOOL|BOOLEAN BOOLEAN  
ENUM CREATE TYPE … AS ENUM  
SET 无  

2 语法对比

2.1 limit

MySQL：

or

 

Greenplum：

2.2 replace

MySQL：

Greenplum：

不支持该语法，需要使用函数实现，例：

2.3 insert into … on duplicate key update

MySQL：

Greenplum：

不支持该语法，需要使用函数实现，例：

2.4 select … into outfile

MySQL：

Greenplum：

2.5 自增列

MySQL：

列加auto_increment属性，例:create table a(id int auto_increment primary key)

获取当前值:select last_insert_id()

 

Greenplum：

字段类型使用serial,例:create table a(id serial primary key)

获取当前值:select currval(‘a_id_seq’)

2.6 注释

MySQL：

使用#或–

Greenplum：

使用–

2.7 执行存储过程

MySQL：

Greenplum：

Greenplum并无存储过程，使用函数代替，所以执行：

3 常用函数对比

3.1 时间函数

3.1.1 时间转字符串

MySQL：date_format（）

例：select date_format(now(),’%Y%m%d%H%i%s’)

Greenplum：to_char（）

例：select to_char(now(), ‘YYYYMMDDHH24MISS’)

3.1.2 字符串转时间

MySQL：str_to_date()

例：select str_to_date(‘20171120′,’%Y%m%d%H%i%s’)

Greenplum：to_date(),to_timestamp()

例：select to_date(‘20171120’, ‘YYYYMMDD’)

select to_date(‘20171120’, ‘YYYYMMDDHH24MISS’)

3.1.3 时间计算

MySQL：date_add()

例：select date_add(now(), interval 2 day)

Greenplum：直接计算

例：select now() + interval ‘2 day’

3.2 字符函数

3.2.1 空字符串处理

MySQL：ifnull

例：select ifnull(null,‘default’)

Greenplum：coalesce

例：select coalesce(null,‘default’)

3.2.2 字符串拼接

MySQL：concat()

例：select concat(‘abc’,‘def’)

Greenplum：||

例：select ‘abc’||’def’

4 数据迁移

Greenplum数据导入3种方式：

4.1 COPY命令

COPY需要经过master，仅建议在小数据量时使用。无法并行导入，在大量数据导入时效率很低，不过多介绍。

例：COPY tablea FROM ‘/data/tablea_data’;

4.2 使用外部表

外部表以及4.3中的gpload都需要使用gpfdist服务。

gpfdist是Greenplum自带的一个并行文件服务，原理如下图：

gpfdist为每个segment提供并行读写数据文件的服务。

 

1、先启动gpfdist服务，例：

-d 指定数据目录 -p指定服务端口 -l 指定日志文件

将数据文件放入该目录下

2、创建外部表，例：

  • 从外部表导入数据，例：

或者先创建，后导入：

4.3 gpload

通过配置yaml控制文件来进行数据导入，同样依赖gpfdist服务。

例：

1、编辑a.yml文件

2、进行导入：