優(yōu)化工作：

1. 數(shù)據(jù)表分區(qū)盡量采用數(shù)值類型字段，如Date類型轉(zhuǎn)換為距離1970-01-01的絕對天數(shù)。
2. SQL語法：盡量避免between and 的使用(查看查詢計(jì)劃，影響不大)，多個(gè)子查詢時(shí)盡量使用CTE（with v as…）查詢。
3. 根據(jù)表的數(shù)據(jù)量以及大多數(shù)查詢的類型設(shè)計(jì)數(shù)據(jù)分布策略（隨機(jī)分布o(jì)r哈希分布）以及bucketnum參數(shù)設(shè)置。
4. 冷熱數(shù)據(jù)存儲到不同schema相同的表中，盡量減小熱數(shù)據(jù)所在表的分區(qū)數(shù)(通過動態(tài)增加和刪除分區(qū))。
5. 根據(jù)SQL所需的資源設(shè)置參數(shù)hawq_rm_stat_nvseg和hawq_rm_vseg_memory。
6. 每天定時(shí)執(zhí)行“vacuum table_name;analyze table_name;”獲取每個(gè)表的統(tǒng)計(jì)信息，以便生成最優(yōu)的查詢計(jì)劃。
7. 通過執(zhí)行”vacuum pg_class; reindex table pg_class”源數(shù)據(jù)表pg_class，減少元數(shù)據(jù)記錄數(shù)。
8. 執(zhí)行分析查詢計(jì)劃，找到SQL的性能瓶頸，有針對性的優(yōu)化。
9. 采取措施盡量提升查詢數(shù)據(jù)的本地化比率。

實(shí)際測試

數(shù)據(jù)表采用隨機(jī)分布： bucketnum=9

創(chuàng)新互聯(lián)是網(wǎng)站建設(shè)專家,致力于互聯(lián)網(wǎng)品牌建設(shè)與網(wǎng)絡(luò)營銷，專業(yè)領(lǐng)域包括做網(wǎng)站、網(wǎng)站建設(shè)、電商網(wǎng)站制作開發(fā)、成都微信小程序、微信營銷、系統(tǒng)平臺開發(fā)，與其他網(wǎng)站設(shè)計(jì)及系統(tǒng)開發(fā)公司不同，我們的整合解決方案結(jié)合了恒基網(wǎng)絡(luò)品牌建設(shè)經(jīng)驗(yàn)和互聯(lián)網(wǎng)整合營銷的理念，并將策略和執(zhí)行緊密結(jié)合，且不斷評估并優(yōu)化我們的方案，為客戶提供全方位的互聯(lián)網(wǎng)品牌整合方案！

1.設(shè)置用于查詢的virtual segment數(shù)量
語句級別：

SET hawq_rm_stmt_nvseg=10;
SET hawq_rm_stmt_vseg_memory='256mb';

• 禁用語句級別

  SET hawq_rm_stmt_nvseg=0;

set hawq_rm_nvseg_perquery_perseg_limit=10;
set hawq_rm_nvseg_perquery_limit=512;

??通過hawq_rm_nvseg_perquery_limit和hawq_rm_nvseg_perquery_perseg_limit參數(shù)可以調(diào)整查詢執(zhí)行時(shí)使用的virtual segments的數(shù)量

2.哈希分布表的相關(guān)參數(shù)：

default_hash_table_bucket_number
hawq_rm_nvseg_perquery_limit
hawq_rm_nvseg_perquery_perseg_limit

3.可以使用pg_partitions視圖查找有關(guān)分區(qū)設(shè)計(jì)的信息。例如，查看銷售表的分區(qū)設(shè)計(jì)：

SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
FROM pg_partitions
WHERE tablename='ins_wifi_dates';

下表和視圖顯示有關(guān)分區(qū)表的信息。

• pg_partition - 跟蹤分區(qū)表及其繼承關(guān)系。
• pg_partition_templates - 顯示使用子分區(qū)模板創(chuàng)建的子分區(qū)。
• pg_partition_columns - 顯示分區(qū)設(shè)計(jì)中使用的分區(qū)鍵列。

4.查看表的segment file分布

SELECT gpr.tablespace_oid,
         gpr.database_oid,
         gpf.relfilenode_oid,
         gpf.segment_file_num,
         '/hawq_data/'||gpr.tablespace_oid||'/'||gpr.database_oid||'/'||gpf.relfilenode_oid ||'/'||gpf.segment_file_num as path,
         pg_class.relname,
         gpr.persistent_state,
         gpf.persistent_state
FROM gp_persistent_relfile_node gpf,pg_class, gp_persistent_relation_node gpr
WHERE gpf.relfilenode_oid = pg_class.relfilenode
        AND gpr.relfilenode_oid = pg_class.relfilenode
        AND pg_class.relname='person';

SELECT distinct gpr.tablespace_oid,
         gpr.database_oid,
         gpf.relfilenode_oid,
         pg_class.relname,
         gpr.persistent_state,
         gpf.persistent_state
FROM gp_persistent_relfile_node gpf,pg_class, gp_persistent_relation_node gpr
WHERE gpf.relfilenode_oid = pg_class.relfilenode
        AND gpr.relfilenode_oid = pg_class.relfilenode
        AND pg_class.relname like 'person_%' order by pg_class.relname ;

#schema

SELECT gpr.tablespace_oid,
         gpr.database_oid,
         gpf.relfilenode_oid,
         gpf.segment_file_num,
         '/hawq_data/'||gpr.tablespace_oid||'/'||gpr.database_oid||'/'||gpf.relfilenode_oid ||'/'||gpf.segment_file_num as path,
        pgn.nspname AS schemaname,
         pg_class.relname AS tablename,
         gpr.persistent_state,
         gpf.persistent_state
FROM gp_persistent_relfile_node gpf,pg_class, gp_persistent_relation_node gpr, pg_namespace pgn
WHERE gpf.relfilenode_oid = pg_class.relfilenode
        AND gpr.relfilenode_oid = pg_class.relfilenode 
        AND pgn.oid = pg_class.relnamespace
        AND pg_class.relname='t_wifi_terminal_chrs_1_prt_1';

經(jīng)過測試發(fā)現(xiàn)：
?數(shù)據(jù)在hdfs中的存儲位置為： tablespace/database/table/segfile
分區(qū)表A目錄中有默認(rèn)哈希桶數(shù)目的segfile，但大小都為0，而其字表（如a1）目錄中有默認(rèn)哈希桶數(shù)目的segfile，且有文件。

查看表大小：

select sotdsize from hawq_toolkit.hawq_size_of_table_disk where sotdtablename='t_net_access_log';

5.使用explain 或者 explain analyze 查看查詢計(jì)劃時(shí)，指定

set gp_log_dynamic_partition_pruning=on;

可以顯示掃描的分區(qū)名稱。

??Explain analyze和explain語句不同，explain analyze會真正執(zhí)行查詢，并得到查詢執(zhí)行過程中的統(tǒng)計(jì)數(shù)據(jù)。explain analyze的結(jié)果對了解查詢執(zhí)行的具體情況以及了解查詢性能問題產(chǎn)生的原因有很大幫助。

SELECT * FROM pg_stats  WHERE tablename = 'inventory';

#查詢會話信息

select * from pg_stat_activity;

select application_name, datname, procpid, sess_id, usename, waiting, client_addr, client_port, waiting_resource,  query_start, backend_start, xact_start from pg_stat_activity;

select application_name, datname, procpid, sess_id, usename, waiting, client_addr, client_port, waiting_resource, current_query, query_start, backend_start, xact_start from pg_stat_activity;

select application_name, datname, procpid, sess_id, usename, waiting, client_addr, client_port, waiting_resource,  query_start, backend_start, xact_start from pg_stat_activity where application_name='psql' and current_query<>'';

datname表示數(shù)據(jù)庫名
procpid表示當(dāng)前的SQL對應(yīng)的PID
query_start表示SQL執(zhí)行開始時(shí)間
current_query表示當(dāng)前執(zhí)行的SQL語句
waiting表示是否正在執(zhí)行，t表示正在執(zhí)行，f表示已經(jīng)執(zhí)行完成
client_addr表示客戶端IP地址
284933
kill有兩種方式，第一種是：

SELECT pg_cancel_backend(PID);

這種方式只能kill select查詢，對update、delete 及DML不生效)

第二種是：

SELECT pg_terminate_backend(PID);

這種可以kill掉各種操作(select、update、delete、drop等)操作

在pg_cancel_backend()下，session還在，事物回退;
在pg_terminate_backend()操作后，session消失，事物回退。

如果在某些時(shí)候pg_terminate_backend()不能殺死session，那么可以在os層面，直接kill -9 pid

select * from pg_resqueue_status;

--資源隊(duì)列

SELECT * FROM dump_resource_manager_status(2);

--Segment

SELECT * FROM dump_resource_manager_status(3);

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname = 'ins_wifi_dates';
SELECT * FROM pg_stats  WHERE tablename = 'ins_wifi_dates';

SELECT gp_segment_id, COUNT(*)
FROM ins_wifi_dates
GROUP BY gp_segment_id
ORDER BY gp_segment_id

set gp_select_invisible=true;
select count(*) from pg_class;
set gp_select_invisible=false;
select count(*) from pg_class;

vacuum pg_class;
reindex table pg_class;

日志：
set

表重分布：

ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

檢查未analyze的表：

select * from hawq_toolkit.hawq_stats_missing;

http://hawq.incubator.apache.org/docs/userguide/2.2.0.0-incubating/reference/toolkit/hawq_toolkit.html#topic46
HAWQ查看表大?。? //不包含分區(qū)表

SELECT relname AS name, sotdsize AS size, sotdtoastsize AS 
toast, sotdadditionalsize AS other 
FROM hawq_toolkit.hawq_size_of_table_disk AS sotd, pg_catalog.pg_class 
WHERE sotd.sotdoid=pg_class.oid and pg_class.relname='t_wifi_terminal_chrs'
ORDER BY relname;

hawq_size_of_partition_and_indexes_disk

select relname AS name, sopaidpartitionoid, sopaidpartitiontablename, sopaidpartitiontablesize as size, sotailtablesizeuncompressed as uncompressed from hawq_toolkit.hawq_size_of_partition_and_indexes_disk sopi,pg_catalog.pg_class WHERE sopi.sopaidparentoid=pg_class.oid and pg_class.relname='t_wifi_terminal_chrs'
ORDER BY sopaidpartitionoid;

select relname AS name,sum(sopaidpartitiontablesize) as size from hawq_toolkit.hawq_size_of_partition_and_indexes_disk sopi,pg_catalog.pg_class WHERE sopi.sopaidparentoid=pg_class.oid and pg_class.relname='t_wifi_terminal_chrs'
group by relname ;

內(nèi)存/vore比值

[root@master2 pg_log]# cat hawq-2017-10-17_224829.csv 
2017-10-17 18:21:57.319620 CST,,,p237647,th317192736,,,,0,con4,,seg-10000,,,,,"LOG","00000","Resource manager chooses ratio 5120 MB per core as cluster level memory to core ratio, there are 2304 MB memory 6 CORE resource unable to be utilized.",,,,,,,0,,"resourcepool.c",4641,
2017-10-17 18:21:57.319668 CST,,,p237647,th317192736,,,,0,con4,,seg-10000,,,,,"LOG","00000","Resource manager adjusts segment hd4.bigdata original global resource manager resource capacity from (154368 MB, 32 CORE) to (153600 MB, 30 CORE)",,,,,,,0,,"resourcepool.c",4787,
2017-10-17 18:21:57.319716 CST,,,p237647,th317192736,,,,0,con4,,seg-10000,,,,,"LOG","00000","Resource manager adjusts segment hd1.bigdata original global resource manager resource capacity from (154368 MB, 32 CORE) to (153600 MB, 30 CORE)",,,,,,,0,,"resourcepool.c",4787,
2017-10-17 18:21:57.319762 CST,,,p237647,th317192736,,,,0,con4,,seg-10000,,,,,"LOG","00000","Resource manager adjusts segment hd2.bigdata original global resource manager resource capacity from (154368 MB, 32 CORE) to (153600 MB, 30 CORE)",,,,,,,0,,"resourcepool.c",4787,