Shulou(Shulou.com)06/01 Report--

This section briefly introduces the installation and use of Greenplum open source column storage zedstore.

Installation

Download the source code from Github. Just like ordinary PG, you can compile and install it.

[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... [root@localhost postgres-zedstore] # make-J4... [root@localhost postgres-zedstore] # make install...PostgreSQL installation complete.

Heap vs ZedStore

Create a user and initialize the database

[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" includes 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

Let's compare the performance differences between head am and zedstore

PG

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

Execute query

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)

Execution time is 933ms

ZedStore

[zedstore@localhost testdb] $psql-d testdbpsql (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 TABLEtestdb=#\ 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: zedstoretestdb=# insert into t_olap select x | XJ as c2' | | x from generate_series (1pm 5000000) c2' | INSERT 0 5000000

Execute query

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)

The execution time is 1753ms, and the column storage doesn't seem to work? To be continued。

references

PostgreSQL access method api-based column memory zedstore

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.