Greenplum或DeepGreen的管理数据

1. 关于GPDB的并发控制
与事务型数据库系统通过锁机制来控制并发访问的机制不同， GPDB(与PostgreSQL
一样)使用多版本控制(Multiversion Concurrency Control/MVCC)保证数据一致性。 这
意味着在查询数据库时，每个事务看到的只是数据的快照，其确保当前的事务不会
看到其他事务在相同记录上的修改。据此为数据库的每个事务提供事务隔离。


MVCC以避免给数据库事务显式锁定的方式，最大化减少锁争用以确保多用户环境
下的性能。在并发控制方面，使用MVCC而不是使用锁机制的最大优势是， MVCC对查询(读)的锁与写的锁不存在冲突，并且读与写之间从不互相阻塞。

锁模式

相关 SQL 命令

冲突

ACCESS SHARE

SELECT

ACCESS EXCLUSIVE

ROW SHARE

SELECT FOR UPDATE
SELECT FOR SHARE

EXCLUSIVE、 ACCESS EXCLUSIVE

ROW EXCLUSIVE

INSERT、 COPY

SHARE、 SHARE ROW EXCLUSIVE
EXCLUSIVE、 ACCESS EXCLUSIVE

SHARE UPDATE EXCLUSIVE

VACUUM (without FULL)
ANALYZE

SHARE UPDATE EXCLUSIVE、 SHARE
SHARE ROW EXCLUSIVE、 EXCLUSIVE
ACCESS EXCLUSIVE

SHARE

CREATE INDEX

ROW EXCLUSIVE、 SHARE UPDATE EXCLUSIVE
SHARE ROW EXCLUSIVE、 EXCLUSIVE
ACCESS EXCLUSIVE

SHARE ROW EXCLUSIVE

 

ROW EXCLUSIVE、 SHARE UPDATE EXCLUSIVE
SHARE、 SHARE ROW EXCLUSIVE
EXCLUSIVE、 ACCESS EXCLUSIVE

EXCLUSIVE

DELETE, UPDATE

ROW SHARE、 ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE、 SHARE ROW EXCLUSIVE
EXCLUSIVE、 ACCESS EXCLUSIVE

ACCESS EXCLUSIVE

ALTER TABL、DROP TABLE
TRUNCATE、 REINDEX
CLUSTER、 VACUUM FUL

ACCESS SHARE、 ROW SHARE、 ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE、 SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE、 ACCESS EXCLUSIVE

 

 

 

2. 插入新记录
create table products(product_no int , name varchar(30),price decimal(12,2))
distributed by (product_no);
INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);


INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);


在同时插入大量数据时，应该考虑使用外部表(CREATE EXTERNAL TABLE)或者COPY命
令。在装载大量记录时，这些装载机制比使用INSERT更高效。


AO表为批量装载做了优化。不建议在AO表上使用单条的INSERT语句。




3. 更新记录
在GPDB中使用UPDATE有如下的限制：
(1)  GP的DK不可以被UPDATE
update products set product_no = 33 where name = 'Milk';
ERROR:  Cannot parallelize an UPDATE statement that updates the distribution columns


(2)  在GPDB中不支持RETURNING子句
update products set name = 'Spark' where product_no = 3 returning *;
ERROR:  The RETURNING clause of the UPDATE statement is not supported in this version of Greenplum Database.


4. 删除记录(delete,truncate)
在GPDB中使用DELETE操作的限制：
在GPDB中不支持RETURNING子句
delete from products where product_no = 1 returning *;
ERROR:  The RETURNING clause of the DELETE statement is not supported in this version of Greenplum Database.


5. 使用事务
在GP中执行事务的SQL命令为：
(1) 使用BEGIN或START TRANSACTION开始一个事务块
(2) 使用END或COMMIT提交事务块
(3) 使用ROLLBACK回滚事务而不提交任何修改
(4) 使用SAVEPOINT选择性的保存事务点，之后可以使用ROLLBACK TO SAVEPOINT回滚到之前保存的事务点，还可以使用RELEASE SAVEPOINT来释放之前保存的事务点。


SQL标准定义了4个事务隔离级别。在GPDB中可以使用4个事务隔离级别的任何一个。但在内部，实际上只存在两个不同的隔离级别为：已提交读(read committed)和序列化(serializable)：
(1) 已提交读(read committed)
避免脏读,但可以出现不可重复读和幻读。
不可重复读：一事务对数据进行了更新或删除操作,另一事务两次查询的数据不一致
幻像读：一事务对数据进行了新增操作,另一事务两次查询的数据不一致


(2) 序列化(serializable)
串行化读，事务只能一个一个执行，避免了脏读、不可重复读、幻读。


(3) 未提交读(read uncommitted)
可以读脏数据。
读脏数据：一事务对数据进行了增删改,但未提交,有可能回滚,另一事务却读取了未提交的数据。


在GPDB中与已提交读等同。
(4) 可重复读(repeatable read)
避免脏读,不可重复读,允许幻像读。


在GPDB中与序列化等同。


在GPDB中缺省的事务隔离级别是已提交读。要使用不同的事务隔离级别，可
以在BEGIN事务时声明隔离界别，或者在事务开始之后使用SET TRANSACTION命令设置隔离级别。


6. 回收空间
由于MVCC事务并发模型的原因，已经删除或者更新的记录仍然占据着磁盘空间，
虽然其对于新的事务来说已经不可见。如果数据库有大量的更新和删除操作，其将
会产生大量的过期记录。定期的运行VACUUM命令可以删除这些过期的记录。


VACUUM命令还会收集表级别的统计信息，如记录数、占用磁盘页面数，所以在
装载数据之后对全表执行VACUUM是有必要的，这同样适用AO表。


配置子空间映射
过期的记录会被存在叫做自由空间映射(Free Space Map)的地方。自由空间映射的大小必须足够容纳数据库中的所有过期记录。如果尺寸不够大，超出自由映射空间的过期记录占用的空间将无法被VACUUM命令回收。


VACUUM FULL命令将回收所有过期记录，但这是一个很昂贵的操作并且其可
能会花费无法接受的时间长度在一张大表上来完成操作。如果自由映射空间已
经溢出，最好的做法是及时的使用CREATE TABLE AS命令来重建数据表并删
除旧的表。在GPDB中不建议使用VACUUM FULL命令。


最好将自由映射空间设置为一个合适的值。自由空间映射由下面的参数来设置：
max_fsm_pages 
设置在共享的自由空间映射表里自由空间会跟踪的最大数目的磁盘页面数。 每个页面槽位需要消耗六个字节的共享内存。这个设置必须大于 16 * max_fsm_relations。 缺省是 200000。 这个选项只能在服务器启动的时候设置。


这个参数告诉GP申请多大的内存空间用于保存数据文件的free space信息。不过Free Space Map是存在于内存中，大小毕竟是有限的，对于大量数据的删除＋插入，要么指定一个较大的max_fsm_pages,要么及时进行vacuum以整理表中的碎片。


max_fsm_relations
设置自由空间将在共享地自由空间映射里跟踪的最大数目的关系（表和索引）。 每个槽位大概要使用五十字节左右。缺省是 1000。 这个选项只能在服务器启动的时候设置。


总结：
这些参数控制共享的自由空间映射的尺寸， 自由空间映射太小会导致数据库随着时间推移消耗不合理的磁盘空间， 因为不在映射表里面的自由空间是不能重复使用的；这个时候再需要存储新数据的时候，会向操作系统要求更多的磁盘空间。 一个数据库范围内的 VACUUM VERBOSE 命令的最后几行现实的信息可以帮助判断当前的设置是否足够。 如果当前的设置太低，那么在这样的操作中也会打印一条 NOTICE 信息。