<small id="7ktuj"></small>
      <bdo id="7ktuj"></bdo>
        <mark id="7ktuj"></mark>

        <source id="7ktuj"></source>
        <small id="7ktuj"></small>

        ITPub博客

        首頁 > 數據庫 > PostgreSQL > PostgreSQL DBA(123) - 列式存儲zedstore再體驗

        PostgreSQL DBA(123) - 列式存儲zedstore再體驗

        原創 PostgreSQL 作者:husthxd 時間:2019-11-07 14:54:23 0 刪除 編輯

        本節簡單介紹了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/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
        創建用戶,初始化數據庫

        [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的性能差異
        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 5000000
        testdb=#
        

        執行查詢

        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 loo
        ps=3)
                       ->  Parallel Seq Scan on t_heap  (cost=0.00..52584.55 rows=2083355 width=17) (actual time=0.094..25
        6.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 5000000
        testdb=#
        

        執行查詢

        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 l
        oops=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%左右。

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

        來自 “ ITPUB博客 ” ,鏈接:http://www.ep4tq.com/6906/viewspace-2663051/,如需轉載,請注明出處,否則將追究法律責任。

        請登錄后發表評論 登錄
        全部評論
        ITPUB數據庫版塊資深版主,對Oracle、PostgreSQL有深入研究。

        注冊時間:2007-12-28

        • 博文量
          1429
        • 訪問量
          3867686
        妹子图每日分享