POSTGResql的小技巧(by
使用Oracle和POSTGResql都有一段时间，一直试图将Oracle中许多先进的功能在PostgreSQl@H_403_4@中使用，所以也在这方面一直比较注意，下面先整理出3点，以后会慢慢完善。
（1）和Oracle类似的dblink功能@H_403_4@使用过Oracle的人都知道，Oracle有个很先进的功能叫：dblink,能够在一个数据库中操作另外@H_403_4@一个远程的数据库，比如：一个数据库在中国北京，另外一台数据库在中国上海，我可以在北京@H_403_4@这台数据库上面建立一个到上海数据库的dblink，然后可以在北京这台数据库上面对上海的数据库@H_403_4@进行query或者@R_450_9531@e或者delete。这个先进功能其实在PostgreSQl的源代码：contrib/dblink@H_403_4@中已经有了，大家可以像这样将他编译并安装到我们的数据库中。@H_403_4@#cdcontrib/dblink@H_403_4@#make@H_403_4@#makeinstall
假设我们的POSTGResql安装在：/home/pgsql中。@H_403_4@makeinstall后，在/home/pgsql/lib/中会有一个：dblink.so文件。这就是使用dblink必须的@H_403_4@函数文件。另外，在/home/pgsql/share/contrib中会有一个dblink.sql文件，这就是安装dblink.so@H_403_4@的函数所需要的sql语句。@H_403_4@大家可以像这样安装dblink的所有函数：@H_403_4@#catdblink.sql|psql@H_403_4@[pgsql@webtrendscontrib]$catdblink.sql|psql@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATETYPE@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@CREATEFUNCTION@H_403_4@[pgsql@webtrendscontrib]$@H_403_4@说明我们的函数安装成功。@H_403_4@下面可以使用dblink的所有先进功能了。@H_403_4@大家可以先看看dblink.sql中的一些函数申明，让我们更了解他的作用。
下面进入psql:@H_403_4@pgsql=#SELEctdblink_connect(’host=localhostuser=pgsqlpassword=’);@H_403_4@dblink_connect@H_403_4@—————-@H_403_4@OK@H_403_4@(1row)@H_403_4@这个函数用来建立到远程数据库的连接。
我们可以像这样想远程的数据库中insert一条记录：@H_403_4@pgsql=#SELEctdblink_exec(’inserTintostudentvalues(/’linux_prog/’,/’12345/’)');@H_403_4@dblink_exec@H_403_4@——————-@H_403_4@INSERT225162761@H_403_4@(1row)
现在我们检索我们刚才insert的记录：@H_403_4@pgsql=#SELEct*fromdblink(’SELEct*fromstudent’)asstudent(namevarchar(100),passvarchar(100));@H_403_4@name|pass@H_403_4@————+——-@H_403_4@linux_prog|12345@H_403_4@(1row)
怎么样？刚才insert的记录已经在里面了。
dblink的功能非常强大，我上面列举的只是他的最简单的应用。大家可以参考POSTGResql的sourcecode下面：@H_403_4@contrib/dblink/sql/dblink.sql仔细看一下。
(2)找出系统中性能很差的sql，并加以优化@H_403_4@我们在做Oracle系统管理的时候，经常做的事情是：@H_403_4@首先看看系统中哪几条sql的性能最差，通过linux命令：top-c找出该最前面的几个Oracle进程的PID，然后在Oracle的相关vIEw@H_403_4@中将这些sql找出来，然后去看看这些sql的executeplan,然后进行相关的优化。@H_403_4@POSTGResql也提供了这样先进的功能。@H_403_4@首先，在PostgreSQl.conf中把stats_command_String=true打开，使POSTGResql的statscollectorprocess监控每个session@H_403_4@的sql语句。@H_403_4@编写相关的脚本：@H_403_4@vIEwsql.sh:@H_403_4@#!/bin/sh
######################################################@H_403_4@#vIEwsql.sh#@H_403_4@#Author:linux_prog#@H_403_4@#usetoshowallactivesession’ssqlinPOSTGResql.#@H_403_4@######################################################@H_403_4@iftest-z$1;then@H_403_4@echo”Usage:$0pID”@H_403_4@exit10@H_403_4@fi
echo”SELEct*from(SELECTpg_stat_geT_Backend_pID(s.BACkendID)AS@R_262_5338@,pg_stat_geT_Backend_activity(s.BACkendID)AScurrent_queryFROM(SELECTpg_stat_geT_Backend_IDset()ASBACkendID)ASs)asqueryStringwhere@R_262_5338@=$1;”|psql
这个脚本是显示指定的pID的session目前正在执行的sql语句。@H_403_4@比如：@H_403_4@我用top-c,结果是：@H_403_4@3665pgsql150124M124M122MR30.02.10:04POSTGRes:pgsqlpgsql[local]INSERT
可以看到：3665这个pID显示在第一条，说明它的sql可能效率比较低。@H_403_4@[pgsql@webtrendsbin]$./vIEwsql.sh3665@H_403_4@@R_262_5338@|current_query@H_403_4@———+—————————————————@H_403_4@3665|inserTintoaccess_logSELEct*fromaccess_log;@H_403_4@(1row)
我们可以看到他正在进行的sql语句，然后我们就可以对这些sql进行性能的优化。@H_403_4@如果，如果是一条SELEct语句，执行速度狂慢的话，我们可以用explain来看看他的executeplan,@H_403_4@看是否有合适的index或者是否是某个table很久没有analyze过了，等等。
另外，可以提供一个KILL一个session的脚本，比如：有个session占用的资源太多，如果不kill掉他的话，可能会导致系统DOWN机.@H_403_4@killsession.sh:@H_403_4@#!/bin/sh@H_403_4@################################################@H_403_4@#Author:linux_prog#@H_403_4@#usetokillonesession.#@H_403_4@################################################@H_403_4@iftest-z$1;then@H_403_4@echo”Usage:$0pID”@H_403_4@exit10@H_403_4@fi
SID=$1@H_403_4@echo”SELEctpg_cancel_BACkend($SID);”|psql
比如：我执行：@H_403_4@[pgsql@webtrendsbin]$./killsession.sh3665@H_403_4@pg_cancel_BACkend@H_403_4@——————-@H_403_4@1@H_403_4@(1row)
刚才那个很占资源的session的目前的sql操作就被cancel掉了。@H_403_4@在3665的psql中会显示：@H_403_4@pgsql=#inserTintoaccess_logSELEct*fromaccess_log;@H_403_4@ERROR:cancelingqueryduetouserrequest@H_403_4@(3)清楚的知道每个table或者index的大小@H_403_4@每一个DBA都应该知道，IO的瓶颈是所有数据库性能的瓶颈。所以我们在设计表结构的时候，一定要尽量的减少每个字段的大小，@H_403_4@只有这样，table的size才会尽量的小。@H_403_4@还有，我们在进行sql调整的时候，首先做的，肯定是对大的table的perfoRMANCETurnning。@H_403_4@因此，我们很清楚的知道每个table或者index所占用的磁盘大小是很有必要的，在Oracle中可以直接访问dba_segments这个vIEw@H_403_4@来知道每个table或者INDEX的大小。@H_403_4@POSTGResql的contrib/dbsize中也有这样的一个模块。@H_403_4@大家可以像上面安装dblink那样安装dbsize.so。@H_403_4@像这样查看table:access_log的大小：@H_403_4@pgsql=#SELEctrelation_size(’access_log’)/1024/1024||’M'asdbsize;@H_403_4@dbsize@H_403_4@——–@H_403_4@332M@H_403_4@(1row)
像这样查看index:test_IDx的大小：@H_403_4@pgsql=#SELEctrelation_size(’test_IDx’)/1024/1024||’M'asdbsize;@H_403_4@dbsize@H_403_4@——–@H_403_4@0M@H_403_4@(1row)
我们先可以写个脚本来进行这样的操作（留给大家自己完成）。
以上内容都是比较实用的东东，大家如果能够深刻理解的话，一定能够在日常的数据库管理中发挥不可小看的工作。