Greenplum – 因为跑不快不敢删库跑路的DBA

Greenplum 列存原理

Greenplum 在表的存储结构上分为 HEAP 表、AO 表。

其中 HEAP 表是 PG 数据库原生存储格式，也是 Greenplum 的默认存储格式，但是只支持行存储，并且不能压缩。AO 表为 append optimized 表，支持行存储、列存储，支持多种压缩算法与压缩级别。

AO 表原理

Greenplum 4.3 之前 AO 表为 appendonly 表，只能追加，即只能进行插入，不能更新和删除。Greenplum 4.3 之后，AO 表优化为 append optimized 表，支持插入、更新、删除。插入即在最后追加记录；删除通过维护一张 bitmap 表，标记被删除的行；更新则为删除和插入 2 个操作的组合。所以 append optimized 表在大量更新或删除之后，标记为无效的数据不会自动清除，需要使用 vaccum 命令回收这部分空间。

bitmap 表如下，通过 bit 位以及偏移量来判定 AO 表上的某一行是否被删除（其中 82342 为 AO 表的 relid，bitmap 表就为 pg_aoseg.pg_aovisimap_{relid}）：

lzk=# select * from pg_aoseg.pg_aovisimap_82342;
 segno | first_row_no |                visimap                 

-------+--------------+----------------------------------------
     2 |      1081344 | \x0100000081002cbc002000008c00
     2 |      2064384 | \x0100000084002ff2ff00c80000000bfcbd80
     2 |      1114112 | \x0100000084002ff2ff266c00020000bfca48
     2 |      3440640 | \x0100000081002f5c000020008180
     2 |      4259840 | \x0100000084002ff2ff28bc00000100bfc9b4
     2 |      5079040 | \x01000000804022dc000800008380
     2 |      3211264 | \x0100000082002ff26dc00400000a34
     2 |      1638400 | \x0100000082002ff25dc00080000a74
     2 |       458752 | \x0100000084002ff2ff2ff223c00000020b54
(9 rows)

对于 heap 表，由 tupleid 决定每条记录的位置（tupleid 包含记录在文件中偏移位置），而对于 AO 表，数据是压缩的，没法确定 value 在文件中偏移位置，因此使用了 rownum，每条记录都有自己的 rownum，rownum 一直增长，每个 block 中记录了起始 rownum 以及 block 在文件中偏移位置，所以只要给定一个 rownum，就能定位到所在的 block，然后从 block 中就可以遍历到这个 rownum 对应的记录。

列存原理

列存只能是 AO 表，所以上一节中所有的 AO 表原理都适用。列存表，按列，每列对应一个或一批文件，每个列占用一个至多个文件，最多 128 个（预留 128 个 id），不同列的值不会同时出现一个文件。

列存表的文件存储，即（25854.1，25854.2 为第一列，25854.129、25854.130 为第二列，以此类推）：

[gpadmin@host10372181 /data1/primary/gpseg0/base/82341]$ll|grep 25854
-rw------- 1 gpadmin gpadmin          0 Jul 14 16:48 25854
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.1
-rw------- 1 gpadmin gpadmin       6168 Jul 16 14:14 25854.1025
-rw------- 1 gpadmin gpadmin    3108976 Jul 15 15:47 25854.1026
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.1153
-rw------- 1 gpadmin gpadmin    1476776 Jul 15 15:47 25854.1154
-rw------- 1 gpadmin gpadmin       5976 Jul 16 14:14 25854.1281
-rw------- 1 gpadmin gpadmin    2746744 Jul 15 15:47 25854.1282
-rw------- 1 gpadmin gpadmin       6160 Jul 16 14:14 25854.129
-rw------- 1 gpadmin gpadmin    2626616 Jul 15 15:47 25854.130
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.1409
-rw------- 1 gpadmin gpadmin    1360584 Jul 15 15:47 25854.1410
-rw------- 1 gpadmin gpadmin       5848 Jul 16 14:14 25854.1537
-rw------- 1 gpadmin gpadmin    2559240 Jul 15 15:47 25854.1538
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.1665
-rw------- 1 gpadmin gpadmin    1257912 Jul 15 15:47 25854.1666
-rw------- 1 gpadmin gpadmin       6368 Jul 16 14:14 25854.1793
-rw------- 1 gpadmin gpadmin    2106232 Jul 15 15:47 25854.1794
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.1921
-rw------- 1 gpadmin gpadmin   17856504 Jul 15 15:47 25854.1922
-rw------- 1 gpadmin gpadmin     279528 Jul 15 15:47 25854.2
-rw------- 1 gpadmin gpadmin       7016 Jul 16 14:14 25854.2049
-rw------- 1 gpadmin gpadmin   66013896 Jul 15 15:47 25854.2050
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.2177
-rw------- 1 gpadmin gpadmin   16055520 Jul 15 15:47 25854.2178
-rw------- 1 gpadmin gpadmin       6208 Jul 16 14:14 25854.2305
-rw------- 1 gpadmin gpadmin   22774616 Jul 15 15:47 25854.2306
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.2433
-rw------- 1 gpadmin gpadmin    7181688 Jul 15 15:47 25854.2434
-rw------- 1 gpadmin gpadmin       7032 Jul 16 14:14 25854.2561
-rw------- 1 gpadmin gpadmin   14922064 Jul 15 15:47 25854.2562
-rw------- 1 gpadmin gpadmin      11176 Jul 16 14:14 25854.257
-rw------- 1 gpadmin gpadmin   11304392 Jul 15 15:47 25854.258
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.2689
-rw------- 1 gpadmin gpadmin    5351744 Jul 15 15:47 25854.2690
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.2817
-rw------- 1 gpadmin gpadmin     201488 Jul 15 15:47 25854.2818
-rw------- 1 gpadmin gpadmin       6160 Jul 16 14:14 25854.2945
-rw------- 1 gpadmin gpadmin    3593248 Jul 15 15:47 25854.2946
-rw------- 1 gpadmin gpadmin       9824 Jul 16 14:14 25854.3073
-rw------- 1 gpadmin gpadmin   12571176 Jul 15 15:47 25854.3074
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3201
-rw------- 1 gpadmin gpadmin    3308464 Jul 15 15:47 25854.3202
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3329
-rw------- 1 gpadmin gpadmin    3258376 Jul 15 15:47 25854.3330
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3457
-rw------- 1 gpadmin gpadmin    1604200 Jul 15 15:47 25854.3458
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3585
-rw------- 1 gpadmin gpadmin    3483096 Jul 15 15:47 25854.3586
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3713
-rw------- 1 gpadmin gpadmin     354840 Jul 15 15:47 25854.3714
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3841
-rw------- 1 gpadmin gpadmin     329024 Jul 15 15:47 25854.3842
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.385
-rw------- 1 gpadmin gpadmin    1204480 Jul 15 15:47 25854.386
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.3969
-rw------- 1 gpadmin gpadmin     363400 Jul 15 15:47 25854.3970
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4097
-rw------- 1 gpadmin gpadmin     368080 Jul 15 15:47 25854.4098
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4225
-rw------- 1 gpadmin gpadmin     213120 Jul 15 15:47 25854.4226
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4353
-rw------- 1 gpadmin gpadmin     187264 Jul 15 15:47 25854.4354
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4481
-rw------- 1 gpadmin gpadmin     218800 Jul 15 15:47 25854.4482
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4609
-rw------- 1 gpadmin gpadmin    1687376 Jul 15 15:47 25854.4610
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4737
-rw------- 1 gpadmin gpadmin     761744 Jul 15 15:47 25854.4738
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.4865
-rw------- 1 gpadmin gpadmin   10054680 Jul 15 15:47 25854.4866
-rw------- 1 gpadmin gpadmin       6624 Jul 16 14:14 25854.513
-rw------- 1 gpadmin gpadmin    2743616 Jul 15 15:47 25854.514
-rw------- 1 gpadmin gpadmin       5280 Jul 16 14:14 25854.641
-rw------- 1 gpadmin gpadmin    1149328 Jul 15 15:47 25854.642
-rw------- 1 gpadmin gpadmin       5632 Jul 16 14:14 25854.769
-rw------- 1 gpadmin gpadmin    1861056 Jul 15 15:47 25854.770
-rw------- 1 gpadmin gpadmin       6160 Jul 16 14:14 25854.897
-rw------- 1 gpadmin gpadmin    2020608 Jul 15 15:47 25854.898

若只更新某1列或某几列，对于列存 AO 表，每个列文件之间并没有指针或其他关联，依靠相同的 rownum 来定位一行数据，所有列文件中该 rownum 数据全部作废，然后使用更新后的数据插入到新的一行中，此行中的所有 rownum 保留。因此，此时的数据更新，并不是只更新指定的列。所以单条 update 带来的 IO 开销很大，需要对每一个列文件都写入。

小结

对于 Greenplum 中的 AO 表，其实无论是行存或列存，更新所带来的性能降低是可以预估的。一条 SQL 语句的执行时间基本上可以简单的分为 IO 消耗的时间和计算时间。一张表在大量更新之后只会线性的增加 IO 时间，计算时间并不会受到影响。即假设表 A 通过 update 更新了 30% 的数据，同样的一条 SQL 执行时间的延长理论上不高于 30%。并且可以通过 vacuum 回收被删除数据的空间来恢复原有的查询性能。

Greenplum 备份方式

Greenplum 官方提供备份恢复工具：gpbackup、gprestore，可以对 Greenplum 集群进行逻辑备份恢复。同时可以使用pg物理备份工具pg_basebackup对master 以及所有 primary segment 分别单独备份恢复。

1. 测试环境

集群架构：

结构	机器IP	部署	使用硬盘
1 Master + 2 Segment Host	10.37.2.180(Master) 10.37.2.181(Seg) 10.37.2.182(Seg)	12 segment	SSD

机器配置：

机器IP	CPU(逻辑核数)	内存（RAM）	硬盘	操作系统
10.37.2.180	32C	128G	HDD+SSD	CentOS7.3
10.37.2.181	32C	128G	HDD+SSD	CentOS7.3
10.37.2.182	32C	128G	HDD+SSD	CentOS7.3

测试库详情：

库大小	表数量	索引数量	表数据大小
全量：672G	41个	90个	317G
全量+增量：888G（增量：216G）	41个	90个	369G（增量：52G）

2. 逻辑备份

2.1 全量备份

备份命令(备份详情请参考附录1)：

gpbackup --leaf-partition-data --dbname 库名 --backup-dir 备份目录 --jobs 并发数

恢复命令：

gprestore -backup-dir 备份目录 --create-db --timestamp 备份时间戳

耗时信息：

源库大小	备份文件大小	备份（1并发）	备份（2并发）	恢复（1并发）	恢复（2并发）
672G	119G	54m4s	30m19s	4h29m3s	2h31m18s

注：2并发时已达测试环境千兆网络瓶颈，故不再增加。

2.2 增量备份

逻辑备份支持增量备份，只需在备份命令中指定 –incremental 参数即可，增量备份仅支持 append only 表，heap 表即使指定增量参数，也进行全量备份。

备份命令：

gpbackup --leaf-partition-data --dbname 库名 --backup-dir 备份目录 --jobs 并发数 --incremental

恢复命令同全量备份，时间戳指定增量备份的时间戳即可

备份文件大小	备份时间	恢复时间	表数据恢复时间	Index
119G+54G	25m55s	5h29m35s	2h57m29s	2h32m2s

2.3 全量增量性能对比

备份	库大小	备份时间	备份速度
全量备份	672G	54m4s	12G/m
增量备份(仅增量)	216G	25m55s	8G/m

恢复	库大小	恢复时间	恢复速度
全量	672G	4h29m3s	149G/h
全量+增量	888G（增量：216G）	5h29m35s	161G/h

全量备份与增量备份的备份恢复速度差距不大。

3. 物理备份

3.1 备份

备份命令：

pg_basebackup -p segment1端口号 -X f -c fast -D 备份目录/gpseg0 --target-gp-dbid segment1_DBID -h 127.0.0.1
......
pg_basebackup -p segment12端口号 -X f -c fast -D 备份目录/gpseg5 --target-gp-dbid segment12_DBID -h 127.0.0.1

备份耗时：

源库大小	物理备份（串行）	物理备份（并行）
672G	25m47s	11m33s

注：串行与并行是指单台 segment host 内的各个 segments 之间，segment host 之间均为并行。

3.2 恢复

物理备份并非 Greenplum 官方推荐的方式，恢复需要对每个 segment 进行单独恢复。

恢复步骤：

1、将每个 segment 的备份文件分别拷贝至 master 与 standby master、primary 与 mirror 对应的数据目录中

2、在 mirror 数据目录中生成 recovery.conf 文件，内容如下，其中 host 与 port 需要根据不同 segment 设置为不同值：

standby_mode = 'on'
primary_conninfo = 'user=gpadmin host=sdw1 port=6000 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=gp_walreceiver'
primary_slot_name = 'internal_wal_replication_slot'

3、在 master 上执行 gpstart -a 启动数据库（注：此时 master 与 standby master、primary 与 mirror 的复制关系尚不存在）

4、登陆到 master 与每个 primary segment 创建复制槽，命令如下（segment 无法正常连接，需要使用 utility 模式）：

PGOPTIONS='-c gp_session_role=utility' psql -p 6000

执行创建复制槽：

select pg_create_physical_replication_slot('internal_wal_replication_slot');

5、在 master 上执行 gpstate -s 查看主备之间复制状态是否正常

3.3 增量

在上述物理备份的基础上，拷贝增量 WAL 日志（或解压归档日志）到 segment 的 pg_xlog 目录中之后启动数据库。查询 WAL 日志增量数据是否可以正常应用：HEAP 表可以正常应用 WAL 日志中的增量数据，AO 表无法应用，并且查询报错：

ERROR:  read beyond eof in table "t_col" file "base/16436/16396.1", read position 0 (small offset 10648), actual read length 0 (large read length 21296) (cdbbufferedread.c:211)  (seg0 slice1 10.243.145.205:6000 pid=1235) (cdbbufferedread.c:211)

4. 总结

根据如上的调查结果，对比两种备份方案，得出：

备份方案	优点	缺点
逻辑备份	1、Greenplum 官方推荐 2、保证全局数据一致性 3、备份、恢复方案简单 4、可支持增量备份	1、恢复速度非常慢（10TB 恢复超过 3 天） 2、备份速度较慢 3、只能恢复到备份时间点
物理备份	1、备份速度较快 2、恢复速度快	1、非 Greenplum 官方推荐备份方式 2、由于是逐节点备份，无法确保全局数据一致性 3、备份、恢复方案较复杂 4、只能恢复到备份时间点

由于 Greenplum 本身 master 与 segment 都自带高可用，segment 宕机 mirror 可以自动切换，master 宕机可以手动切换至 standby master，所以只有在主备都无法启动时需要使用备份进行恢复操作。无论物理备份还是逻辑备份，均只能恢复到备份时间点，若使用备份文件进行恢复，需要通过其他方式将备份点之后的数据补上。

附录1 逻辑备份详细

逻辑备份时，由 master 向每个 segment 发送类似如下命令 COPY 表数据：

COPY public.t_ao_co_zstd10 TO PROGRAM 'gzip -c -1 > /pgdata_test/gpback/gpadmin/gpseg<SEGID>/backups/20200727/2020072709201
2/gpbackup_<SEGID>_20200727092012_33282.gz' WITH CSV DELIMITER ',' ON SEGMENT IGNORE EXTERNAL PARTITIONS;

PyGreSQL入门

——简单整理翻译自官方文档：http://www.pygresql.org/contents/tutorial.html

创建数据库连接

只需import DB类并创建一个实例，填入相应的连接信息，例：

>>> from pg import DB
>>> db = DB(dbname='testdb', host='pgserver', port=5432, user='scott', passwd='tiger')

1 2	>>> from pg import DB >>> db = DB(dbname='testdb', host='pgserver', port=5432, user='scott', passwd='tiger')

如果省略参数，则会使用默认值：

dbname,user默认为当前系统用户，host为localhost，port为5432。

执行SQL语句

DB.query()

>>> db.query("create table fruits(id serial primary key, name varchar)")

1	>>> db.query("create table fruits(id serial primary key, name varchar)")

获取所有表名

DB.get_tables()，类似psql中\d:

>>> db.get_tables()
['public.fruits']

1 2	>>> db.get_tables() ['public.fruits']

获取表属性

DB.get_attnames()，类似psql中\d table：

>>> db.get_attnames('fruits')
{'id': 'int', 'name': 'text'}

1 2	>>> db.get_attnames('fruits') {'id': 'int', 'name': 'text'}

检查权限

DB.has_table_privilege()

>>> db.has_table_privilege('fruits', 'insert')
True

1 2	>>> db.has_table_privilege('fruits', 'insert') True

插入数据

DB.insert() –注：GP不支持

>>> db.insert('fruits', name='apple')
{'name': 'apple', 'id': 1}

1 2	>>> db.insert('fruits', name='apple') {'name': 'apple', 'id': 1}

该方法将完整的行作为字典返回，包括自增列。可以将字典赋值给变量：

>>> banana = db.insert('fruits', name='banana')

1	>>> banana = db.insert('fruits', name='banana')

批量插入数据

Connection.inserttable()

在插入大量数据时，批量插入性能比单条插入快很多

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
>>> data = list(enumerate(more_fruits, start=3))
>>> db.inserttable('fruits', data)

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()

>>> data = list(enumerate(more_fruits, start=3))

>>> db.inserttable('fruits', data)

查询数据

DB.query()

>>> print(db.query('select * from fruits'))
id|   name
--+----------
1|apple
2|banana
3|cherimaya
4|durian
5|eggfruit
6|fig
7|grapefruit
(7 rows)

>>> print(db.query('select * from fruits'))

id| name

--+----------

1|apple

2|banana

3|cherimaya

4|durian

5|eggfruit

6|fig

7|grapefruit

(7 rows)

将查询结果放入元组：

>>> q = db.query('select * from fruits')
>>> q.getresult()
... [(1, 'apple'), ..., (7, 'grapefruit')]

>>> q = db.query('select * from fruits')

>>> q.getresult()

... [(1, 'apple'), ..., (7, 'grapefruit')]

或字典：

>>> q.dictresult()
[{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]

1 2	>>> q.dictresult() [{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]

或named tuple：

>>> rows = q.namedresult()
>>> rows[3].name
'durian'

>>> rows = q.namedresult()

>>> rows[3].name

'durian'

使用DB.get_as_dict()可以轻松的将整张表数据加载到Python 字典中：

>>> db.get_as_dict('fruits', scalar=True)
OrderedDict([(1, 'apple'),
(2, 'banana'),
(3, 'cherimaya'),
(4, 'durian'),
(5, 'eggfruit'),
(6, 'fig'),
(7, 'grapefruit')])

>>> db.get_as_dict('fruits', scalar=True)

OrderedDict([(1, 'apple'),

(2, 'banana'),

(3, 'cherimaya'),

(4, 'durian'),

(5, 'eggfruit'),

(6, 'fig'),

(7, 'grapefruit')])

修改数据

DB.update()

>>> db.update('fruits', banana, name=banana['name'].capitalize())
{'id': 2, 'name': 'Banana'}
>>> print(db.query('select * from fruits where id between 1 and 3'))
id|  name
--+---------
1|apple
2|Banana
3|cherimaya
(3 rows)

>>> db.update('fruits', banana, name=banana['name'].capitalize())

{'id': 2, 'name': 'Banana'}

>>> print(db.query('select * from fruits where id between 1 and 3'))

id| name

--+---------

1|apple

2|Banana

3|cherimaya

(3 rows)

也可使用DB.query()

>>> db.query('update fruits set name=initcap(name)')
'7'

1 2	>>> db.query('update fruits set name=initcap(name)') '7'

返回值：‘7’表示更新的行数。

删除数据

DB.delete()

>>> db.delete('fruits', banana)
1

1 2	>>> db.delete('fruits', banana) 1

1表示删除的行数，再次执行就会显示0行被删除：

>>> db.delete('fruits', banana)
0

1 2	>>> db.delete('fruits', banana) 0

删除表

>>> db.query("drop table fruits")

1	>>> db.query("drop table fruits")

关闭连接

>>> db.close()

1	>>> db.close()

更高级的特性和详细信息，参阅：http://www.pygresql.org/contents/pg/index.html