Greenplum支持表空間，創建表空間時，需要指定filespace。 postgres=# \h create tablespace; Command: CREATE TABLESPACE Description: define a new tablespace Syntax: CREATE TABLESPACE tablespace_name [OWNER username] FILESPACE filespace_name那么什么是filespace呢？ GP在初始化完后，有一個默認的filespace ： pg_system。 表空間pg_default和pg_global都放在這個filespace下面。 也就是說一個filespace可以被多個表空間使用。 postgres=# select oid,* from pg_filespace;oid | fsname | fsowner ------+-----------+---------3052 | pg_system | 10 (1 row) postgres=# select * from pg_tablespace;spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid ------------+----------+-------------+--------+-----------------+-----------------+----------pg_default | 10 | | | | | 3052pg_global | 10 | | | | | 3052 (2 rows)還有TEMPORARY_FILES和TRANSACTION_FILES對應的filespace如下： $gpfilespace --showtempfilespace 20151218:16:02:07:063949 gpfilespace:127.0.0.1:digoal-[INFO]:- A tablespace requires a file system location to store its database files. A filespace is a collection of file system locations for all components in a Greenplum system (primary segment, mirror segment and master instances). Once a filespace is created, it can be used by one or more tablespaces.20151218:16:02:07:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-Getting filespace information for TEMPORARY_FILES 20151218:16:02:08:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-Checking for filespace consistency 20151218:16:02:08:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES 20151218:16:02:09:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-TEMPORARY_FILES OIDs are consistent for pg_system filespace 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-TEMPORARY_FILES entries are consistent for pg_system filespace 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-Current Filespace for TEMPORARY_FILES is pg_system 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-1 /disk1/digoal/gpdata/gpseg-1 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-25 /disk1/digoal/gpdata_mirror/gpseg0 20151218:16:02:11:063949 gpfilespace:127.0.0.1:digoal-[INFO]:-2 /disk1/digoal/gpdata/gpseg0 ......$gpfilespace --showtransfilespace 20151218:16:09:41:071104 gpfilespace:127.0.0.1:digoal-[INFO]:- A tablespace requires a file system location to store its database files. A filespace is a collection of file system locations for all components in a Greenplum system (primary segment, mirror segment and master instances). Once a filespace is created, it can be used by one or more tablespaces.20151218:16:09:41:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-Getting filespace information for TRANSACTION_FILES 20151218:16:09:41:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-Checking for filespace consistency 20151218:16:09:41:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES 20151218:16:09:42:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-TRANSACTION_FILES OIDs are consistent for pg_system filespace 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-TRANSACTION_FILES entries are consistent for pg_system filespace 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-Current Filespace for TRANSACTION_FILES is pg_system 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-1 /disk1/digoal/gpdata/gpseg-1 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-25 /disk1/digoal/gpdata_mirror/gpseg0 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-2 /disk1/digoal/gpdata/gpseg0 20151218:16:09:44:071104 gpfilespace:127.0.0.1:digoal-[INFO]:-26 /disk1/digoal/gpdata_mirror/gpseg1 ......如果我們的greenplum集群中，有SSD硬盤，又有SATA硬盤。怎樣更好的利用這些空間呢？ 方法1. 用flashcache或bcache，通過device mapper技術，將SSD和SATA綁定，做成塊設備。 再通過 邏輯卷管理 或者 軟RAID 或者 brtfs or zfs管理起來，做成大的文件系統。 （還有一種方法是用RHEL 7提供的LVM，可以將SSD作為二級緩存） 這種方法對GP來說，是混合動力，可以創建一個或多個文件系統（都具備混合動力）。 所以建議只需要一個pg_system filespace就夠了(除非容量到了文件系統管理的極限，那樣的話可以分成多個文件系統)。 用多個文件系統的情況下，就需要對每個文件系統，創建對應的目錄，以及filespace。方法2. SSD和SATA分開，各自創建各自的文件系統。 對每個文件系統，創建對應的目錄，以及filespace。創建filespace非常簡單，分幾步。 如下： 1. 創建目錄，需要在所有的角色對應的主機中創建。給予gp 操作系統管理用戶對應的權限。 master $ mkdir /ssd1/gpdata/master_p $ chown gpadmin:gpadmin /ssd1/gpdata/master_pmaster standby $ mkdir /ssd1/gpdata/master_s $ chown gpadmin:gpadmin /ssd1/gpdata/master_ssegment $ mkdir /ssd1/gpdata_p $ chown gpadmin:gpadmin /ssd1/gpdata_psegment mirror $ mkdir /ssd1/gpdata_s $ chown gpadmin:gpadmin /ssd1/gpdata_s2. 查看系統配置 postgres=# select dbid,content,role,preferred_role,hostname,port from gp_segment_configuration order by role,dbid;dbid | content | role | preferred_role | hostname | port ------+---------+------+----------------+-------------------+-------2 | 0 | m | p | digoal.sqa.zmf | 400003 | 1 | m | p | digoal.sqa.zmf | 400014 | 2 | m | p | digoal.sqa.zmf | 400025 | 3 | m | p | digoal.sqa.zmf | 400036 | 4 | m | p | digoal.sqa.zmf | 400047 | 5 | m | p | digoal.sqa.zmf | 400058 | 6 | m | p | digoal.sqa.zmf | 400069 | 7 | m | p | digoal.sqa.zmf | 4000710 | 8 | m | p | digoal.sqa.zmf | 4000811 | 9 | m | p | digoal.sqa.zmf | 4000912 | 10 | m | p | digoal.sqa.zmf | 4001013 | 11 | m | p | digoal.sqa.zmf | 4001114 | 12 | m | p | digoal.sqa.zmf | 4001215 | 13 | m | p | digoal.sqa.zmf | 4001316 | 14 | m | p | digoal.sqa.zmf | 4001417 | 15 | m | p | digoal.sqa.zmf | 4001518 | 16 | m | p | digoal.sqa.zmf | 4001619 | 17 | m | p | digoal.sqa.zmf | 4001720 | 18 | m | p | digoal.sqa.zmf | 4001821 | 19 | m | p | digoal.sqa.zmf | 4001922 | 20 | m | p | digoal.sqa.zmf | 4002023 | 21 | m | p | digoal.sqa.zmf | 4002124 | 22 | m | p | digoal.sqa.zmf | 500111 | -1 | p | p | digoal.sqa.zmf | 192125 | 0 | p | m | digoal.sqa.zmf | 4100026 | 1 | p | m | digoal.sqa.zmf | 4100127 | 2 | p | m | digoal.sqa.zmf | 4100228 | 3 | p | m | digoal.sqa.zmf | 4100329 | 4 | p | m | digoal.sqa.zmf | 4100430 | 5 | p | m | digoal.sqa.zmf | 4100531 | 6 | p | m | digoal.sqa.zmf | 4100632 | 7 | p | m | digoal.sqa.zmf | 4100733 | 8 | p | m | digoal.sqa.zmf | 4100834 | 9 | p | m | digoal.sqa.zmf | 4100935 | 10 | p | m | digoal.sqa.zmf | 4101036 | 11 | p | m | digoal.sqa.zmf | 4101137 | 12 | p | m | digoal.sqa.zmf | 4101238 | 13 | p | m | digoal.sqa.zmf | 4101339 | 14 | p | m | digoal.sqa.zmf | 4101440 | 15 | p | m | digoal.sqa.zmf | 4101541 | 16 | p | m | digoal.sqa.zmf | 4101642 | 17 | p | m | digoal.sqa.zmf | 4101743 | 18 | p | m | digoal.sqa.zmf | 4101844 | 19 | p | m | digoal.sqa.zmf | 4101945 | 20 | p | m | digoal.sqa.zmf | 4102046 | 21 | p | m | digoal.sqa.zmf | 4102147 | 22 | p | m | digoal.sqa.zmf | 41022 (47 rows)3. 創建配置文件，格式如下，假如我要創建一個名為ssd1的filespace。 字段包含(hostname, dbid, DIR/$prefix + $content) $ vi conf filespace:ssd1 digoal.sqa.zmf:1:/ssd1/gpdata/master_p/gp-1 digoal.sqa.zmf:2:/ssd1/gpdata_p/gp0 digoal.sqa.zmf:3:/ssd1/gpdata_p/gp1 ...... digoal.sqa.zmf:25:/ssd1/gpdata_s/gp0 digoal.sqa.zmf:26:/ssd1/gpdata_s/gp1 ......還有一種方法是使用gpfilespace -o conf來生成配置文件。(在提示時輸入目錄名DIR的部分即可)4. 創建filespace gpfilespace -c conf -h 127.0.0.1 -p 1921 -U digoal -W digoal20151218:17:16:39:108364 gpfilespace:127.0.0.1:digoal-[INFO]:-Connecting to database 20151218:17:16:39:108364 gpfilespace:127.0.0.1:digoal-[INFO]:-Filespace "ssd1" successfully created ...... 然后gpfilespace會自動在數據庫執行以下DDL SQL。創建對應的filespace。 所以我們也可以自己在數據庫中執行SQL來創建filespace。CREATE FILESPACE ssd1 (1: '/disk1/digoal/new_p/gp-1',2: '/disk1/digoal/new_p/gp0',3: '/disk1/digoal/new_p/gp1',4: '/disk1/digoal/new_p/gp2',5: '/disk1/digoal/new_p/gp3',6: '/disk1/digoal/new_p/gp4',7: '/disk1/digoal/new_p/gp5',8: '/disk1/digoal/new_p/gp6',9: '/disk1/digoal/new_p/gp7',10: '/disk1/digoal/new_p/gp8',11: '/disk1/digoal/new_p/gp9',12: '/disk1/digoal/new_p/gp10',13: '/disk1/digoal/new_p/gp11',14: '/disk1/digoal/new_p/gp12',15: '/disk1/digoal/new_p/gp13',16: '/disk1/digoal/new_p/gp14',17: '/disk1/digoal/new_p/gp15',18: '/disk1/digoal/new_p/gp16',19: '/disk1/digoal/new_p/gp17',20: '/disk1/digoal/new_p/gp18',21: '/disk1/digoal/new_p/gp19',22: '/disk1/digoal/new_p/gp20',23: '/disk1/digoal/new_p/gp21',24: '/disk1/digoal/new_p/gp22',25: '/disk1/digoal/new_s/gp0',26: '/disk1/digoal/new_s/gp1',27: '/disk1/digoal/new_s/gp2',28: '/disk1/digoal/new_s/gp3',29: '/disk1/digoal/new_s/gp4',30: '/disk1/digoal/new_s/gp5',31: '/disk1/digoal/new_s/gp6',32: '/disk1/digoal/new_s/gp7',33: '/disk1/digoal/new_s/gp8',34: '/disk1/digoal/new_s/gp9',35: '/disk1/digoal/new_s/gp10',36: '/disk1/digoal/new_s/gp11',37: '/disk1/digoal/new_s/gp12',38: '/disk1/digoal/new_s/gp13',39: '/disk1/digoal/new_s/gp14',40: '/disk1/digoal/new_s/gp15',41: '/disk1/digoal/new_s/gp16',42: '/disk1/digoal/new_s/gp17',43: '/disk1/digoal/new_s/gp18',44: '/disk1/digoal/new_s/gp19',45: '/disk1/digoal/new_s/gp20',46: '/disk1/digoal/new_s/gp21',47: '/disk1/digoal/new_s/gp22' );現在你可以使用這個filespace了. 例如 1. 將temp , trans移動到這個新的filespace. $gpfilespace --movetempfilespace ssd120151218:17:17:29:008363 gpfilespace:127.0.0.1:digoal-[INFO]:- A tablespace requires a file system location to store its database files. A filespace is a collection of file system locations for all components in a Greenplum system (primary segment, mirror segment and master instances). Once a filespace is created, it can be used by one or more tablespaces.20151218:17:17:29:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Database was started in NORMAL mode 20151218:17:17:29:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Stopping Greenplum Database 20151218:17:17:57:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Starting Greenplum Database in master only mode 20151218:17:18:02:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Checking if filespace ssd1 exists 20151218:17:18:02:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Checking if filespace is same as current filespace 20151218:17:18:02:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Stopping Greenplum Database in master only mode 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Checking for connectivity 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace information 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Obtaining segment information ... 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Creating RemoteOperations list 20151218:17:18:04:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Moving TEMPORARY_FILES filespace from pg_system to ssd1 ... 20151218:17:18:06:008363 gpfilespace:127.0.0.1:digoal-[INFO]:-Starting Greenplum Database$gpfilespace --movetransfilespace ssd1 ... 20151218:17:19:17:055389 gpfilespace:127.0.0.1:digoal-[INFO]:-Moving TRANSACTION_FILES filespace from pg_system to ssd1 ... 20151218:17:21:16:055389 gpfilespace:127.0.0.1:digoal-[INFO]:-Starting Greenplum Database2. 創建表空間，使用這個filespace. postgres=# create tablespace tbs_ssd1 filespace ssd1; CREATE TABLESPACE postgres=# create table tt(id int) tablespace tbs_ssd1; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 TABLE postgres=# select * from pg_tablespace ;spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid ------------+----------+-------------+--------+-----------------+-----------------+----------pg_default | 10 | | | | | 3052pg_global | 10 | | | | | 3052tbs_ssd1 | 10 | | | | | 69681 (3 rows)postgres=# select * from pg_filespace;fsname | fsowner -----------+---------pg_system | 10ssd1 | 10 (2 rows)greenplum為什么會引入filespace的概念？ 因為主機目錄結構可能不一樣，所以原有的目錄結構式的方法來創建表空間，可能不夠靈活。最后，如何查看每個節點的filespace和location的關系？ digoal=# select a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation from gp_segment_configuration a,pg_filespace b,pg_filespace_entry c where a.dbid=c.fsedbid and b.oid=c.fsefsoid order by content;dbid | content | role | port | hostname | fsname | fselocation ------+---------+------+-------+-------------------+-----------+--------------------------------------1 | -1 | p | 1921 | digoal193096.zmf | pg_system | /data01/gpdata/master_pgdata/gpseg-12 | 0 | p | 40000 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg03 | 1 | p | 40001 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg14 | 2 | p | 40002 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg25 | 3 | p | 40000 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg36 | 4 | p | 40001 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg47 | 5 | p | 40002 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg58 | 6 | p | 40000 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg69 | 7 | p | 40001 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg710 | 8 | p | 40002 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg811 | 9 | p | 40000 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg912 | 10 | p | 40001 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg1013 | 11 | p | 40002 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg1114 | 12 | p | 40000 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg1215 | 13 | p | 40001 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg1316 | 14 | p | 40002 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg1417 | 15 | p | 40003 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg1518 | 16 | p | 40003 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg1619 | 17 | p | 40003 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg1720 | 18 | p | 40003 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg1821 | 19 | p | 40003 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg1922 | 20 | p | 40000 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg2223 | 21 | p | 40001 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg2324 | 22 | p | 40002 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg2425 | 23 | p | 40003 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg25 (25 rows)[參考] gpfilespace -h

總結

以上是生活随笔為你收集整理的Greenplum 表空间和filespace的用法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。