postgresql 工具 pg_reorg
来源:互联网 发布:360网络防火墙下载 编辑:程序博客网 时间:2024/05/16 14:40
了解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 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)); test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’; test=> delete from tbl_Test1; test=> select pg_relation_size(‘tbl_test1_pkey’); test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’; test=> delete from tbl_Test1; test=> select pg_relation_size(‘tbl_test1′); 处理后回到初始大小: postgres@db-172-16-3-33-> pg_reorg –help Usage: Connection options: Generic options: Read the website for details. <http://reorg.projects.postgresql.org/> test=> select pg_relation_size(‘tbl_test1′); test=> select pg_relation_size(‘tbl_test1_pkey’); 同时可以看出,INDEX的值不变的话,怎么删除插入都不会改变INDEX的大小。
you can retrieve or update rows in tables being reorganized.
The module is developed to be a better alternative of CLUSTER and VACUUM FULL.
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)
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
892928
(1 row)
DELETE 10000
test=> select pg_relation_size(‘tbl_test1′);
pg_relation_size
——————
892928
(1 row)
pg_relation_size
——————
245760
(1 row)
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1_pkey’);
pg_relation_size
——————
245760
(1 row)
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)
pg_relation_size
——————
1335296
(1 row)
pg_reorg re-organizes a PostgreSQL database.
pg_reorg [OPTION]… [DBNAME]
Options:
-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
-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
-e, –echo echo queries
-E, –elevel=LEVEL set output message level
–help show this help, then exit
–version output version information, then exit
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.
pg_relation_size
——————
450560
(1 row)
pg_relation_size
——————
245760
(1 row)
- postgresql 工具 pg_reorg
- PostgreSQL对象重组工具【pg_reorg】
- PostgreSQL 性能测试工具
- PostgreSQL工具pg_ctl实现代码
- PostgreSQL data同步工具【pg_rewind】
- PostgreSQL 代码生成工具选择
- PostgreSQL:psql工具的使用
- PostgreSQL 代码生成工具选择
- PostgreSQL数据库集群:pgpool-II工具
- PostgreSQL数据库集群:pgpool-II工具
- PostgreSQL数据库集群:pgpool-II工具
- 关于 PostgreSQL 监控工具的学习
- PostgreSQL连接池工具PGBouncer安装
- postgresql maestro工具,插入数据字段注释
- postgreSQL
- PostgreSQL
- PostgreSQL
- postgresql
- 各种实用排序算法
- javaEE运行环境小结
- Windows防火墙之NDIS HOOK和TDI HOOK
- java 编写的最大优先级队列
- Dijkstra
- postgresql 工具 pg_reorg
- 各浏览器性能测试 - 都在测些啥?
- 时代周刊--最佳发明揭晓
- 转:c/c++数据类型转换1
- Python的print 格式化输出
- 通过get 方式向webservice 传参 获取值
- 重写memcpy()
- 《赶走你的抑郁》-斯蒂芬·伊拉迪
- Microsoft OLE DB Provider for ODBC Drivers (0x80004005) 错误