使用pg_reorg在线重新整理表
使用pg_reorg在线重新整理表了解PostgreSQL存储原理的话，应该知道PostgreSQL在大量的更新删除插入操作后。会出现垃圾空间，索引也会变得比正常的情况大一些，直接导致的后果是，BUFFER空间浪费，IO请求增加，数据库性能下降。
一般通过vacuum来回收这些垃圾空间。但是这些还不够，为了达到更好的性能，一般需要重建表或者把表按顺序重新处理存储。通常reindex和cluster 在更新频繁的大表上操作的话会引起锁等待的问题。
下面的话介绍一下使用pg_reorg组建，解决锁等待的问题，由于作者给出的说明文档实在太少了，所以我稍微写一下，方便大家使用：
来看看作者是怎么说的（不需要加载任何锁，太好了.有点类似ORACLE的在线重定义）：
pg_reorg can re-organize tables on a postgres database without any locks so that
you can retrieve or update rows in tables being reorganized.
The module is developed to be a better alternative of CLUSTER and VACUUM FULL.
1. 下载
到pgfoundry下载源代码
http://pgfoundry.org/frs/download.php/2745/pg_reorg-1.1.3.tar.gz
2. 安装
安装的套路其实很简单，加载到源码中编译即可。
解压后拷贝源代码目录到$PG_SOURCE/contrib
su – postgres
cd $PG_SOURCE/contrib/pg_reorg-1.1.3
USE_PGXS=1 gmake
su – root
. /home/postgres/.bash_profile (can find pg_config)
cd $PG_SOURCE/contrib/pg_reorg-1.1.3
USE_PGXS=1 gmake install
3. 加载
su – postgres
cd $PG_HOME/share/contrib
psql yourdb superuser -f ./pg_reorg.sql
4. 使用范例
要使用pg_reorg vacuum full或cluster表。表必须有主键。
如：
test=> create table tbl_test1 (id int primary key,name varchar(10));
NOTICE:CREATE TABLE / PRIMARY KEY will create implicit index “tbl_test1_pkey” for table “tbl_test1″
CREATE TABLE
test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> delete from tbl_Test1;
DELETE 10000
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
450560
(1 row)
test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
892928
(1 row)
test=> delete from tbl_Test1;
DELETE 10000
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
892928
(1 row)
test=> select pg_relation_size(‘tbl_test1_pkey’);
pg_relation_size
——————
245760
(1 row)
test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1_pkey’);
pg_relation_size
——————
245760
(1 row)
test=> delete from tbl_Test1;
DELETE 10000
test=> insert into tbl_test1 (id,name) select generate_series(10001,20000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1_pkey’);
pg_relation_size
——————
466944
(1 row)
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
1335296
(1 row)
处理后回到初始大小：
postgres@db-172-16-3-33-> pg_reorg –help
pg_reorg re-organizes a PostgreSQL database.
Usage:
pg_reorg …
Opti**:
-a, –all reorg all databases
-t, –table=TABLE reorg specific table only
-n, –no-order do vacuum full instead of cluster
-o, –order-by=columns order by columns instead of cluster keys
-T, –wait-timeout=secs timeout to cancel other backends on conflict.
-Z, –no-analyze don’t analyze at end
Connection opti**:
-d, –dbname=DBNAME database to connect
-h, –host=HOSTNAME database server host or socket directory
-p, –port=PORT database server port
-U, –username=USERNAME user name to connect as
-w, –no-password never prompt for password
-W, –password force password prompt
Generic opti**:
-e, –echo echo queries
-E, –elevel=LEVEL set output message level
–help show this help, then exit
–version output version information, then exit
Read the website for details. <http://reorg.projects.postgresql.org/>
Report bugs to <reorg-general@lists.pgfoundry.org>.
postgres@db-172-16-3-33-> pg_reorg -t test.tbl_test1 -o id -d test -U postgres
postgres@db-172-16-3-33-> psql test test
psql (9.0beta2)
Type “help” for help.
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
450560
(1 row)
test=> select pg_relation_size(‘tbl_test1_pkey’);
pg_relation_size
——————
245760
(1 row)
同时可以看出，INDEX的值不变的话，怎么删除插入都不会改变INDEX的大小。
页:
[1]