Greenplum 表空间和filespace的用法

最新推荐文章于 2020-08-29 10:46:41 发布
最新推荐文章于 2020-08-29 10:46:41 发布
阅读量862 收藏 2
点赞数
文章标签： 数据库 操作系统
原文链接：https://yq.aliyun.com/articles/190
版权 
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 |             |        |                 |                 |     3052
 pg_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_p

master standby
$ mkdir /ssd1/gpdata/master_s
$ chown gpadmin:gpadmin /ssd1/gpdata/master_s

segment
$ mkdir /ssd1/gpdata_p
$ chown gpadmin:gpadmin /ssd1/gpdata_p

segment mirror
$ mkdir /ssd1/gpdata_s
$ chown gpadmin:gpadmin /ssd1/gpdata_s

2. 查看系统配置
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 | 40000
    3 |       1 | m    | p              | digoal.sqa.zmf | 40001
    4 |       2 | m    | p              | digoal.sqa.zmf | 40002
    5 |       3 | m    | p              | digoal.sqa.zmf | 40003
    6 |       4 | m    | p              | digoal.sqa.zmf | 40004
    7 |       5 | m    | p              | digoal.sqa.zmf | 40005
    8 |       6 | m    | p              | digoal.sqa.zmf | 40006
    9 |       7 | m    | p              | digoal.sqa.zmf | 40007
   10 |       8 | m    | p              | digoal.sqa.zmf | 40008
   11 |       9 | m    | p              | digoal.sqa.zmf | 40009
   12 |      10 | m    | p              | digoal.sqa.zmf | 40010
   13 |      11 | m    | p              | digoal.sqa.zmf | 40011
   14 |      12 | m    | p              | digoal.sqa.zmf | 40012
   15 |      13 | m    | p              | digoal.sqa.zmf | 40013
   16 |      14 | m    | p              | digoal.sqa.zmf | 40014
   17 |      15 | m    | p              | digoal.sqa.zmf | 40015
   18 |      16 | m    | p              | digoal.sqa.zmf | 40016
   19 |      17 | m    | p              | digoal.sqa.zmf | 40017
   20 |      18 | m    | p              | digoal.sqa.zmf | 40018
   21 |      19 | m    | p              | digoal.sqa.zmf | 40019
   22 |      20 | m    | p              | digoal.sqa.zmf | 40020
   23 |      21 | m    | p              | digoal.sqa.zmf | 40021
   24 |      22 | m    | p              | digoal.sqa.zmf | 50011
    1 |      -1 | p    | p              | digoal.sqa.zmf |  1921
   25 |       0 | p    | m              | digoal.sqa.zmf | 41000
   26 |       1 | p    | m              | digoal.sqa.zmf | 41001
   27 |       2 | p    | m              | digoal.sqa.zmf | 41002
   28 |       3 | p    | m              | digoal.sqa.zmf | 41003
   29 |       4 | p    | m              | digoal.sqa.zmf | 41004
   30 |       5 | p    | m              | digoal.sqa.zmf | 41005
   31 |       6 | p    | m              | digoal.sqa.zmf | 41006
   32 |       7 | p    | m              | digoal.sqa.zmf | 41007
   33 |       8 | p    | m              | digoal.sqa.zmf | 41008
   34 |       9 | p    | m              | digoal.sqa.zmf | 41009
   35 |      10 | p    | m              | digoal.sqa.zmf | 41010
   36 |      11 | p    | m              | digoal.sqa.zmf | 41011
   37 |      12 | p    | m              | digoal.sqa.zmf | 41012
   38 |      13 | p    | m              | digoal.sqa.zmf | 41013
   39 |      14 | p    | m              | digoal.sqa.zmf | 41014
   40 |      15 | p    | m              | digoal.sqa.zmf | 41015
   41 |      16 | p    | m              | digoal.sqa.zmf | 41016
   42 |      17 | p    | m              | digoal.sqa.zmf | 41017
   43 |      18 | p    | m              | digoal.sqa.zmf | 41018
   44 |      19 | p    | m              | digoal.sqa.zmf | 41019
   45 |      20 | p    | m              | digoal.sqa.zmf | 41020
   46 |      21 | p    | m              | digoal.sqa.zmf | 41021
   47 |      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 digoal

20151218: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 ssd1

20151218: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 Database

2. 创建表空间，使用这个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 |             |        |                 |                 |     3052
 pg_global  |       10 |             |        |                 |                 |     3052
 tbs_ssd1   |       10 |             |        |                 |                 |    69681
(3 rows)

postgres=# select * from pg_filespace;
  fsname   | fsowner 
-----------+---------
 pg_system |      10
 ssd1      |      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-1
    2 |       0 | p    | 40000 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg0
    3 |       1 | p    | 40001 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg1
    4 |       2 | p    | 40002 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg2
    5 |       3 | p    | 40000 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg3
    6 |       4 | p    | 40001 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg4
    7 |       5 | p    | 40002 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg5
    8 |       6 | p    | 40000 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg6
    9 |       7 | p    | 40001 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg7
   10 |       8 | p    | 40002 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg8
   11 |       9 | p    | 40000 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg9
   12 |      10 | p    | 40001 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg10
   13 |      11 | p    | 40002 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg11
   14 |      12 | p    | 40000 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg12
   15 |      13 | p    | 40001 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg13
   16 |      14 | p    | 40002 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg14
   17 |      15 | p    | 40003 | digoal193096.zmf | pg_system | /data01/gpdata/gpseg15
   18 |      16 | p    | 40003 | digoal199092.zmf | pg_system | /data01/gpdata/gpseg16
   19 |      17 | p    | 40003 | digoal200164.zmf | pg_system | /data01/gpdata/gpseg17
   20 |      18 | p    | 40003 | digoal204016.zmf | pg_system | /data01/gpdata/gpseg18
   21 |      19 | p    | 40003 | digoal204063.zmf | pg_system | /data01/gpdata/gpseg19
   22 |      20 | p    | 40000 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg22
   23 |      21 | p    | 40001 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg23
   24 |      22 | p    | 40002 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg24
   25 |      23 | p    | 40003 | digoal209198.zmf | pg_system | /data01/gpdata/gpseg25
(25 rows)

[参考]
gpfilespace -h
weixin_33889665
关注
Greenplum内存问题处理
JustinMars的博客
09-27 1632
错误 java.sql.SQLException: [Pivotal][Greenplum JDBC Driver][Greenplum]Out of memory (seg1 slice3 *.*.3.245:6001 pid=27558). 原因 内存不足错误消息表明Greenplum的Segment、主机和进程遇到了内存不足错误。 提示：Greenplum数据库集群中，内存管理对性能有显著的影响。默认设置适合于大部分环境。不要更改默认设置，除非理解系统上的内存特点和使用。 原因 解决方案
Greenplum 6.0 版本介绍
Greenplum中文社区
04-09 845
...
Greenplum中定义数据库对象之创建与管理表空间
行者牛仔
03-29 3301
GP中表空间以及文件空间的创建、管理与删除
Greenplum创建表空间
Focus on PostgreSQL
01-20 2175
Greenplum中创建表空间的方式和pg中有较大不同，因为gp中有表空间(tablespace)和文件空间(filespace)两个概念。 我们可以这样去理解，因为在gp中master和segment节点中数据文件肯定得存放在不同的位置，所以我们得想办法将所有的文件系统位置的文件全部都表示起来，因此就引入了文件空间这样一个概念——Greenplum数据库中所有部件所需的文件系统位置集合被称作一个...
Greenplum tablespace与filespace的关系
魂醉的一亩二分地
03-17 1863
GP里面和PG不同的是，多了个filespace概念，在GP里面tablespace必须建立在filespace上面，所以建立表空间之前，先要有指定的filespace filespace可以认为是多个文件目录的集合，可以对应一个或者多个tablespace 下面介绍一下创建filespace的实例： 1. su - gpadmin 2. 创建新的目录赋予权限 1
Greenplum管理表空间Tablespace
Gtlions Tracker
04-19 3565
create tablespace gtlions_ts1 [owner gtlions] filespace gtlionsfilespace; alter tablespace gtlions_ts1 rename to gtlions_ts2; alter tablespace gtlions_ts1 owner to gtlions; 表空间是基于文件空间filespace的，实
Greenplum表空间管理
a6418720的博客
03-14 497
在GP系统中有表空间(tablespace)和文件空间(filespace)的概念。 有不同，在postgresq9.6中可以直接在创建表空间时指定路径。 在系统初始化时，只有两个表空间pg_default和pg_gloal。这两个表空间都在文件空间pg_system下。 select a.spcname,b.fsname from pg_tablespace a,pg...
Greenplum 字典表 元数据
wei15633624611的博客
08-29 787
1、Greenplum 基本查询信息 1.1、Greenplum 常用查询 – pg_constraint 对存储对表的检查,主键,唯一和外键约束。 select * from pg_catalog.pg_constraint; – pg_compression 描述了可用的压缩方法 select * from pg_catalog.pg_compression; – pg_class 目录表和大多数具有列或其他类似于表的所有其他表（也称为关系）。 select * from pg_catalog.pg
GreenPlum常用数据库命令
11-28
- `SELECT * FROM pg_filespace_entry;` - 用于查看文件系统的使用情况。 - **字段说明**： - `Content`: 用于标识实例对，即主实例(primary instance)和镜像实例(mirror instance)。 - `Isprimary`: 标识该实例...
Greenplum系统参数查看操作
芷芸的博客
05-20 3556
Greenplum问题分析的基本操作 Greenplum基于PostgreSQL开发的关系型数据仓库，跟PostgreSQL的兼容性非常好，因此本文很多语法与PG通用。 1.查看实例配置和状态 select * from gp_configuration order by 1 ; select * from gp_configuration_history order by 1 ; 主要字段说明： Content：该字段相等的两个实例，是一对Ｐ（primary instance）和Ｍ（mirror
Greenplum释放AO表SHELL脚本
10-17
Greenplum支持行储存(HEAP储存)与列(append-only)储存,对于AO存储，虽然是appendonly，但实际上GP是支持DELETE和UPDATE的，被删除或更新的行，通过BITMAP来标记删除与修改。AO存储是块级组织，当一个块内的数据大部分都被删除或更新掉时，扫描它浪费的成本实际上是很高的。而PostgreSQL是通过HOT技术以及autovacuum来避免或减少垃圾的。但是Greenplum没有自动回收的worker进程，所以需要人为的触发。接下来就分析AO表与HEAP表的问题以及如何解答，执行空间的释放有3中方法分别是: 1、执行VACUUM。（当膨胀率大于gp_appendonly_compaction_threshold参数时），为共享锁。 2、执行VACUUM FULL。（不管gp_appendonly_compaction_threshold参数的设置，都会回收垃圾空间。），为DDL锁，需要慎用这个命令，会把CPU与IO沾满。 3、执行重分布。（不管gp_appendonly_compaction_threshold参数，都会回收垃圾空间。），为DDL锁
GreenPlum 实用手册（日常维护）
04-18 1074
GreenPlum 实用手册（日常维护）   1.0   数据库的连接设置 1.0 数据库安装成功后，首先编辑连接配置: 打开$MASTER_DATA_DIRECTORY/pg_hba.conf进行编辑，拿当前安装的数据库举例： vi /opt/greenplum/master/gpseg-1/pg_hba.conf 添加： #host  database      role   
greenplum的操作异常收集
jsjw18的专栏
09-09 4754
ERROR: could not write 3124 bytes to temporary file: No space left on device (buffile.c:405)
greenplum（六） 现场常见由业务导致的故障
xiaohai798的专栏
06-30 2646
1 应用侧发现执行调度时出现连接重置，需重新连接数据库的问题问题现象:问题代码:select c.bcyc_id,c.acyc_id,c.eparchy_code, c.city_code, 'E0_001_01', count(distinct c.user_id) , coalesce( (select coun...
greenplum分区表查看所占空间大小[转]
weixin_34060741的博客
05-22 359
为什么80%的码农都做不了架构师？>>> ...
greenplum vacuum表的作用
a290450134的博客
11-12 2022
vacuum：该选项主要是清理数据库表中的垃圾空间，该动作会消耗系统一定的资源，引起系统的IO上升，对有一定系统瓶颈来说容易造成堵塞，严重会把GP宕掉，造成数据库瞬断。一般不建议vacuum库中全表，通常做法是 vacuum 指定的表。 GPDB 与PostgreSQL一样，在执行delete语句时，并没有将数据删除，而是标志为删。 执行update，只是将旧数据标记为删除，重新再插入一...
国外云盘（如Rapidgator）免费下载姿势详解
热门推荐
蓬蒿人
03-03 11万+
在国内，文件的分享基本上市通过百度云盘来的，但很多小伙伴有时候会发现需要下载的文件是放在一些国外云盘上的，这些文件该怎么下载捏？ 其实国外的云盘种类非常多，不像国内的一家独大，常用的国外云盘比如Rapidgator，alfafile，uploadgig，filespace 等等。但这些云盘都是有些类似的特点就是，如果不付费下载的需要验证码或者下载的超级慢！！！尤其在国内的网络环境下。不仅如此，...
greenplum视图和表
最新发布
04-28
Greenplum是一个基于PostgreSQL的分布式数据库管理系统，它支持创建和管理表和视图。 表是一种存储数据的结构，可以在其中定义列和行。在Greenplum中，表可以被分成多个分片，每个分片可以存储在不同的节点上，从而...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值