PostgreSQL的小技巧 dblink(转)

PostgreSQL的小技巧(dblink,SQL administrator,dbsize)
                        --linux_prog at 2005-04-24

        本人使用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
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
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的记录：
pgsql=# select * from dblink('select * from student') as student(name varchar(100),pass varchar(100));
    name    | pass  
------------+-------
linux_prog | 12345
(1 row)

怎么样？刚才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)

我们先可以写个脚本来进行这样的操作（留给大家自己完成）。



以上内容都是比较实用的东东，大家如果能够深刻理解的话，一定能够在日常的数据库管理中发挥不可小看的工作。