Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2900560
  • 博文数量: 199
  • 博客积分: 1400
  • 博客等级: 上尉
  • 技术积分: 4126
  • 用 户 组: 普通用户
  • 注册时间: 2008-07-06 19:06
个人简介

半个PostgreSQL DBA,热衷于数据库相关的技术。我的ppt分享https://pan.baidu.com/s/1eRQsdAa https://github.com/chenhuajun https://chenhuajun.github.io

文章分类

全部博文(199)

文章存档

2020年(5)

2019年(1)

2018年(12)

2017年(23)

2016年(43)

2015年(51)

2014年(27)

2013年(21)

2011年(1)

2010年(4)

2009年(5)

2008年(6)

分类: Mysql/postgreSQL

2014-06-25 21:21:43

1. 概述

cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景,数据分析的场景下数据是批量加载的。
这个扩展使用了Optimized Row Columnar (ORC)数据存储格式,ORC改进了Facebook的RCFile格式,带来如下好处:

压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。
列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。
跳过索引:为行组存储最大最小统计值,并利用它们跳过无关的行。

2. 使用

cstore_fdw的安装和使用都非常简单,可以参考官方资料。


注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做几个简单的性能对比,看看cstore_fdw究竟能带来多大的性能提升。

2.1 数据加载

2.1.1 普通表

CREATE TABLE tb1
(
    id int,
    c1 TEXT,
    c2 TEXT,
    c3 TEXT,
    c4 TEXT,
    c5 TEXT,
    c6 TEXT,
    c7 TEXT,
    c8 TEXT,
    c9 TEXT,
    c10 TEXT    
);

注:要和普通表的全表扫描作对比,所以不建主键和索引。

[postgres@node2 chenhj]$ time psql -p 40382  -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"  
COPY 10000000
1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
776inputs+0outputs (17major+918minor)pagefaults 0swaps

real    6m42.402s
user    0m15.174s
sys 0m14.904s

postgres=# select pg_total_relation_size('tb1'::regclass);
 pg_total_relation_size 
------------------------
             1161093120
(1 row)

postgres=# \timing
Timing is on.
postgres=# analyze tb1;
ANALYZE
Time: 11985.070 ms

插入1千万条记录,数据占用存储大小1.16G,插入耗时6分42秒,分析耗时12秒。

2.1.2 cstore表

$ mkdir -p /home/chenhj/data94/cstore

CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
CREATE FOREIGN TABLE cstb1
(
    id int,
    c1 TEXT,
    c2 TEXT,
    c3 TEXT,
    c4 TEXT,
    c5 TEXT,
    c6 TEXT,
    c7 TEXT,
    c8 TEXT,
    c9 TEXT,
    c10 TEXT    
)
SERVER cstore_server
OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',
        compression 'pglz');

[postgres@node2 chenhj]$ time psql -p 40382  -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"
COPY 10000000
1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k
968inputs+0outputs (20major+920minor)pagefaults 0swaps

real    7m35.520s
user    0m14.809s
sys 0m14.170s

[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore
-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore

postgres=# \timing      
Timing is on.
postgres=# analyze cstb1;
ANALYZE
Time: 5946.476 ms

插入1千万条记录,数据占用存储大小390M,插入耗时7分35秒,分析耗时6秒。
使用cstore列存储后,数据占用存储大小降到普通表的3分之1。需要说明的是,由于所有TEXT列填充了随机数据,压缩率不算高,某些实际的应用场景下压缩效果会比这更好。

2.2 Text列的like查询性能对比

2.2.1 普通表

清除文件系统缓存,并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94  -l logfile94 restart

[root@node2 ~]# free
             total       used       free     shared    buffers     cached
Mem:       2055508     771356    1284152          0       9900     452256
-/+ buffers/cache:     309200    1746308
Swap:      4128760     387624    3741136
[root@node2 ~]# echo 1 > /proc/sys/vm/drop_caches
[root@node2 ~]# free
             total       used       free     shared    buffers     cached
Mem:       2055508     326788    1728720          0        228      17636
-/+ buffers/cache:     308924    1746584
Swap:      4128760     381912    3746848

对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.42    0.00   95.40

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.55       330.68       212.08    7351441    4714848

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
 count  
--------
 100000
(1 row)

real    0m7.051s
user    0m0.001s
sys 0m0.004s

[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.43    0.00   95.39

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.90       381.53       211.90    8489597    4714956

耗时7.1秒,产生IO读1.14G,IO写108K。

不清文件系统缓存,不重启PostgreSQL,再执行一次。消耗时间降到1.6秒,几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.43    0.00   95.39

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.81       332.20       213.06    7350301    4714364

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
 count  
--------
 100000
(1 row)

real    0m1.601s
user    0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.43    0.00   95.38

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.80       332.12       213.01    7350337    4714364

2.2.2 cstore表

清除文件系统缓存,并重启PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94  -l logfile94 restart
[root@node2 ~]# echo 1 > /proc/sys/vm/drop_caches

对Text列执行like查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.38    0.00   95.45

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.12       376.42       209.04    8492017    4716048

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
 count  
--------
 100000
(1 row)


real    0m2.786s
user    0m0.002s
sys 0m0.003s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.38    0.00   95.44

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             58.12       378.75       208.89    8550761    4716048

耗时2.8秒,产生IO读59M,IO写0K。执行时间优化的虽然不是太多,但IO大大减少,可见列投影起到了作用。

不清文件系统缓存,不重启PostgreSQL,再执行一次。消耗时间降到1.4秒,几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.36    0.00   95.47

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.75       376.33       207.58    8550809    4716524

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
 count  
--------
 100000
(1 row)

real    0m1.424s
user    0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.80    0.00    0.38    3.36    0.00   95.47

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.70       375.96       207.38    8550809    4716588

2.3 对Int列执行=查询

2.3.1 普通表

清除文件系统缓存,并重启PostgreSQL后
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94  -l logfile94 restart
[root@node2 ~]# echo 1 > /proc/sys/vm/drop_caches

对Int列执行=查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.33    0.00   95.50

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.25       373.21       205.67    8560897    4717624

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
 count 
-------
     1
(1 row)


real    0m6.844s
user    0m0.002s
sys 0m0.006s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.34    0.00   95.49

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.60       422.57       205.54    9699161    4717708

耗时6.8秒,产生IO读1.14G,IO写84K
不清缓存,再执行一次。消耗时间降到1.1秒,几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.33    0.00   95.50

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.44       421.37       204.97    9699177    4718032

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
 count 
-------
     1
(1 row)

real    0m1.106s
user    0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.33    0.00   95.50

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.43       421.25       204.91    9699209    4718032

2.3.2 cstore表

清除文件系统缓存,并重启PostgreSQL后
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94  -l logfile94 restart
[root@node2 ~]# echo 1 > /proc/sys/vm/drop_caches

对Int列执行=查询
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.32    0.00   95.52

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.23       419.81       204.22    9699681    4718484

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where id =666666"
 count 
-------
     1
(1 row)


real    0m0.108s
user    0m0.004s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.32    0.00   95.52

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             57.23       420.01       204.14    9708181    4718484

耗时0.11秒,产生IO读8.5M,IO写0K。执行速度极大提高,看来Int列的跳过索引起作用了。
不清缓存,再执行一次。消耗时间降到0.02秒,几乎不产生IO。
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.29    0.00   95.55

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             56.77       416.63       202.52    9708197    4719028

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where id =666666"
 count 
-------
     1
(1 row)

real    0m0.020s
user    0m0.002s
sys 0m0.001s
[postgres@node2 chenhj]$ iostat -k dm-2
Linux 2.6.32-71.el6.x86_64 (node2)  06/23/14    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.79    0.00    0.37    3.29    0.00   95.55

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dm-2             56.76       416.53       202.47    9708229    4719060

3 总结

针对1列int加10列Text构成的表,插入1千万条记录的性能测试结果如下:
项目 项目 普通表 cstore表 性能提升倍数
数据加  速度(s)  402 526 0.76
ANALYZE速度(s)  12 5.9 2.03
数据大小(Byte) 1161093120 389583021 2.98
Text列的like 时间(s)  7.1 2.8 2.54
IO(KByte) 1138156 58744 19.37
Int列的= 执行时间(s) 6.8 0.11 61.82
产生读IO(KByte) 1138264 8500 133.91


基于cstore_fdw的外部表确实可以大大降低IO需求提高数据分析的性能。但是,它毕竟是外部表,目前仅支持select和copy,功能上不能和普通表相比。

阅读(20646) | 评论(4) | 转发(1) |
给主人留下些什么吧!~~

skykiker2016-08-25 21:46:35

xunyaodong:你好  请问对cstore的文件结构有了解吗

你可以看下 Optimized Row Columnar  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC

回复 | 举报

xunyaodong2016-08-25 09:38:35

你好  请问对cstore的文件结构有了解吗

skykiker2015-11-17 11:49:29

nakata175947:作为OLAP cstore开源版本不能集群  得购买citusDB
这个不爽

如果都是单机MonetDB要比cstore快N倍
人家是完全基于内存的

集群可以用GreenPlum啊,也是基于PostgreSQL改的,已经开源了。

回复 | 举报

nakata1759472015-05-07 10:36:44

作为OLAP cstore开源版本不能集群  得购买citusDB
这个不爽

如果都是单机MonetDB要比cstore快N倍
人家是完全基于内存的