我們前面已經(jīng)說了，GPDB是一個列式數(shù)據(jù)庫，數(shù)據(jù)庫都有什么特性呢？都能做什么呢？熟悉MySQL，Oracle的同學(xué)可能就比較清楚關(guān)系型數(shù)據(jù)庫的管理，下面我們就來說下GPDB的一些管理和常用操作。
數(shù)據(jù)庫常用操作分為:創(chuàng)建數(shù)據(jù)庫、創(chuàng)建schema、創(chuàng)建資源池、創(chuàng)建角色、創(chuàng)建用戶、創(chuàng)建表。下面我們一一來說下GPDB這些常用操作，和關(guān)系型數(shù)據(jù)庫MYSQL有什么不同呢？
-
創(chuàng)建數(shù)據(jù)庫
gp_sydb=# CREATE DATABASE GPTEST;
CREATE DATABASE
刪除數(shù)據(jù)庫
gp_sydb=# DROP DATABASE GPTEST;
DROP DATABASE
你也可以通過 createdb 創(chuàng)建數(shù)據(jù)庫
[gpadmin@mpp01 ~]$ createdb -h mpp01 -p 5432 GPTEST
查看創(chuàng)建的數(shù)據(jù)庫：
gp_sydb=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
GPTEST | gpadmin | UTF8 |
gp_sydb | gpadmin | UTF8 |
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(5 rows)
- 創(chuàng)建schema
創(chuàng)建schema
GPTEST=# CREATE SCHEMA bdp;
CREATE SCHEMA
查看schema
GPTEST=# \dn
List of schemas
Name | Owner
--------------------+---------
bdp | gpadmin
gp_toolkit | gpadmin
information_schema | gpadmin
pg_aoseg | gpadmin
pg_bitmapindex | gpadmin
pg_catalog | gpadmin
pg_toast | gpadmin
public | gpadmin
(8 rows)
查看當(dāng)前的schema
GPTEST=# SELECT current_schema();
current_schema
----------------
public
(1 row)
可以看到當(dāng)前schema是public，這是創(chuàng)建用戶的時候，我們沒有給它分配schema，那就是用默認(rèn)的schema public。
把新建的schema賦給數(shù)據(jù)庫gptest
gptest=# \dn
List of schemas
Name | Owner
--------------------+---------
bdp | gpadmin
gp_toolkit | gpadmin
information_schema | gpadmin
pg_aoseg | gpadmin
pg_bitmapindex | gpadmin
pg_catalog | gpadmin
pg_toast | gpadmin
public | gpadmin
(8 rows)
gptest=# ALTER DATABASE GPTEST SET search_path TO bdp, public, pg_catalog;
ALTER DATABASE
gptest=# SELECT current_schema();
current_schema
----------------
bdp
(1 row)
可以看到賦了新的schema。
- 創(chuàng)建角色
創(chuàng)建角色，如果有很多用戶對于一批表都有一樣的權(quán)限，這時候可以創(chuàng)建一個角色，把這些權(quán)限先賦給角色，然后把角色賦權(quán)給各個用戶。
gptest=# create role selectbdp;
CREATE ROLE
gptest=# \du
List of roles
Role name | Attributes | Member of
-----------+---------------------------------------------------------------------------------------------------------------------------------+-----------
bdp01 | | {}
gpadmin | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Ext hdfs Table, Wri Ext hdfs Table | {}
selectbdp | Cannot login | {}
gptest=#
可以看到角色已經(jīng)創(chuàng)建成功。
賦權(quán)角色。
gptest=# grant selectbdp to gpadmin;
GRANT ROLE
角色其實就是一個用戶組。
- 創(chuàng)建用戶：
gptest=# create user bdp01 WITH PASSWORD 'passwd123';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gptest=#
使用用戶登錄：
psql -Ubdp01 -d gptest -w 'passwd123'
發(fā)現(xiàn)報錯了：
[gpadmin@mpp01 gpseg-1]$ psql -Ubdp01 -d gptest -w 'passwd123'
Password for user bdp01:
psql: FATAL: no pg_hba.conf entry for host "[local]", user "bdp01", database "gptest", SSL off
GPDB登錄需要在pg_hba.conf文件中添加信任的用戶：
local all bdp01 trust
添加后，使配置文件生效，再次登錄：
[gpadmin@mpp01 gpseg-1]$ psql -Ubdp01 -d gptest -w 'passwd123'
psql: warning: extra command-line argument "passwd123" ignored
psql (8.3.23)
Type "help" for help.
gptest=>
登錄成功！
- 創(chuàng)建資源隊列
為什么要創(chuàng)建資源隊列呢？我們知道一個數(shù)據(jù)庫肯定不是給一個用戶使用的，通常是有ETL用戶，進(jìn)行數(shù)據(jù)加載，數(shù)據(jù)清洗。還有WEB端用戶進(jìn)行查詢，還有個人用戶進(jìn)行開發(fā)查詢使用等等。這么多用戶，如果不進(jìn)行資源隊列管控，那么就會出現(xiàn)資源爭搶現(xiàn)象，影響線上應(yīng)用功能。比如一個新手開發(fā)人員寫了一個比較菜的查詢語句，占用了大量資源，此時數(shù)據(jù)庫的其他查詢用戶都需要排隊等待了。再比如加載數(shù)據(jù)是需要使用大量資源，那么也會影響查詢性能，以上你會發(fā)現(xiàn)，你需要把不同類型的操作進(jìn)行資源隔離，這就是為什么要有資源隊列了。
查看已有的資源隊列：
gptest=> SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status
gptest-> WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;
rolname | rsqname
-----------+------------
bdp01 | pg_default
selectbdp | pg_default
gpadmin | pg_default
(3 rows)
創(chuàng)建一個新的資源隊列：
gptest=# create resource queue load_queue with (active_statements=3,MEMORY_LIMIT='1024MB',PRIORITY=LOW); CREATE QUEUE
把創(chuàng)建的資源隊列賦權(quán)給剛才創(chuàng)建的用戶bdp01:
gptest=# ALTER USER bdp01 resource queue load_queue;
ALTER ROLE
再次查詢資源池分配情況：
gptest=# SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;
rolname | rsqname
-----------+------------
bdp01 | load_queue
selectbdp | pg_default
gpadmin | pg_default
(3 rows)
可以看到bpd01已經(jīng)使用了新的資源池了。
- 創(chuàng)建表
簡單的建表語句，此處不在講解了，來說兩個地方，一個是分區(qū)，一個是分布鍵。 - 分區(qū)：
來看下分區(qū)的數(shù)據(jù)結(jié)構(gòu)
分區(qū)是把一張大表按照適合的維度進(jìn)行分割，通過表的繼承，規(guī)則，約束實現(xiàn)。
并不是每個表都適合分區(qū)，只有很大的表才適合分區(qū)，應(yīng)為分區(qū)多了會增加表的元數(shù)據(jù)信息，特別是多級分區(qū)。如果一個表被按照日和城市劃分并且有1000個日以及1000個城市，那么分區(qū)的總數(shù)就是一百萬。列存表會把每一列存在一個物理表中，因此如果這個表有100個列，系統(tǒng)就需要為該表管理一億個文件。一方面Linux的iNode可能不會有這么大，就會出現(xiàn)文件查詢報錯等，分區(qū)過多了，對于數(shù)據(jù)庫停止和恢復(fù)也會造成很大影響的。所以建立分區(qū)表的時候，對分區(qū)進(jìn)行自動的創(chuàng)建和刪除，以保障合適的數(shù)據(jù)周期很重要。
- 分布鍵 在MPP架構(gòu)中，一個查詢是需要從所有segment獲取數(shù)據(jù)然后在master匯總得到結(jié)果的。那么這樣，影響查詢性能的就有兩個問題，查詢最慢的segment和網(wǎng)絡(luò)帶寬。分布鍵就是為了解決第一個問題，讓數(shù)據(jù)盡可能均勻的分布在每個segment上，從而避免數(shù)據(jù)傾斜導(dǎo)致的查詢效率問題。第二個網(wǎng)絡(luò)問題，MPP數(shù)據(jù)集群搭建要求集群內(nèi)部為萬兆網(wǎng)連接，并且機(jī)器配置一樣，從而避免影響。
分布鍵一般選擇主鍵，比如手機(jī)號等。
盡量選擇經(jīng)常需要 JOIN 的列，當(dāng)關(guān)聯(lián)鍵和分布鍵均一致時，可以在 Segment 中完成 JOIN，不需要重分布或者廣播小表。
以上就是GPDB簡單的管理流程，后面我們會再介紹更深入的查詢優(yōu)化和管理知識。
