转载:PostgreSQL的小技巧
转载:http://tech.it168.com/o/2007-12-22/200712221049762.shtml 【IT168 技术文档】本人使用Oracle和PostgreSQL数年,一直试图将Oracle中许多先进的功能在PostgreSQL中使用,所以也在这方面一直比较注意,下面先整理出3点,以后会慢慢完善。 (1)和Oracle类似的dblink功能 使用过oracle的人都知道,oracle有个很先进的功能叫:dblink,能够在一个数据库中操作另外一个远程的数据库,比如:一个数据库在中国北京,另外一台数据库在中国上海,我可以在北京这台数据库上面建立一个到上海数据库的dblink,然后可以在北京这台数据库上面对上海的数据库进行query或者update或者delete。这个先进的功能在PostgreSQL的原代码的:contribdblink中已经有了,大家可以像这样将他编译并安装到我们的数据库中。
假设我们的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一条记录: # select dblink_exec(
insert into student values('linux_prog','12345')
); dblink_exec
-------------------
INSERT
22516276
(
现在我们检索我们刚才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 $
;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
---------+---------------------------------------------------
insert into access_log select
*
from access_log ; (
我们可以看到他正在进行的SQL语句,然后我们就可以对这些SQL进行性能的优化。
如果,如果是一条select语句,执行速度狂慢的话,我们可以用explain来看看他的execute plan,看是否有合适的index或者是否是某个table很久没有analyze过了,等等。 另外,可以提供一个KILL一个session的脚本,比如:有个session占用的资源太多,如果不kill掉他的话,可能会导致系统DOWN机。 killsession.sh: #
sh ################################################ # Author:linux_prog # # use to kill one session. # ################################################
; then echo
fi SID
$
echo
select pg_cancel_backend($SID);
psql 比如:我执行: [pgsql@webtrends bin]$ .
killsession.sh
pg_cancel_backend
刚才那个很占资源的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; 0M 我们先可以写个脚本来进行这样的操作(留给大家自己完成)。 以上内容都是比较实用的东东,大家如果能够深刻理解的话,一定能够在日常的数据库管理中发挥不可小看的工作。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |