怎么安装和使用Greenplum开源的列式存储zedstore

本篇内容介绍了"怎么安装和使用Greenplum开源的列式存储zedstore"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

安装
从Github上下载源码,与普通PG一样,编译安装即可

[root@localhost postgres-zedstore]# ./configure --enable-debug --with-python --with-perl --with-tcl --with-gssapi --with-pam --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-dtrace --enable-depend --enable-cassert --with-systemd CFLAGS="-O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2" --prefix=/appdb/zedstorechecking build system type... x86_64-pc-linux-gnu...[root@localhost postgres-zedstore]# make -j4...[root@localhost postgres-zedstore]# make install...PostgreSQL installation complete.

Heap vs ZedStore
创建用户,初始化数据库

[zedstore@localhost ~]$ initdb -E utf8 -D /data/zedstore/testdbThe files belonging to this database system will be owned by user "zedstore".This user must also own the server process.The database cluster will be initialized with locale "en_US.UTF-8".The default text search configuration will be set to "english".Data page checksums are disabled.creating directory /data/zedstore/testdb ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default timezone ... PRCcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option -A, or--auth-local and --auth-host, the next time you run initdb.Success. You can now start the database server using:    pg_ctl -D /data/zedstore/testdb -l logfile start

下面来对比一下head am和zedstore的性能差异
Heap

testdb=# create table t_heap(id int,c1 int,c2 varchar(20));CREATE TABLE                           testdb=# insert into t_heap select x,x,'c2'||x from generate_series(1,5000000) as x;INSERT 0 5000000testdb=#

执行查询

testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_heap;                                                                  QUERY PLAN                                      ---------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate  (cost=69209.94..69209.95 rows=1 width=72) (actual time=964.313..964.314 rows=1 loops=1)   ->  Gather  (cost=69209.71..69209.92 rows=2 width=72) (actual time=963.978..966.938 rows=3 loops=1)         Workers Planned: 2         Workers Launched: 2         ->  Partial Aggregate  (cost=68209.71..68209.72 rows=1 width=72) (actual time=929.220..929.220 rows=1 loops=3)               ->  Parallel Seq Scan on t_heap  (cost=0.00..52584.55 rows=2083355 width=17) (actual time=0.094..256.014 rows=1666667 loops=3) Planning Time: 17.157 ms Execution Time: 968.461 ms(8 rows)testdb=#

执行时间为968ms

空间占用

testdb=# select pg_size_pretty(pg_table_size('t_heap')); pg_size_pretty ---------------- 248 MB(1 row)

ZedStore

testdb=# create table t_zedstore(id int,c1 int,c2 varchar(20)) using zedstore;CREATE TABLE                           testdb=# insert into t_zedstore select x,x,'c2'||x from generate_series(1,5000000) as x;INSERT 0 5000000testdb=#

执行查询

testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_zedstore;                                                                   QUERY PLAN                                     ------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate  (cost=24917.50..24917.51 rows=1 width=72) (actual time=1341.238..1341.239 rows=1 loops=1)   ->  Gather  (cost=24917.27..24917.48 rows=2 width=72) (actual time=1341.046..1343.387 rows=3 loops=1)         Workers Planned: 2         Workers Launched: 2         ->  Partial Aggregate  (cost=23917.27..23917.28 rows=1 width=72) (actual time=1328.432..1328.432 rows=1 loops=3)               ->  Parallel Seq Scan on t_zedstore  (cost=0.00..18968.87 rows=659787 width=17) (actual time=0.702..837.075 rows=1666667 loops=3) Planning Time: 0.643 ms Execution Time: 1343.612 ms(8 rows)testdb=#

使用zedstore的时间是1343ms，比起heap方式要慢接近40%。

空间占用

testdb=# select pg_size_pretty(pg_table_size('t_zedstore')); pg_size_pretty ---------------- 97 MB(1 row)testdb=#

不过使用zedstore，空间占用倒是只有原来的40%左右。

"怎么安装和使用Greenplum开源的列式存储zedstore"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！