MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人，通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ～ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標，基本紀錄登入使用的範例：

2016年8月22日 星期一

EDB 功能 - Optimizer Hints

在 PostgreSQL 中要影響執行計畫，就是透過 PostgreSQL 的 GUC 參數，對 Query Planner 選用執行計畫優先順序影響。

而 PostgreSQL 企業版則提供直接影響 Query Plan 的工具 — Optimizer hints，能夠以用 Oracle 的 Optimizer Hints 工具的使用方式，去影響 Postgres Query Planner 的行為。

Optimizer hints 將 Postgres 裡面的可行的設置方式，對照成 「Oracle-Style」 的 Optimizer Hint，直接對執行計畫進行動作的開關。如果 Query Planner 對於所指令的 Hint 找不到符合的執行計畫，這個 Hint 就會被忽略。

由於 Hint 是包在註解中的，於是如果 Hint 沒有被理會，也不會跳出任何錯誤的。

使用方式，是採用多行註解進行 Hint 的指定，適用於 SELECT、INSERT、UPDATE、DELETE 等 SQL 指令：
SELECT
/*+
放置 Hint 內容
*/
col_list FROM tbl;

以下利用企業版內建範例表格 emp，作一個簡單示範：
edb=# explain SELECT /*+ FIRST_ROWS(1) */ * FROM emp ORDER BY empno;               
                            QUERY PLAN                            
---------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.14..10.85 rows=14 width=45)
(1 row)

edb=# explain SELECT /*+ FULL(emp) */ * FROM emp ORDER BY empno;
                       QUERY PLAN                       
-----------------------------------------------------------
 Sort  (cost=1.41..1.44 rows=14 width=45)
   Sort Key: empno
   ->  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=45)
(3 rows)

根據使用手冊，目前 9.5 企業版可以指定與 Oracle Optimizer Hint 相容的的有：
類型
Hint
Row Number Correction
CHOOSE
ALL_ROWS
FIRST_ROWS
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000
FIRST_ROWS(數量)
Scan Method
FULL(表格名稱)
INDEX(表格名稱 INDEX名稱)
NO_INDEX(表格名稱 INDEX名稱)
Join Method
USE_HASH(表格名稱)
NO_USE_HASH(表格名稱)
USE_MERGE(表格名稱)
NO_USE_MERGE(表格名稱)
USE_NL(表格名稱)
NO_USE_NL(表格名稱)
Join Order
ORDERED


最後，社群也有針對 postgreSQL 開發出 Hint 套件，稱為 pg_hint_plan。不過該套件和 EDB 企業版內建的 Optimizer Hints 相比較，企業版仍然想辦法對應 Oracle 提供的 Hints 種類為主；而 pg_hint_plan 則是以 postgresql.conf 設定檔內的 Planner Method Configuration 小節對應的 Planner Methode 為主（詳見 Hint 支援清單），對於 Oracle 習慣用戶來說，還是從 Postgres 企業版比較親切一些。

參考

Postgres Query Optimizer 基本觀念：Hinting at PostgreSQL | Blog - 2ndQuadrant



其他資料：


社群版相似工具： pg_hint_plan
相關資料：
於 沒有留言:
標籤：

EDB 的 Index 輔助建議功能 - Index Advisor

EDB 在企業版 EDB Postgres Advanced Server 提供協助建立 INDEX 的工具，主要以提供 B-Tree Index 建議為主，目前還不包含 PostgreSQL 提供的其他類型 INDEX（Hash Index、GiST Index、spGiST Index、GIN Index、BRIN Index）。

Index Advisor 會藉由假定暫時的 Index，對於給定的 SQL 指令，產生不同的 Query Plan，並計算個別 Cost Esitmation，找出最佳的 Index 設置組合，然後輸出建議的 CREATE INDEX 指令。

Index Advisor 只會產生建議 Index 的 SQL 指令（輸出到檔案），不會擅自建立這些 Index。

在此練習使用 Index Advisor 的指令工具 pg_advise_index 以及 psql 下的操作方式。此處使用的版本為 EPAS 9.5 版。

執行之前，要先對準備考慮加入 Index 的表格執行 ANALYZE，進行 Query Planner 要參考的採樣資訊更新，才會有比較貼近現況的建議。

要在資料庫內部啟用 Index Advisor，直接執行安裝的 Script 即可
edb=# \i /opt/PostgresPlus/9.5AS/share/contrib/index_advisor.sql
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW

弄好 Index Advisor 相關物件後，可以開始使用。

以下將以一個簡單例子作示範：
在此以用以下的表格以及插入資料
CREATE TABLE tstabcd(
id INT,
intime TIMESTAMP WITH TIME ZONE,
mystr TEXT);

INSERT INTO tstab VALUES (
generate_series(1,100000),
now(),
random()::TEXT );

上面的例子，我們可以預期，在 id 欄位上放一個 Primary Key 比較好。

接著便來看看 Index Advisor 如何使用。

Index Advisor 有兩種模式： psql 下的一個 Session 內進行追蹤，以及指令工具 pg_advise_index。

psql 下操作：要啟動 Index Advisor 進行分析、紀錄前，需要先載入 index_advisor 模組，然後於過程中，Index Advisor 便會將建議紀錄到 index_advisor_log 中，便可以用 show_index_recommandations() 或 index_recommendations 進行查看。
edb=# LOAD 'index_advisor';                                                     
LOAD
edb=# select * from tstabcd where id < 400;
id  |        mystr        |              intime            
-----+---------------------+----------------------------------
  1 | 0.887104642577469   | 22-AUG-16 07:15:32.843803 +00:00
  2 | 0.659782143775374   | 22-AUG-16 07:15:32.843803 +00:00
  3 | 0.966384563129395   | 22-AUG-16 07:15:32.843803 +00:00
  4 | 0.953097302932292   | 22-AUG-16 07:15:32.843803 +00:00
  5 | 0.120846566744149   | 22-AUG-16 07:15:32.843803 +00:00
  6 | 0.559676289092749   | 22-AUG-16 07:15:32.843803 +00:00
  7 | 0.541864807717502   | 22-AUG-16 07:15:32.843803 +00:00
  8 | 0.913167451974005   | 22-AUG-16 07:15:32.843803 +00:00
  9 | 0.864201350603253   | 22-AUG-16 07:15:32.843803 +00:00
 10 | 0.361520271748304   | 22-AUG-16 07:15:32.843803 +00:00
 11 | 0.444020337890834   | 22-AUG-16 07:15:32.843803 +00:00
 12 | 0.067021322902292   | 22-AUG-16 07:15:32.843803 +00:00
 13 | 0.340361757669598   | 22-AUG-16 07:15:32.843803 +00:00
 14 | 0.66826061764732    | 22-AUG-16 07:15:32.843803 +00:00
 15 | 0.350825355388224   | 22-AUG-16 07:15:32.843803 +00:00
 16 | 0.391068525612354   | 22-AUG-16 07:15:32.843803 +00:00
 17 | 0.441221610177308   | 22-AUG-16 07:15:32.843803 +00:00
 18 | 0.921129890717566   | 22-AUG-16 07:15:32.843803 +00:00
 19 | 0.225387694779783   | 22-AUG-16 07:15:32.843803 +00:00
 20 | 0.504998795688152   | 22-AUG-16 07:15:32.843803 +00:00
 21 | 0.368924054317176   | 22-AUG-16 07:15:32.843803 +00:00
edb=# SELECT show_index_recommendations(NULL);
                                               show_index_recommendations                                          
------------------------------------------------------------------------------------------------------------------------
 create index idx_tstabcd_id on enterprisedb.tstabcd(id);/* size: 2624 KB, benefit: 1964.68, gain: 0.748734776566668 */
(1 row)

edb=# select * from index_recommendations;
 backend_pid |                                               show_index_recommendations                                            
-------------+------------------------------------------------------------------------------------------------------------------------
        2855 | create index idx_tstabcd_id on enterprisedb.tstabcd(id);/* size: 2624 KB, benefit: 1964.68, gain: 0.748734776566668 */
(1 row)

Command-Line 下：將需要考慮的 SQL 指令放到檔案中，由 pg_advise_index 載入並將建議的 CREATE INDEX 指令寫到檔案中
[enterprisedb@edbvm ~]$ # 把需要分析的 SQL 寫到檔案裡
[enterprisedb@edbvm ~]$ echo 'select * from tstabcd where id < 400;' >> ~/test.sql
[enterprisedb@edbvm ~]$ # 接著進行分析
[enterprisedb@edbvm ~]$ pg_advise_index -p 5432 -s 100M -o ~/advise.sql ~/test.sql
poolsize = 102400 KB
load workload from file '/home/enterprisedb/test.sql'
Password:
Analyzing queries . done.
size = 2624 KB, benefit = 1964.680000
/* 1. tstabcd("id"): size=2624 KB, benefit=1964.68 */
/* Total size = 2624KB */
[enterprisedb@edbvm ~]$ # 最後查看建議內容
[enterprisedb@edbvm ~]$ cat ~/advise.sql
create index "idx_tstabcd_id" on "enterprisedb"."tstabcd" ("id");

如果認為 Index Advisor 所建議的指令可以用，就執行這些指令即可。
edb=# -- 建立所建議的 Index 之前
edb=# explain select * from tstabcd where id < 400;
                        QUERY PLAN                        
-------------------------------------------------------------
 Seq Scan on tstabcd  (cost=0.00..1986.00 rows=423 width=30)
   Filter: (id < 400)
(2 rows)
edb=# -- 建立所建議的 Index
edb=# \i ~/advise.sql
CREATE INDEX
edb=# explain select * from tstabcd where id < 400;
                                   QUERY PLAN                                  
----------------------------------------------------------------------------------
 Index Scan using idx_tstabcd_id on tstabcd  (cost=0.29..21.20 rows=423 width=30)
   Index Cond: (id < 400)
(2 rows)

以上便是 Index Advisor 的操作。

最後，Index Advisor 也有整合到 EDB 的監控/開發工具 - Postgres Enterprise Manager Client 內，詳情請參考使用手冊。以下為對 pgbench 活動分析的截圖

SQL Profiler 追蹤活動：
圖片2.png

Index Advisor GUI 對選取的追蹤活動產生建議：
圖片1.png

參考：

於 沒有留言:
標籤：

2016年8月12日 星期五

列舉 PostgreSQL 記憶體用量的一個方法

在此作個小小紀錄...

一個 PostgreSQL 程序，會有下列的 Process：

[enterprisedb@edbvm ~]$ ps aux|grep postgres
enterpr+  1235  0.0  1.9 623920 29472 ?        S    10:08   0:00 /opt/PostgresPlus/9.5AS/bin/edb-postgres -D /opt/PostgresPlus/9.5AS/data
enterpr+  1255  0.0  0.0 209936  1412 ?        Ss   10:08   0:00 postgres: logger process   
enterpr+  1257  0.0 18.0 624076 276876 ?       Ss   10:08   0:00 postgres: checkpointer process   
enterpr+  1258  0.0 12.3 623920 189224 ?       Ss   10:08   0:00 postgres: writer process   
enterpr+  1259  0.0  0.9 623920 13920 ?        Ss   10:08   0:00 postgres: wal writer process   
enterpr+  1260  0.0  0.1 624348  2796 ?        Ss   10:08   0:00 postgres: autovacuum launcher process   
enterpr+  1261  0.0  0.1 212188  1864 ?        Ss   10:08   0:00 postgres: stats collector process   
enterpr+  1274  0.0  0.6 628896 10156 ?        Ss   10:08   0:01 postgres: enterprisedb edb ::1[54154] idle
enterpr+  7206  0.0  0.0 112648   968 pts/0    R+   13:29   0:00 grep --color=auto postgres

想要觀察 PostgreSQL 這個程式「總」記憶體用量（包含它全部的 Process），可以用下式執行（測試於 CentOS 7，其他 Linux Distribution 可能要微調一下）：
[enterprisedb@edbvm ~]# while true
> do
>  ps -u enterprisedb o pid= | grep -o '[^\t ].*' | sed 's#.*#/proc/&/smaps#' | xargs sudo grep ^Pss: | awk '{A+=$2} END{print A}'
>  sleep 3
> done

就會以每三秒列一次 PostgreSQL 記憶體用量（KB）了。

至於有沒有其他方式，以後有遇到再補充。

參考：
於 沒有留言:
標籤：

2016年8月11日 星期四

pg_ident.conf 設定檔的功能練習筆記

PostgreSQL 的登入控管，是透過 $PGDATA/pg_hba.conf 內的設定控制訪問 IP 與資料庫帳號的登入認證模式。然而， PostgreSQL 還有一個 pg_ident.conf 的管理檔，也和登入控管有關。這個檔案是針對 pg_hba.conf 內認證方法為 ident 以及本機 peer 認證所使用的：設定該檔案，便能夠控制在 ident 認證（ident Authentication）以及本機 peer 認證（peer Authentication）下，作業系統帳號與資料庫帳號的登入對應。
  • 要使用 ident 認證，需要一個 Ident Server 進行控管。可以在本機安裝相關的服務。
  • 本機 peer 認證則單純透過 local socket 連線，不用額外的認證服務。但該選項只限於本機控管（pg_hba.conf 內 METHOD 的設定為 local 的條目）。此外，將 METHOD 為 local 的條目設定成 ident 認證連接的話，找不到 Ident Server 時，就會以 peer 的認證模式進行。

以下在 PostgreSQL 9.5 上進行演練：依照使用手冊所提的，Ident Server 已經比較少地方在用了，所以在此沒建立 Ident Server，僅示範 pg_ident.conf 在 peer 認證模式的狀況。

現在，有一個 OS 帳號 aaa，要賦予他在本機上，能夠以 postgres 資料庫帳號，以 peer 授權認證設定，「直接」登入 PostgreSQL 資料庫。（註：該步驟對於設置 ident 認證也是一樣的）。

首先，在 pg_ident 內
# MAPNAME       SYSTEM-USERNAME      PG-USERNAME
test            aaa                  postgres
test            enterprisedb         postgres
test2           bbb                  postgres

上面設置了兩種對照表，test 以及 test2，這名字將帳號對照分成不同組進行管理。

接著，pg_hba 相應設定
# TYPE  DATABASE     USER       ADDRESS         METHOD
local   all          all                        peer map=test
#host   all          all        127.0.0.1/32     ident map=test2
host   all          all        127.0.0.1/32     md5
#host   all          all        ::1/128          ident map=test2
host   all          all        ::1/128          md5

以上便設置本機為 peer 連線認證，並且依照 pg_ident.conf 裡面 MAPNAME 為 test 的條目進行對照。紅色部份則是設置 ident 連線認證，依照 pg_ident.conf 裡面 MAPNAME 為 test2 的條目進行對照，再這次練習中由於不會作用，於是將它們註解掉。

重新載入資料庫
[postgres@pgvm ~]$ pg_ctl reload

開 log 來看
[aaa@pgvm ~]$ psql  -U postgres -d testdb -p 5432
psql.bin (9.5.3.8)
輸入 "help" 顯示說明。

testdb=#
[postgres@pgvm ~]$ tail -f ~/9.5/data/pg_log/postgresql-2016-08-11_095327.log
...
2016-08-11 16:11:01 CST LOG:  connection received: host=[local]
2016-08-11 16:11:01 CST LOG:  connection authorized: user=postgres database=testdb

上面顯示了成功登入的狀況。
用另外的帳號 bbb 在本機嘗試登入 postgres 這個資料庫帳號
[bbb@pgvm ~]$ psql -U postgres -d testdb -p 5432
psql.bin: FATAL:  Peer authentication failed for user "postgres"

...(承上的 log)
2016-08-11 16:12:06 CST LOG:  connection received: host=[local]
2016-08-11 16:12:06 CST LOG:  no match in usermap "test" for user "postgres" authenticated as "bbb"
2016-08-11 16:12:06 CST FATAL:  Peer authentication failed for user "postgres"
2016-08-11 16:12:06 CST DETAIL:  Connection matched pg_hba.conf line 83: "local    all             all                         ident map=test"

可以看到 bbb 就算在 pg_ident.conf 有紀錄到，但由於是不同的 MAPNAME，於是就被禁止以 postgres 帳號在本機登入。
以上的練習，基本上，使用 Ident Server  也是一樣的效果。

最後一提，若是沒有設置 Ident Server 的話，把上面紅色部份啟用，aaa 帳號就會怎麼樣也連不了（除了對 pg_hba.conf 的 local 條目設定成 ident 認證仍然會有效：沒有 Ident Server 就會被當成 peer 認證）
# TYPE  DATABASE     USER       ADDRESS         METHOD
local   all          all                        peer map=test
host    all          all        127.0.0.1/32    ident map=test2
host    all          all        ::1/128         ident map=test2
[aaa@pgvm ~]$ psql -U postgres -d testdb -p 5432 -h 127.0.0.1
psql: FATAL:  Ident authentication failed for user "postgres"
2016-08-11 15:39:22 CST LOG:  connection received: host=127.0.0.1 port=42333
2016-08-11 15:39:22 CST LOG:  could not connect to Ident server at address "127.0.0.1", port 113: 連線被拒絕
2016-08-11 15:39:22 CST FATAL:  Ident authentication failed for user "postgres"
2016-08-11 15:39:22 CST DETAIL:  Connection matched pg_hba.conf line 87: "host    all             all        127.0.0.1/32           ident map=test"

上面的 log 提到沒有安裝 Ident Service，監聽不到相關的服務，造成連線失敗。



最後，對於 Ident Service，使用手冊提到，該服務只適用在封閉的子網路環境中：因為佈署在外網能存取的地方，有心人士就可能入侵 Client 機器，對 Ident 服務（port 113）進行攻擊。這大概也是為什麼 PostgreSQL 使用手冊提到這功能比較少人用的原因。


參考
於 沒有留言:
標籤：

2016年8月3日 星期三

EDB-LanguagePack 程式語言套件的 Python/Perl 模組安裝筆記

由於 EDB 提供的 Postgres Distribution （含原生版本以及企業版本）有包裝自己用的 Python/Perl/Tcl Distribution，於是要安裝想在 EDB/Postgres 用到的 PL/Python / PL/Perl / PL/Tcl 模組，就要針對資料庫程式額外安裝。

EDB-LanguagePack 在 EDB 公司提供的 Stack Builder（/Plus）套件安裝程式上可以取得並安裝。安裝後的預設路徑為 /opt/EnterpriseDB/LanguagePack/9.{4,5}/。另外，這個安裝路徑不能更動。

在此示範於 CentOS 下的 LanguagePack 安裝後的設置方式，Windows 版本請參考本文末的產品安裝手冊內容連結。
閱讀更多 »
於 沒有留言:
標籤： ,
訂閱： 文章 (Atom)