PostgreSQLDBA(60)-列式存儲(chǔ)zedstore

本節(jié)簡單介紹了Greenplum開源的列式存儲(chǔ)zedstore的安裝和使用.

目前創(chuàng)新互聯(lián)已為上千多家的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管、服務(wù)器租用、企業(yè)網(wǎng)站設(shè)計(jì)、上虞網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

安裝
從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/zedstore
checking 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
創(chuàng)建用戶,初始化數(shù)據(jù)庫

[zedstore@localhost ~]$ initdb -E utf8 -D /data/zedstore/testdb
The 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 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You 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的性能差異
PG

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

執(zhí)行查詢

testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_olap;
                                                                  QUERY PLAN                                                 
-------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=69209.40..69209.40 rows=1 width=72) (actual time=925.540..925.540 rows=1 loops=1)
   ->  Gather  (cost=69209.17..69209.38 rows=2 width=72) (actual time=925.284..932.688 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=68209.17..68209.18 rows=1 width=72) (actual time=911.539..911.539 rows=1 loops=3)
               ->  Parallel Seq Scan on t_olap  (cost=0.00..52584.24 rows=2083324 width=17) (actual time=0.037..240.287 rows=
1666667 loops=3)
 Planning Time: 22.703 ms
 Execution Time: 933.020 ms
(8 rows)

執(zhí)行時(shí)間為933ms

ZedStore

[zedstore@localhost testdb]$ psql -d testdb
psql (13devel)
Type "help" for help.
testdb=# \dA+
                                List of access methods
   Name   | Type  |         Handler          |              Description               
----------+-------+--------------------------+----------------------------------------
 brin     | Index | brinhandler              | block range index (BRIN) access method
 btree    | Index | bthandler                | b-tree index access method
 gin      | Index | ginhandler               | GIN index access method
 gist     | Index | gisthandler              | GiST index access method
 hash     | Index | hashhandler              | hash index access method
 heap     | Table | heap_tableam_handler     | heap table access method
 spgist   | Index | spghandler               | SP-GiST index access method
 zedstore | Table | zedstore_tableam_handler | zedstore table access method
(8 rows)
testdb=# create table t_olap(id int,c1 int,c2 varchar(20)) using zedstore;
CREATE TABLE
testdb=# \d+ t_olap
                                          Table "public.t_olap"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 c1     | integer               |           |          |         | plain    |              | 
 c2     | character varying(20) |           |          |         | extended |              | 
Access method: zedstore
testdb=# insert into t_olap select x,x,'c2'||x from generate_series(1,5000000) as x;
INSERT 0 5000000

執(zhí)行查詢

testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_olap;
                                                                  QUERY PLAN                                                 
-------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=31425.10..31425.11 rows=1 width=72) (actual time=1707.755..1707.756 rows=1 loops=1)
   ->  Gather  (cost=31424.87..31425.08 rows=2 width=72) (actual time=1659.121..1710.512 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=30424.87..30424.88 rows=1 width=72) (actual time=1647.216..1647.217 rows=1 loops=3)
               ->  Parallel Seq Scan on t_olap  (cost=0.00..24130.07 rows=839307 width=17) (actual time=0.418..1124.465 rows=
1666667 loops=3)
 Planning Time: 1.907 ms
 Execution Time: 1753.191 ms
(8 rows)

執(zhí)行時(shí)間為1753ms,列式存儲(chǔ)似乎沒有發(fā)揮作用?待續(xù).

參考資料
PostgreSQL 基于access method api的列存zedstore