第九章数据库管理

[TOC]

9.1 用户及权限管理

9.1.1 Greenplum 数据库逻辑结构

图9-1

在 gp/pgsql 中，角色（Role）、模式（Schema）、数据库（DataBase）是三个不同的概念，不同于 Mysql 的 DataBase 等同于 Schema，Oracle 的 Role 等同于 Schema。

在 gp 中：

一个 database 下可以有多个 schema。schema在 gp 中也叫做 namespace。
Language 在使用前必须创建，一个语言只属于一个 database
table、view、sequence、function 必须只属于一个 schema
一个 filespace 可以有多个 tablespace，一个 tablespace 只属于一个 filespace，fielspace 与 role 没有关系
tablespace 与 table 是一对多的关系，一个 schema 下的表可以分布在多个 tablespace 下
在图9-1 中，除了 filespace 之外，其他的权限管理都是通过 role 来实现，在这些层次结构中，用户必须对上一层有访问权限，才能够访问该层的内容
group 与 role 是一样的概念，group的语法还能用，但实际上已被废弃了

9.1.2 Grant 语法（赋权）

创建数据库语法为：

CREATE ROLE name [[WITH] option [ ... ]]
where option can be:
    SUPERUSER | NOSUPERUSER 
    | CREATEDB | NOCREATEDB 
    | CREATEROLE | NOCREATEROLE 
    | CREATEEXTTABLE | NOCREATEEXTTABLE
    [ ( attribute='value'[, ...]) ] 
    where attribute and values are:
    type='readable'|'writable'
    protocol='gpfdist'|'http'|'gphdfs'
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE rolename [, ...]
    | ROLE rolename [,...]
    | ADMIN rolename [,...]
    | RESOURCE QUEUE queue_name

从语法上看，参数配置主要有：

超级用户（SUPERUSER）：最高用户权限，不受资源队列控制，拥有所有的权限，可以对数据库进行任何操作，一般只有 DBA 可以拥有这个权限
创建数据库权限（CREATEDB）
创建用户权限（CREATEUSER）
登录权限（LOGING）：可以指定该用户登录的连接数控制
创建外部表权限（CREATEEXTTABLE）：属性配置中也可以对外部表有更细的权限控制，如只读、可写外部表权限等
用户继承（INHERIT）：子用户可以拥有父用户的所有权限
资源队列控制（RESOURCE QUEUE）
密码控制（ENCRYPTED）：还可以指定密码以及失效时间

赋权命令 Grant：

GRANT 权限类型 ON Relation(如表、视图、函数、schema等) TO 用户或用户组

9.2 登录权限控制

客户端认证是由一个配置文件（通常名为 pg_hba.conf）控制的，它存放在数据库集群的数据目录中。HBA 是 “Host-Based Authentication”的缩写，即基于主机的认证，可以限制登录机器的 IP 段。

9.3 资源队列即并发控制

资源负载管理是为了限制系统中活动的 sql 对使用资源的消耗，避免由于 sql 将系统资源（如 CPU、I/O、内存）耗尽而造成系统缓慢或崩溃。资源队列可以限制活动 sql 的个数，以及 sql 各种消耗的大小。每一个用户会对应到一个资源队列中。通过对用户消耗资源的控制，dba 可以尽量避免系统出现过负载。

资源队列在 gp 中是如何工作的？

资源调度在系统安装的时候已经默认打开了，所有的数据库用户都必须对应了一个资源对象，如果配置具体的资源队列，默认的资源队列是 pg_default。

在 gp 中，资源队列可以实现如下的限制：

活动的 sql 数，在这个资源队列下最多能够运行的 sql 数
能够消耗的最大内存
sql 优先级，与其他队列的比较，主要限制在 cpu 的资源上
sql 的cost 值

图9-3

（1）内存

如果一个资源队列中限制了最大使用内存是 2000 MB，同时设置了同时执行的sql 数为 10 个，那么每一个 sql 最多使用的内存是 200 MB，同时每个 sql 消耗的内存，不能大于 statement_mem 参数中设置的内存大小。当一个 sql 运行时，这个内存大小就会被分配出来，直到 sql 执行结束后才释放

（2）CPU

cpu 优先级管理，每一个资源队列中，都有一个对应的 cpu 优先级。cpu 的优先级有三个登记：

abhoc，低优先级
reportin，高优先级
executive，最高优先级

当系统中有新的 sql 进入的时候，各个 sql 消耗 cpu 的资源会根据其优先级重新评估，如图9-4：

图9-4

当 executive 优先级的 sql 进入时，系统会将大部分的资源分配给它，如图9-5：

图9-5

（3）语法介绍

并不是所有的 sql 都会被限制在资源队列中，在默认情况下，select、select into、create table as select 和 declare cursor 会被限制在队列中。如果将参数 resource_select_only 设置为 off，那么 insert、update、delete 语句也会被限制在队列中。

下面介绍如何创建资源队列，以及如何使用资源队列，语法如下：

CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ...])
where queue_attribute is:
    ACTIVE_STATEMENTS=integer
        [ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}]]
        [ MIN_COST=float]
        [ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}]
        [ MEMORY='memory_units']| MAX_COST=float [COST_OVERCOMMIT={TRUE|FLASE}]
        [ ACTIVE_STATEMENTS=integer]
        [ MIN_COST=float]
        [ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}]
        [ MEMORY_LIMIT='memory_units']

（1）创建一个队列只有限制最大的活动sql数：

CREATE RESOURCE QUEUE abhoc WITH (ACTIVE_STATEMENTS=3);

（2）创建一个队列加上内存限制：

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20, MEMORY_LIMIT='200MB');

如果想对一个sql进行特殊处理，增加其运行时的内存，那么可以设置 statement_mem 参数，将它调大：

SET statement_mem='2GB';
select * from my_big_table where column='value' order by id;
RESER statement_mem;

（3）设置最大的cost值：

CREATE RESOURCE QUEUE webuser WITH (MAX_COST=10000.0);

（4）设置 CPU 优先级：

CPU优先级有5个级别：MIN|LOW|MEDIUM|HIGH|MAX，可以根据不同的需求选择：

ALTER RESOURCE QUEUE ABHOC WITH (PRIORITY=HIGH);

查看配置情况：

SELECT * FROM pg_resqueue_attributes;

查看现有的资源队列使用情况：

SELECT * FROM pg_resqueue_status;

在 gp_toolkit 中，还有几个视图可用于查看资源队列的使用情况：

\dv gp_toolkit.gp_resq*

创建/修改用户指定资源队列：

CREATE ROLE aquery RESOURCE QUEUE abhoc;
ALTER ROLE etl RESOURCE QUEUE abhoc;

修改资源队列的语法如下，只有超级用户才可以修改资源组：

ALTER RESOURCE QUEUE name WITH (queue_attribute=value [, ...] )
where queue_attribute is:
    ACTIVE_STATEMENTS=integer
    MEMORY_LIMIT='memory_units'
    MAX_COST=float
    COST_OVERCOMMIT={TRUE|FALSE}
    MIN_COST=float
    PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}

9.4 Greenplum 锁机制

gp 的锁基本上与 postgresSQL 的锁是一样的，但 gp 的锁机制还不够完善，在某些场景上可能会出现一些问题。

表9-1

表9-1-2

这集中锁的冲突如表9-2所示：

表9-2

图9-7

locktype表示锁住的内容，主要是transactionid 和 relation。在gp中，master到segment的连接就是一个 transaction，只要一连接就会有这个锁信息。

relation对应pg_class的oid字段。

gpid不等于-1就是代表每一个segment的锁信息。

通过 lock 命令可以显式地将表锁住，语法如下：

Command: LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] name  [, ...] [ IN lockmode MODE] [ NOWAIT]
where lockmode is one of :
    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

9.5 数据目录结构

图9-8是gp主节点（master）的数据目录结构：

图9-8

base是数据目录，每个数据库在这个目录下，会有一个对应的文件夹
global是每一个数据库公用的数据目录
gpperfmon监控数据库性能时，存放监控数据的地方
pg_changetracking 是 segment 之间主备同步用到的一些原数据信息保存的地方
pg_clog 是记录数据库事务信息的地方，保存每一个事务id的状态，这个非常重要，不能丢失，一旦丢失，整个数据库就基本上不可用了
pg_log 是数据库的日志信息
pg_twophase 是二阶段提交的事务信息（二阶段提交参阅第7章）
pg_xlog 是数据库重写日志保存的地方，其中每个文件固定大小为64MB，并不断重复使用
gp_dbid 记录这个数据库的dbid 以及它对应的 mirror节点的dbid
pg_hba.conf 是访问权限控制文件
pg_ident.conf 是 Ident 映射文件
PG_VERSION 是 PostgreSQL 的版本号
postgresql.conf 是参数配置文件
postmaster.opts 是启动该数据库的 pg_ctl 命令
postmaster.pid 是该数据库的进程号和数据目录信息

其中base 下面的文件夹结构为：

# ls
1 10890 10891 16992 285346

其中一个文件夹代表一个数据库，文件夹的名字就是数据库的oid，可以通过 pg_databse查询其对应关系

9.6 数据文件存储分布

下面分别介绍表、索引、序列的文件存储分布（外部表和视图没有实际的数据，没有数据文件生成），每一种类型对应数据库里面哪些文件

（1）表

一般的堆表只有一个数据文件，如果表中有大字段，会多两个数据文件：toast表、toast表索引。

大字段：text字段、varchar的大小大于2036的字段。

select oid,relname,reltoastrelid from pg_class where relname='表名';

默认 toast表的名字为 pg_toast_ + 原表的 relfilenode，索引为 pg_toast_ + 原表的 relfilenode + _index，例如316696是原表的oid：

# select oid, relname from pg_class where relname ~ '316696';

 oid    |  relname
 -----------------
 316698 | pg_toast_316696
 316699 | pg_toast_316696_index

如果是 Appendonly 表，那么会多4个文件：pg_aoseg、pg_aovisimap表及其索引对应的数据文件（参阅第6章）

（2）索引

索引文件只有一个，可以通过索引名在pg_class中查找。索引在创建时就分配了32KB 的存储控件，等到这32 KB用完才开始扩大。

（3）序列

序列（Sequence）与索引一样，也只有一个数据文件，在pg_class中对应一条记录，relfilenode 字段就是文件名。

如果一个表中有字段是 serial 类型的，即一个递增序列，那么这个表会自动创建一个序列，也就会多一个数据文件。

9.7 表空间管理

在 gp4.0 之后的版本中，gp加入了文件空间（Filespace）和表空间（Tablespace）的概念。

在系统初始化的时候，只有两个表空间 pg_default 和 pg_global，这两个表空间都在 pg_system 这个文件系统下：

select a.spcname, b.fsname from pg_tablespace a ,pg_filespace b where spcfsoid=b.oid;

 spcname    | fsname
 ------------------------
 pg_default | pg_system
 pg_global  | pg_system

pg_global 表空间保存的是各个数据库之间的通用信息，在 data_directory/global 目录下，pg_default 表空间保存的是每个数据库特有的数据，包括数据字典及用户数据。其中，每一个数据库都会有一个对应的数据目录，如果数据库中的表比较多，或者表分区比较多（每一个分区都相当于一张表），那么在一个目录下就会有非常多的文件，文件数太多会给文件系统带来非常大的压力。因此，当文件数增长到一定程度的时候，就必须使用表空间，将数据存放到多个目录下。

在 gp 中，表空间必须创建在文件空间上，默认只有 pg_system 一个文件空间，在这个文件空间上不能再创建其他的表空间。

下面介绍如何创建多一个文件空间：

为每一个表空间创建系统目录，在master 和每一个 segment 上都要创建：

MASTER:/home/gpadmin/gpdata/master_fspc
Primary Segment:/home/gpadmin/gpdata/primary_fspc
Mirror Segment:/home/gpadmin/gpdata/mirror_fspc

运行 gpfilespace 脚本，根据提示输入文件系统的名字和每个segment的目录
```
$ gpfilespace
...
> fs_test
...
```
之后，会生成一个 gp_filespace_config 文件，这个文件中保存了每个 segment 对应的数据目录（也可以手动编辑这个文件）：
```
cat /home/gpadmin/gpdata/gpfilespace_config_20120603_210559
fielspace:fs_test
mdw:1:/home/gpadmin/gpdata/master_fspc?gpseg-1
...
```
运行 gpfilespace 创建文件系统：
```
$ gpfilespace --config /home/gpadmin/gpdata/gpfilespace_config_20120603_210559
```
创建好文件系统之后，就可以在上面创建表空间了：
```
testDB#= create tablespace tbs_test1 filespace fs_test;
CREATE TABLESPACE
```
在表空间创建成功之后，在建表时就可以使用参数，指定表建在哪个表空间下面：
```
create table test_01 (id int ,name varchar(200))
tablespace tbs_test1 distributed by (id);
```
在filespace 的目录下，就可以看到刚刚创建的表的数据文件了：

9-5

默认都是在 default_tablespace（参数）下面建表，这个参数默认是 pg_default 表空间，可以在配置文件 postgresql.conf 中修改这个参数，或者以下命令：
```
set default_tablespace='tbs_test1';
```
这样，建的表就在这个表空间下。还可以为每一个用户设置不同的表空间，同时必须给表空间赋权：
```
testDB=# alter role etl set default_tablespace='tbs_test2';
ALTER ROLE
testDB=# grant ALL on tablespace tbs_test2 to etl;
```
更换表空间，将表 hello1 从表空间tbs_test2 更换到 tbs_test3：
```
testDB=# alter table hello1 set tablespace tbs_test3;
```

9.8 小结

本章介绍了数据库管理的相关内容，以及一些基本的数据库管理工具。

Greenplum企业应用实战（笔记）：第九章数据库管理

Greenplum企业应用实战（笔记）：第九章数据库管理

第九章数据库管理

9.1 用户及权限管理

9.1.1 Greenplum 数据库逻辑结构

9.1.2 Grant 语法（赋权）

9.2 登录权限控制

9.3 资源队列即并发控制

9.4 Greenplum 锁机制

9.5 数据目录结构

9.6 数据文件存储分布

9.7 表空间管理

9.8 小结

Greenplum企业应用实战（笔记）：第九章 数据库管理

第九章 数据库管理

9.1 用户及权限管理

9.1.1 Greenplum 数据库逻辑结构

9.1.2 Grant 语法（赋权）

9.2 登录权限控制

9.3 资源队列即并发控制

9.4 Greenplum 锁机制

9.5 数据目录结构

9.6 数据文件存储分布

9.7 表空间管理

9.8 小结

Greenplum企业应用实战（笔记）：第九章数据库管理

第九章数据库管理