Greenplum实践总结

实际使用greenplum中的一些问题或知识点，做一个汇总，会不断进行更新。

1.对于greenplum，当创建表时，若指定一个primary key，会将其默认设置为索引，此时，当进行大量数据的插入时，由于索引的存在，会导致插入时间变长。
另：当进行大量数据的插入时，需先删除掉索引，待数据插入完成后再建立索引。

2.在greenplum中，外部表不能指定 not null，也不能指定primary key等设置。

3.使用TPC-DS生成测试数据

./dsdgen -DIR /usr/datas -SCALE 1 -TERMINATE N 

以上命令用以生成1G测试数据，并将生成的测试存放在/usr/datas下
可以只生成某个表的数据，以加快数据的生成。

./dsdgen -DIR /usr/datas -SCALE 1 -TERMINATE N -table catalog_sales

只生成catalog_sales表

4.查看表中数据是否分布倾斜

select gp_segment_id,count(1) from tablename group by 1;

count(1)和count(*)类似，唯一的区别是，count(1)不统计null字段，而count(*)会统计null字段。

5.postgresql的两种转换方法
使用cast函数

select cast(‘5’ as int),cast(‘2018-10-08’ as date);

使用::表示

select ‘5’::int,’2018-08-08’::date;

6.Postgresql数值类型
boolean：1字节,true or false
整数：只有3种，smallint(2字节)，int(4字节)，bigint(8字节)。
精确的小数类型可用numeric,numeric(m,n),numeric(m)表示。
一个numeric类型的标度是小数部分的位数，而精度是全部数据的位数，也即小数点两边的位数的总和。
numeric：在pg中，如果不带任何精度与标度的声明numeric，则表示可以创建一个任意精度和标度的数值。
numeric(m,n)：精度为m，标度为n
numeric(m)：精度为m，标度为0

Real和double为变精度数据类型，占用空间分别为4字节和8字节。

序列类型：serical和bigserial分别为4字节和8字节的自增整数，有很大的操作范围。

7.postgresql的字符类型

类型	特性
varchar(n)	变长，有长度限制，最大存储字符为1G。存储空间为：4+实际的字符串长度。若没有指定长度n，表示可以存储任意长度的字符串。
char(n)	定长,不足补空白，最大存储字符为1G。存储空间为：4+实际的字符串长度。若没有指定长度n，表示只能存储1字节。
text	变长，无长度限制。

8.postgresql中的时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp[无时区]	8字节	包括日期和时间	4713 BC	5874897AD	1毫秒/14位
timestamp[含时区]	8字节	日期和时间，带时区	4713 BC	5874897AD	1毫秒/14位
interval	12字节	时间间隔	-178000000年	178000000年	1毫秒/14位
date	4字节	只用于日期	4713 BC	32767AD	1天
time[无时区]	8字节	只用于一日内时间	00:00:00	24:00:00	1毫秒/14位

9.表空间
在postgresql中，表空间实际上是为表指定的一个存储目录。在创建数据库、表、索引时就可以指定表空间，从而使得表、索引存储到表空间对应的目录下。

10.使用select * from pg_stat_activity;查看当前活动状态，使用
select pg_terminate_backend(procpid);可以终止某个活动。

11.greenplum中分区与分布的区别
分区是按照字段进行逻辑上的划分，为了减少数据扫描，从而提高查询等操作的效率。
分布是对字段进行物理分区，把数据分散到多个segment，以充分利用每个segment的物理资源，从而提高查询等操作的效率。

12.在生成表数据时，可以使用generate_series函数指定一个生产字段，来插入数据。比如对于数据库test_table，包含3个字段：id,name,blog，使用以下命令：

insert into test_table select generate_series(1,500) as id,’jack’,’xxxxx’;

13.当greenplum集群起不来的时候，可以先查看log中最近有什么操作，来查找集群不能启动的原因。
对于参数修改造成的起不来，可以先修改master节点的postgresql.conf文件，然后使用gpstart -m 命令启动master节点，启动master节点后，通过gpconfig命令修改参数后，执行gpstop -u，然后执行gpstart即可启动集群。

14.在进行插入数据，创建索引，更新表等操作后，应对表进行analyze操作，以更新统计数据，优化后续的查询等操作。

15.在具有索引的表中执行查询时，查看执行计划，有时候会发现并不走索引。造成这种现象的原因有：where条件字段没有和索引字段类型一致；满足条件的行太多等。
Greenplum强制走索引：
关掉seqscan，打开indexscan

set enable_seqscan=off;
set enable_indexscan=on;

16.在greenplum中，当打开optimizer后，对于小查询（毫秒级别的查询），性能会变差，且开启optimizer可能会影响数据加载速率。但当查询比较大时，开启optimzer会有一定的优势。

17.两种聚合方式
hash aggregate
根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表，几个聚合函数就有几个数组。相同数据量的情况下，聚合字段的重复度越小，使用的内存越大。
group aggregate
先将表中的数据按照group by的字段排序，在对排好序的数据进行全扫描，并进行聚合函数计算。消耗内存基本是恒定的。
选择
在SQL中有大量的聚合函数，group by的字段重复值比较少的时候，应该用group aggregate。

18.greenplum建立列表的一个例子：

create table catalog_sales_column(like catalog_sales) with 
(appendonly = true,compresstype =zlib,compresslevel=4,orientation=column);

19.对于列表的分区，表的每个分区的每一列都会对应一个文件，因此，防止打开的物理文件过多，需要控制分区数目。物理文件数=segments * columns *分区数目

20.遇到一个向表内copy数据时，报invalid byte sequence for encoding “UTF8”的问题。
原因是某个字段中的’\’没有转义，导致导入数据时，沿着\往后进行解析，从而报错。
解决办法：
1.数据从其他数据库导出时，考虑将\字段进行转义。
2.使用copy导入数据时，使用escape命令，将转义字符给替换掉。如将\替换为~。

21.pg执行copy等进行导入时，有时候遇到null字符导入错误问题，这个时候可以使用null ‘NULL’，也就是将数据文件的null字符识别为NULL（注意NULL一定要大写）。
当执行gpfdist等失败时，又找不到原因，可以提取少量数据进行copy，从而更快发现问题。

22.对于简单查询，若突然变得很慢，查看是否是由于打开optimzer的影响。

23.死锁问题定位-示例
1.通过查询pg_locks可以获取到对象的锁信息。

select * from pg_locks where relation=(select oid from pg_class where relname=’test_1’);

2.执行如下命令查出对应的线程号。

select * from pg_stat_activity;

3.根据需要使用函数kill死锁线程释放对应的锁。

select pg_terminate_backend(线程号);

24.对于数据库，若可以在断电前关闭数据库，尽量关闭数据库，以防止断电后恢复出现问题。