第九章 数据库管理
[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 小结
本章介绍了数据库管理的相关内容，以及一些基本的数据库管理工具。
网友评论