PostgreSQL的小技巧

本人使用Oracle和PostgreSQL數年，一直試圖將Oracle中許多先進的功能在PostgreSQL中使用，所以也在這方面一直比較注意，下面先整理出3點，以後會慢慢完善。

　　(1)和Oracle類似的dblink功能

　　使用過oracle的人都知道，oracle有個很先進的功能叫：dblink,能夠在一個資料庫中操作另外一個遠端資料庫，比如：一個資料 庫在中國北京，另外一台資料庫在中國上海，我可以在北京這台資料庫上面建立一個到上海資料庫的dblink，然後可以在北京這台資料庫上面對上海的資料庫 進行query或者update或者delete。這個先進的功能在PostgreSQL的原代碼的：contrib\dblink中已經有了，大家可以 像這樣將他編譯並安裝到我們的資料庫中。

　　#cd contrib/dblink
　　#make
　　#make install

　　假設我們的postgresql安裝在：/home/pgsql中。

　　make install後，在/home/pgsql/lib/中會有一個：dblink.so檔案。這就是使用dblink必須的函數檔案。另外，在/home /pgsql/share/contrib中會有一個dblink.sql檔案，這就是安裝dblink.so的函數所需要的sql語句。

　　大家可以像這樣安裝dblink的所有函數：

　　#cat dblink.sql|psql
　　[pgsql@webtrends contrib]$ cat dblink.sql |psql
　　CREATE FUNCTION
　　[pgsql@webtrends contrib]$

　　說明我們的函數安裝成功。

　　下面可以使用dblink的所有先進功能了。

　　大家可以先看看dblink.sql中的一些函數申明，讓我們更瞭解他的作用。

　　下面進入psql:

　　pgsql=# select dblink_connect('host=localhost user=pgsql password=');
　　dblink_connect
　　----------------
　　OK
　　(1 row)

　　這個函數用來建立到遠端資料庫的串連。

　　我們可以像這樣想遠端資料庫中insert一條記錄：

　　pgsql=# select dblink_exec('insert into student values(\'linux_prog\',\'12345\')');
　　dblink_exec
　　-------------------
　　INSERT 22516276 1
　　(1 row)

　　現在我們檢索我們剛才insert的記錄：怎麼樣?剛才insert的記錄已經在裡面了。

　　dblink的功能非常強大，我上面列舉的只是他的最簡單的應用。大家可以參考PostgreSQL的source code下面：

　　contrib/dblink/sql/dblink.sql仔細看一下。

　　(2)找出系統中效能很差的SQL，並加以最佳化

　　我們在做Oracle系統管理的時候，經常做的事情是：

　　首先看看系統中哪幾條SQL的效能最差，通過linux命令：top -c找出該最前面的幾個oracle進程的PID，然後在oracle的相關view

　　中將這些SQL找出來，然後去看看這些SQL的execute plan,然後進行相關的最佳化。

　　PostgreSQL也提供了這樣先進的功能。

　　首先，在postgresql.conf中把stats_command_string = true開啟，使PostgreSQL的stats collector process監控每個session的sql語句。

　　編寫相關的指令碼：

　　viewsql.sh:
　　#!/bin/sh
　　######################################################
　　# viewsql.sh #
　　# Author:linux_prog #
　　# use to show all active session's sql in PostgreSQL.#

　　######################################################
　　if test -z $1 ;then
　　echo "Usage:$0 pid"
　　exit 10
　　fi
　　echo "select * from (SELECT pg_stat_get_backend_pid(s.backendid) AS 
procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM 
(SELECT pg_stat_get_backend_idset() AS backendid) AS s) as querystring 
where procpid=$1;" | psql

　　這個指令碼是顯示指定的pid的session目前正在執行的sql語句。

　　比如：

　　我用top -c,結果是：

　　3665 pgsql 15 0 124M 124M 122M R 30.0 2.1 0:04 postgres: pgsql pgsql [local] INSERT

　　可以看到：3665這個pid顯示在第一條，說明它的sql可能效率比較低。

　　[pgsql@webtrends bin]$ ./viewsql.sh 3665
　　procpid | current_query
　　---------+---------------------------------------------------
　　3665 | insert into access_log select * from access_log ;
　　(1 row)

　　我們可以看到他進行中的SQL語句，然後我們就可以對這些SQL進行效能的最佳化。

　　如果，如果是一條select語句，執行速度狂慢的話，我們可以用explain來看看他的execute plan,看是否有合適的index或者是否是某個table很久沒有analyze過了，等等。

　　另外，可以提供一個KILL一個session的指令碼，比如：有個session佔用的資源太多，如果不kill掉他的話，可能會導致系統DOWN機。

　　killsession.sh:
　　#!/bin/sh
　　################################################
　　# Author:linux_prog #
　　# use to kill one session. #
　　################################################
　　if test -z $1; then
　　echo "Usage:$0 pid"
　　exit 10
　　fi
　　SID=$1
　　echo "select pg_cancel_backend($SID);"|psql
　　比如：我執行：
　　[pgsql@webtrends bin]$ ./killsession.sh 3665
　　pg_cancel_backend
　　-------------------
　　1
　　(1 row)

　　剛才那個很佔資源的session的目前的SQL操作就被cancel掉了。

　　在3665的psql中會顯示：

　　pgsql=# insert into access_log select * from access_log ;

　　ERROR: canceling query due to user request

　　(3)清楚的知道每個table或者index的大小

　　每一個DBA都應該知道，IO的瓶頸是所有資料庫效能的瓶頸。所以我們在設計表結構的時候，一定要盡量的減少每個欄位的大小，只有這樣，table的size才會盡量的小。

　　還有，我們在進行SQL調整的時候，首先做的，肯定是對大的TABLE的performance turnning。

　　因此，我們很清楚的知道每個table或者index所佔用的磁碟大小是很有必要的，在oracle中可以直接存取dba_segments這個view來知道每個TABLE或者INDEX的大小。

　　PostgreSQL的contrib/dbsize中也有這樣的一個模組。

　　大家可以像上面安裝dblink那樣安裝dbsize.so。

　　像這樣查看table:access_log的大小：

　　pgsql=# select relation_size('access_log')/1024/1024 ||'M' as dbsize;

　　dbsize

　　--------

　　332M

　　(1 row)

　　像這樣查看index:test_idx的大小：

　　pgsql=# select relation_size('test_idx')/1024/1024 ||'M' as dbsize;

　　dbsize

　　--------

　　0M

　　(1 row)

　　我們先可以寫個指令碼來進行這樣的操作(留給大家自己完成)。

　　以上內容都是比較實用的東東，大家如果能夠深刻理解的話，一定能夠在日常的資料庫管理中發揮不可小看的工作。