greenplum 5.7 + create table + insert into

os: centos 7.4
gp: gpdb-5.7.0

三台機器
node1 為master host
node2、node3為segment host

psql 登入 node1 master

$ psql -d peiybdbpeiybdb=# select current_database(); current_database ------------------ peiybdb(1 row)create table tmp_t0(c1 varchar(100),c2 varchar(100),c3 varchar(100));NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEpeiybdb=# \d+ tmp_t0                        Table "public.tmp_t0" Column |          Type          | Modifiers | Storage  | Description --------+------------------------+-----------+----------+------------- c1     | character varying(100) |           | extended |  c2     | character varying(100) |           | extended |  c3     | character varying(100) |           | extended | Has OIDs: noDistributed by: (c1)

查看node1、node2、node3的檔案目錄大小

[gpadmin@node1 gpseg-1]$ pwd/u01/greenplum-data/gpseg-1[gpadmin@node1 gpseg-1]$ du -sh109M    .[gpadmin@node2 gpseg0]$ pwd/u01/greenplum-data/gpseg0[gpadmin@node2 gpseg0]$ du -sh109M    .[gpadmin@node3 gpseg1]$ pwd/u01/greenplum-data/gpseg1[gpadmin@node3 gpseg1]$ du -sh109M    .

插入資料

peiybdb=# select gp_opt_version();                gp_opt_version                 ----------------------------------------------- GPOPT version: 2.55.20, Xerces version: 3.1.2(1 row)set optimizer=on;set optimizer_enumerate_plans=on;set optimizer_minidump=always;set optimizer_enable_constant_expression_evaluation=off;set client_min_messages='debug5';insert into tmp_t0(c1,c2,c3)select generate_series(1, 1000000) as c1,       md5(random()::text) as c2 ,       md5(random()::text) as c3;

node1 master上查詢pg_stat_activity

peiybdb=# \xExpanded display is on.peiybdb=# select * from pg_stat_activity;-[ RECORD 1 ]----+-------------------------------------------datid            | 16384datname          | peiybdbprocpid          | 10904sess_id          | 20usesysid         | 10usename          | gpadmincurrent_query    | insert into tmp_t0                 | (c1,c2,c3)                 | select generate_series(1, 1000000) as c1,                  |        md5(random()::text) as c2 ,                  |        md5(random()::text) as c3                  | waiting          | fquery_start      | 2018-05-02 07:20:33.325637+00backend_start    | 2018-05-02 07:13:17.573451+00client_addr      | 192.168.56.1client_port      | 63217application_name | xact_start       | 2018-05-02 07:20:33.3212+00waiting_reason   | rsgid            | 0rsgname          | rsgqueueduration | -[ RECORD 2 ]----+-------------------------------------------datid            | 16384datname          | peiybdbprocpid          | 10906sess_id          | 21usesysid         | 10usename          | gpadmincurrent_query    | <IDLE>waiting          | fquery_start      | 2018-05-02 07:17:17.675179+00backend_start    | 2018-05-02 07:13:17.580506+00client_addr      | 192.168.56.1client_port      | 63218application_name | xact_start       | waiting_reason   | rsgid            | 0rsgname          | rsgqueueduration | -[ RECORD 3 ]----+-------------------------------------------datid            | 16384datname          | peiybdbprocpid          | 11095sess_id          | 22usesysid         | 10usename          | gpadmincurrent_query    | select * from pg_stat_activity;waiting          | fquery_start      | 2018-05-02 07:28:07.30111+00backend_start    | 2018-05-02 07:27:54.501407+00client_addr      | client_port      | -1application_name | psqlxact_start       | 2018-05-02 07:28:07.30111+00waiting_reason   | rsgid            | 0rsgname          | rsgqueueduration | 

再次查看node1、node2、node3的檔案目錄大小

[gpadmin@node1 gpseg-1]$ du -sh109M    .[gpadmin@node2 gpseg0]$ du -sh224M    .[gpadmin@node3 gpseg1]$ du -sh224M    .

可以觀察到 master 節點node1的資料檔案大小並沒有發生變化，segment的node2、node3的資料檔案增長了不少。
主要就是由於 master 節點是用來儲存定義，segment是用來儲存資料的。

查看tmp_t0的定義

peiybdb=# peiybdb=# \d              List of relations Schema |  Name  | Type  |  Owner  | Storage --------+--------+-------+---------+--------- public | tmp_t0 | table | gpadmin | heap(1 row)peiybdb=# \d+ tmp_t0;                        Table "public.tmp_t0" Column |          Type          | Modifiers | Storage  | Description --------+------------------------+-----------+----------+------------- c1     | character varying(100) |           | extended |  c2     | character varying(100) |           | extended |  c3     | character varying(100) |           | extended | Has OIDs: noDistributed by: (c1)

Distributed by 這個就是tmp_t0表的分布列,表的分布列一定要合理，能夠降資料比較均勻的分布到各個segment節點上。
檢索資料時能夠在多個節點並發處理資料。