1、Greenplum 數(shù)據(jù)庫(kù)開發(fā)基礎(chǔ),2014年10月,目錄,Greenplum概述和數(shù)據(jù)分布,數(shù)據(jù)加載和外部表,客戶端工具,Greenplum數(shù)據(jù)庫(kù)基礎(chǔ),Greenplum與Oracle,優(yōu)化策略,其他要點(diǎn)及示例,MPP架構(gòu),MPP (Massively Parallel Processing) Shared-Nothing Architecture,MPP無(wú)共享架構(gòu)的優(yōu)勢(shì),數(shù)據(jù)分布在所有的并行節(jié)點(diǎn)上 每個(gè)節(jié)點(diǎn)只處理其中一部分?jǐn)?shù)據(jù) 最優(yōu)化的I/O處理 所有的節(jié)點(diǎn)同時(shí)進(jìn)行并行處理 節(jié)點(diǎn)之間完全無(wú)共享，無(wú)I/O沖突 自動(dòng)化的并行處理機(jī)制 內(nèi)部處理自動(dòng)化并行，無(wú)需人工分區(qū)或優(yōu)化 加載與訪問(wèn)方式與一般

2、數(shù)據(jù)庫(kù)相同 最易于擴(kuò)展的架構(gòu) BI和數(shù)據(jù)分析的最佳選擇 增加節(jié)點(diǎn)實(shí)現(xiàn)線性擴(kuò)展 增加節(jié)點(diǎn)可線性增加存儲(chǔ)、查詢和加載性能,Greenplum基本架構(gòu),MPP (Massively Parallel Processing) Shared-Nothing Architecture,Network Interconnect,.,.,Master Severs Query planning 如果表沒有主鍵，或者沒有合適的字段作為分布鍵，可以使用隨機(jī)分布鍵 （DISTRIBUTED RANDOMLY） 如果沒有明確定義分布鍵，系統(tǒng)會(huì)把第一個(gè)字段作為表的分布鍵,分布存儲(chǔ),數(shù)據(jù)均勻分布- 并行處理的關(guān)鍵,Ord

3、er,策略: 數(shù)據(jù)盡可能的均勻分布到每個(gè)節(jié)點(diǎn),查詢命令的執(zhí)行,SQL查詢處理機(jī)制,并行查詢計(jì)劃,SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date=04302008;,壓縮存儲(chǔ)和行列存儲(chǔ),壓縮存儲(chǔ) 支持ZLIB和QUICKLZ方式的壓縮,壓縮比可到10:1 壓縮表只能是Append Only方式 壓縮數(shù)據(jù)不一定會(huì)帶來(lái)性能的下降，壓縮表將消耗CPU資源，而減少I/O資源占用 語(yǔ)法 CREATE TABLE foo (a int, b text) WITH (appendonly=true, com

4、presstype=zlib, compresslevel=5); 行或列存儲(chǔ)模式 列模式目前只支持Append Only 如果常用的查詢只取表中少量字段，則列模式效率更高，如查詢需要取表中的大量字段，行模式效率更高 語(yǔ)法： CREATE TABLE sales2 (LIKE sales) WITH (appendonly=true, orientation=column);,鎖,停止活動(dòng)的SQL查詢,查看要停止的SQL查詢的進(jìn)程ID 執(zhí)行 select * from pg_stat_activity 查看到當(dāng)前數(shù)據(jù)庫(kù)連接的IP 地址，用戶名，提交的查詢等。（另外也可以在master 主機(jī)上查

5、看進(jìn)程，對(duì)每個(gè)客戶端連接，master 都會(huì)創(chuàng)建一個(gè)進(jìn)程。ps -ef |grep -i postgres |grep -i con） #查詢表是否被鎖 select procpid,t.* from pg_stat_activity t where usename = lds_betl and datname = ldsdb and waiting = t; 停止SQL： 執(zhí)行 Select pg_cancel_backend(procpid) 或者 Select pg_terminate_backend(procpid) 或者 在MASTER OS：$ kill procpid 注：極端情

6、況下， kill 不能停止SQL時(shí)，采用kill -11停止進(jìn)程 千萬(wàn)不要使用kill -9，該操作導(dǎo)致數(shù)據(jù)庫(kù)崩潰； 生產(chǎn)系統(tǒng)請(qǐng)不要采用kill操作。,表分區(qū)的概念,將一張大表邏輯性地分成多個(gè)部分，如按照分區(qū)條件進(jìn)行查詢，將減少數(shù)據(jù)的掃描范圍，提高系統(tǒng)性能。 提高對(duì)于特定類型數(shù)據(jù)的查詢速度和性能 也可以更方便數(shù)據(jù)庫(kù)的維護(hù)和更新 兩種類型: Range分區(qū) (日期范圍或數(shù)字范圍)/如日期、價(jià)格等 List 分區(qū),例如地區(qū)、產(chǎn)品等 Greenplum中的表分區(qū)在使用中具有總表的繼承性，并通過(guò)Check參數(shù)指定相應(yīng)的子表 分區(qū)的子表依然根據(jù)分布策略分布在各segment上 分區(qū)是一種非常有用的優(yōu)化措

7、施，例如一年的交易按交易日期分區(qū)后，查詢一天的交易性能將提高365倍！,Data Distribution To see a list of all schemas in the database: dn To see the schema search path: SHOW search_path; To set the search path for a database: ALTER DATABASE SET search_path TO myschema, public, pg_catalog;,表,To Create: CREATE TABLE Additional DISTRIBU

8、TED BY or DISTRIBUTED RANDOMLY clause Some syntax not supported To Edit: ALTER TABLE Cannot alter distribution key columns To Drop: DROP TABLE PSQL Tips To list tables in the database: dt To see structure of a table: d+ table_name To list system catalog tables: dtS To list external tables only: dx T

9、o see the distribution key columns of a table: d table_name,表和字段約束,CHECK table or column constraints NOT NULL column constraints UNIQUE column constraints One allowed per table Unique columns must also be in distribution key Not allowed if table also has a primary key PRIMARY KEY table constraints U

10、sed as distribution key by default FOREIGN KEY constraints definitions are supported but not enforced Foreign key relationships are utilized by the query planner to improve query plans.,視圖,View SQL Commands: CREATE VIEW DROP VIEW PSQL Tips: To list all views while in psql: dv To see a view definitio

11、n: d+ view_name EXAMPLE: CREATE VIEW topten AS SELECT name, rank, gender, year FROM names, rank WHERE rank =b.min_ip and a.ip=b.max_ip A表：千萬(wàn)級(jí) B表：幾萬(wàn)行 在A表上建立IP的b-tree索引可以提高系統(tǒng)效率 11、表分區(qū)提高查詢效率 12、Truncate代替delete all，避免Vacuum處理；,優(yōu)化策略（5）,例12： Demo1：Vacuum analyze 優(yōu)化 Table1：100萬(wàn)條數(shù)據(jù) create table table1 ( t1

12、 integer, t2 varchar )distributed randomly; Table1：1千條數(shù)據(jù) create table table2 ( t1 integer, t2 varchar )distributed randomly; 執(zhí)行SQL: select a.*,b.* from table1 a , table2 b where a.t1=b.t1;,優(yōu)化策略（6）,未作vacuum analyze前：,Vacuum analyze 后：,說(shuō)明：對(duì)小表做Broadcast到所有節(jié)點(diǎn)，比對(duì)大表做redistribution更好性能 知識(shí)點(diǎn)：greenplum中有三種數(shù)據(jù)的

13、移動(dòng)操作： A: Broadcast Motion (N:N) ,即廣播數(shù)據(jù)，每個(gè)節(jié)點(diǎn)向其他節(jié)點(diǎn)廣播需要發(fā)送的數(shù)據(jù)。 B: Redistribute Motion (N:N) ，重新分布數(shù)據(jù)，利用join的列值hash不同，將篩選后的數(shù)據(jù)在其他segment重新分布。 C: Gather Motion (N:1)，聚合匯總數(shù)據(jù)，每個(gè)節(jié)點(diǎn)將join后的數(shù)據(jù)發(fā)到一個(gè)單節(jié)點(diǎn)上，通常是發(fā)到主節(jié)點(diǎn)master。,優(yōu)化策略（7）,13、DK優(yōu)化，相同DK 表關(guān)聯(lián)性能更高 create table table1 ( t1 integer, t2 varchar )distributed by(t1); cr

14、eate table table2 ( t1 integer, t2 varchar )distributed by (t1,t2); 執(zhí)行SQL: select a.*,b.* from table1 a , table2 b where a.t1=b.t1;,優(yōu)化策略（8）,修改table2的DK； create table table2 ( t1 integer, t2 varchar )distributed by (t1); 說(shuō)明：兩個(gè)表的DK相同，節(jié)省了做Redistribution的消耗。,優(yōu)化策略（9）,需要說(shuō)明的是，DK需要順序相同，類型相同，才能避免redistributi

15、on。 例如： CREATE TABLE test ( t1 integer, t2 character varying(10) ) DISTRIBUTED BY (t1); CREATE TABLE test1 ( t1 integer, t2 character varying(10) ) DISTRIBUTED BY (t2); select * from test a ,test1 b where a.t1 = b.t2 兩個(gè)表都要做重分布；,優(yōu)化策略（10）,14、批量數(shù)據(jù)處理后，無(wú)論成功與否，都應(yīng)該進(jìn)行vaccum analyze . analyze $prefixion.$tar

16、_table ( $key_list ); analyze $prefixion.$tar_table; 15、對(duì)于大表的UNION操作，請(qǐng)用UNION ALL 加 group by進(jìn)行改寫 16、嵌套查詢操作，盡量改寫成連接查詢操作 17、大表更新操作，盡量通過(guò)外連接+插入+truncate進(jìn)行替代 18、大表刪除操作，盡量通過(guò)外連接+插入+truncate進(jìn)行替代 19、盡量避免進(jìn)行存儲(chǔ)過(guò)程函數(shù)嵌套導(dǎo)致鎖沖突，考慮使用其它語(yǔ)言進(jìn)行總體調(diào)度，比如shell,java，c等。 20、盡量避免在數(shù)據(jù)庫(kù)中使用序列，游標(biāo), 循環(huán)，考慮對(duì)數(shù)據(jù)進(jìn)行整體操作。 21、避免高度頻繁的建表刪表操作，容易造成字

17、典破碎和字典鎖的問(wèn)題，因此可以通過(guò)臨時(shí)表和其它語(yǔ)言方式替代。 22、檢查大表的hash鍵定義是否可以保證每個(gè)segment存儲(chǔ)均勻，數(shù)據(jù)處理均勻，以及大表連接操作過(guò)程中可以盡量避免motion操作。,優(yōu)化策略（11）,23、運(yùn)行較大操作時(shí)，不建議使用pgadmin客戶端完成操作，建議寫成shell腳本在后臺(tái)進(jìn)行測(cè)試。避免頻繁不正常中斷操作，對(duì)整個(gè)開發(fā)產(chǎn)生影響 24、盡量避免不正常中斷操作，如需不正常中斷操作，請(qǐng)使用函數(shù)pg_cancel_backend函數(shù)，并耐心等待35分鐘，再繼續(xù)相關(guān)操作，避免造成數(shù)據(jù)字典破壞。,目錄,Greenplum概述和數(shù)據(jù)分布,數(shù)據(jù)加載和外部表,客戶端工具,Gree

18、nplum數(shù)據(jù)庫(kù)基礎(chǔ),Greenplum與Oracle,優(yōu)化策略,其他要點(diǎn)及示例,注意事項(xiàng),新一代數(shù)據(jù)倉(cāng)庫(kù)GP數(shù)據(jù)庫(kù)字符集：UTF8 關(guān)于NULL：不等于NULL 表級(jí)授權(quán) 盡量選擇常用連接條件作為Distribution Key 大批量ETL加工最好不建索引，對(duì)性能提升作用不大 Truncate代替delete all，避免Vacuum處理 盡量避免在數(shù)據(jù)庫(kù)中使用游標(biāo), 循環(huán) 避免高度頻繁的建表刪表操作 要特別注意Greenplum數(shù)據(jù)庫(kù)設(shè)計(jì)開發(fā)運(yùn)行規(guī)范檢查點(diǎn)中的強(qiáng)制條款,Exercise-distribution,Create temp table test as (select 1:in

19、teger, generate_series(1,100) t2) ; -沒有設(shè)置DK select gp_segment_id,count(*) from test group by 1 order by 1; -分布不均勻 Create temp table test1 as (select 1:integer, generate_series(1,100) t2) distributed by (t2); -設(shè)置DK select gp_segment_id,count(*) from test1 group by 1 order by 1; -分布均勻 Create temp tabl

20、e test2 as (select 1:integer, generate_series(1,100) t2) distributed randomly; -隨機(jī)分布 select gp_segment_id,count(*) from test2 group by 1 order by 1; -分布均勻,Exercise-redistribution,Create temp table test1 as (select 1:integer t1, generate_series(5000,15000) t2 ) distributed by (t2); Create temp table

21、test2 as (select 1:integer t1, generate_series(1,10000) t2 ) distributed by (t2); Explain Select count(*) from test1 a inner join test2 b on a.t2=b.t2; -no 用DK做關(guān)聯(lián)條件，不會(huì)重分布 Create temp table test1 as (select 1:integer t1, generate_series(5000,15000) t2 ) distributed by (t2); Create temp table test2 as

22、 (select 1:integer t1, generate_series(1,10000) t2 ) distributed by (t1); Explain Select count(*) from test1 a inner join test2 b on a.t2=b.t2; -yes Test2用非DK做關(guān)聯(lián)條件，會(huì)導(dǎo)致重分布 Create temp table test1 as (select 1:integer t1, generate_series(5000,15000) t2 ) distributed by (t2); Create temp table test2 as

23、 (select 1:integer t1, generate_series(1,10000) t2 ) distributed by (t2,t1); Explain Select count(*) from test1 a inner join test2 b on a.t2=b.t2; -yes Test2關(guān)聯(lián)條件是DK的一部分字段，會(huì)導(dǎo)致重分布,Exercise-redistribution,Create temp table test1 as (select 1:integer t1, generate_series(5000,15000):integer t2 ) distribu

24、ted by (t2); Create temp table test2 as (select 1:integer t1, generate_series(1,10000):varchar t2 ) distributed by (t2); Explain Select count(*) from test1 a inner join test2 b on a.t2=b.t2; -yes DK數(shù)據(jù)類型不一致 Create temp table test1 as (select 1:integer t1, generate_series(50000,150000):integer t2 ) distributed randomly; Create temp table test2 as (select 1:integer t1, generate_series(1,100000):varchar t2 ) distributed randomly; Explain Select count(*) from test1 a inner join