Greenplum朝花夕拾 之 神奇的列存储

在测试环境，运行了greenplum一段时间，如果说在某一个时刻，
真实的感受到了gp相对于oracle、sql server对于查询统计方面
的优势，就是在针对几个几百gb的关键表，由行存储，改为了
列存储加压缩之后，与这几个表相关的查询，相对于oracle平均提高了几十倍。
而且存储空间，节省了进一半。

下面是针对一个300GB的关键表进行列存储压缩后的空间占用情况：
列存储的表名为xxx_aot，普通行存储的表名为：xxx
select pg_relation_size('xxx_aot');
 pg_relation_size
------------------
     167418858408

select pg_relation_size('xxx');
 pg_relation_size
------------------
     341072150528

占用空间少了接近一半.

当然对于一个查询系统，空间占用并不是最重要的，实际查询性能才是需要关注的重点。

下面是一个与对xxx表有关的实际业务查询消耗的时间。

行存储运行时间：320s
列存储加压缩运行时间：5s

可见，性能提高了60多倍。

可是，天下没有白吃的午餐，greenplum的列存储方式，也存在一定的限制。
我现在遇到的最大的限制，是列存储，只能支持aot表（APPEND ONLY TABLE）。
aot表同时支持行存储和列存储，但是列存储，必须使用aot表。
AOT表虽然查询和加载数据的效率很高，但只能支持SELECT，INSERT，truncate操作，不支持UPDATE和DELETE操作。
所以它只适合存放经过处理基本最终无变化的历史数据，用来提供高效的查询访问。
如果遇到现在这种每天需要增量更新数据的情况，目前只能建一个行存储的临时表，
每天的更新都是针对该临时表进行，在晚上，
先将aot表truncate table，drop index，然后将临时表所有数据insert进aot表，再重建所有索引。
这样，每天晚上，虽然目前只将几个关键的表改为aot，但也有1t多的数据读写操作进行。
现在最然还没有出现问题，但未来怎样还无法预测。

所以，选择行存与列存，需要明确各自的限制和优势，仔细进行规划。
如果记录需要update/delete，那么只能选择非压缩的行存方式。
但是如果即需要update/delete，又需要提高查询性能，
目前，我也只想出来上面所说，全表truncate/insert的方式，
如果想节省时间，在io允许的情况下，可以采用多线程并行的方式进行，
我的办法是在shell中，使用& 和 wait的组合。

对于查询，如果选择的列的数量经常超过30个以上的列，那么也应该选择行存方式。
如果选择列的数量非常有限，并且希望通过较高的压缩比换取海量数据查询时的较好
的IO性能，那么就应该选择列存模式。
但是需要注意的是，如果是列存分区表，每个分区的每个列都会有一个对应的物理文件。比如有100个分区，100个列，
那么就会产生10000个文件，默认情况下，这些文件都会放在一个目录中。这可能会导致两方面的问题，
一个是访问数据时有可能超越linux上允许同时打开文件数量的上限。另一个是在同一个目录内·
存放过多的文件，会导致DDL命令的效率很差。解决这两个问题，一个是尽量在SQL中使用事实表的分区键作为条件，
或者尽量指定需要访问的事实表分区，以减少文件的访问。另外，应该创建不同的表空间（相当于不同的系统目录），把分区放入不同的表空间。

现在硬件系统往往IO效率跟不上CPU处理的速度，而数据仓库应用恰恰是IO敏感型的应用，所以很多数据仓库系统上，
我会看到CPU很闲，但是出现大量IO等待的情况。所以通过压缩，尤其是面向列压缩，允许我们牺牲一定的CPU效率进一步换取IO效率，
提高系统的的吞吐量。GP的AOT表允许使用两种压缩算法，一种是ZLIB，它的压缩率较高，对CPU的消耗较多，GP中可以指定1到9 9个级别。
1的压缩比最小，9最大。
例如：
CREATE TABLE SALES
   (PROD_ID numeric NOT NULL ,
    CUST_ID numeric NOT NULL ,
    TIME_ID DATE NOT NULL ,
    CHANNEL_ID numeric NOT NULL ,
    PROMO_ID numeric NOT NULL ,
    QUANTITY_SOLD numeric(10,2) NOT NULL ,
    AMOUNT_SOLD numeric(10,2) NOT NULL)
    WITH (appendonly=true,orientation=column,compresstype=zlib,COMPRESSLEVEL=5)
    distributed by (prod_id,cust_id,time_id,channel_id,promo_id);

另一种算法是QUICKLZ，它的压缩比小，这能设置1，造成的CPU负载也比ZLIB小。
CREATE TABLE SALES
   (PROD_ID numeric NOT NULL ,
    CUST_ID numeric NOT NULL ,
    TIME_ID DATE NOT NULL ,
    CHANNEL_ID numeric NOT NULL ,
    PROMO_ID numeric NOT NULL ,
    QUANTITY_SOLD numeric(10,2) NOT NULL ,
    AMOUNT_SOLD numeric(10,2) NOT NULL)
    WITH (appendonly=true,orientation=column,compresstype=QUICKLZ,COMPRESSLEVEL=1)
    distributed by (prod_id,cust_id,time_id,channel_id,promo_id);

我现在使用的是压缩算法是QUICKLZ。