diff options
| -rw-r--r-- | contrib/Makefile | 3 | ||||
| -rw-r--r-- | contrib/README | 4 | ||||
| -rw-r--r-- | contrib/pgstattuple/Makefile | 22 | ||||
| -rw-r--r-- | contrib/pgstattuple/README.pgstattuple | 47 | ||||
| -rw-r--r-- | contrib/pgstattuple/README.pgstattuple.euc_jp | 70 | ||||
| -rw-r--r-- | contrib/pgstattuple/pgstattuple.c | 131 | ||||
| -rw-r--r-- | contrib/pgstattuple/pgstattuple.sql.in | 4 |
7 files changed, 280 insertions, 1 deletions
diff --git a/contrib/Makefile b/contrib/Makefile index b83762fec65..1de0581e728 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -1,4 +1,4 @@ -# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.28 2001/09/29 03:11:58 momjian Exp $ +# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.29 2001/10/01 01:52:38 ishii Exp $ subdir = contrib top_builddir = .. @@ -27,6 +27,7 @@ WANTED_DIRS = \ pg_resetxlog \ pgbench \ pgcrypto \ + pgstattuple \ rserv \ rtree_gist \ seg \ diff --git a/contrib/README b/contrib/README index 54792b736e0..d58fbf8669e 100644 --- a/contrib/README +++ b/contrib/README @@ -137,6 +137,10 @@ pgcrypto - Cryptographic functions by Marko Kreen <marko@l-t.ee> +pgstattuple - + A function returns the percentage of "dead" tuples in a table + by Tatsuo Ishii <t-ishii@sra.co.jp> + retep - tools to build retep tools packages by Peter T Mount <peter@retep.org.uk> diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile new file mode 100644 index 00000000000..88f1b658d41 --- /dev/null +++ b/contrib/pgstattuple/Makefile @@ -0,0 +1,22 @@ +#------------------------------------------------------------------------- +# +# pgstattuple Makefile +# +# $Id: Makefile,v 1.1 2001/10/01 01:52:38 ishii Exp $ +# +#------------------------------------------------------------------------- + +subdir = contrib/pgstattuple +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global + +MODULE_big := pgstattuple +SRCS += pgstattuple.c +OBJS := $(SRCS:.c=.o) +DOCS := README.pgstattuple README.pgstattuple.euc_jp +DATA_built := pgstattuple.sql + +PG_CPPFLAGS := +SHLIB_LINK := + +include $(top_srcdir)/contrib/contrib-global.mk diff --git a/contrib/pgstattuple/README.pgstattuple b/contrib/pgstattuple/README.pgstattuple new file mode 100644 index 00000000000..c54f97e82f5 --- /dev/null +++ b/contrib/pgstattuple/README.pgstattuple @@ -0,0 +1,47 @@ +pgstattuple README 2001/10/01 Tatsuo Ishii + +1. What is pgstattuple? + + pgstattuple returns the percentage of the "dead" tuples of a + table. This will help users to judge if vacuum is needed. + + In addition, pgstattuple prints more detailed information using + NOTICE. + +test=# select pgstattuple('tellers'); +NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 320 (0.01MB, 18.75%) free/reusable space: 0.01MB (18.06%) overhead: 62.02% + pgstattuple +------------- + 18.75 +(1 row) + + Above example shows tellers tables includes 18.75% dead tuples. + + physical length physical size of the table in MB + live tuples information on the live tuples + dead tuples information on the dead tuples + free/reusable space available space + overhead overhead space + +2. Installing pgstattuple + + $ make + $ make install + $ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test + +3. Using pgstattuple + + pgstattuple can be called as a function: + + pgstattuple(NAME) RETURNS FLOAT8 + + The argument is the table name. pgstattuple returns the percentage + of the "dead" tuples of a table. + +4. Notes + + pgstattuple does not lock the target table at all. So concurrent + update may affect the result. + + pgstattuple judges a tuple is "dead" if HeapTupleSatisfiesNow() + returns false. diff --git a/contrib/pgstattuple/README.pgstattuple.euc_jp b/contrib/pgstattuple/README.pgstattuple.euc_jp new file mode 100644 index 00000000000..d23b995dd47 --- /dev/null +++ b/contrib/pgstattuple/README.pgstattuple.euc_jp @@ -0,0 +1,70 @@ +$Id: README.pgstattuple.euc_jp,v 1.1 2001/10/01 01:52:38 ishii Exp $ + +pgstattuple README 2001/10/01 石井達夫 + +1. pgstattupleとは + + pgstattupleは，UPDATEやDELETEで作られたテーブルのゴミ領域の大きさを， + テーブル自体の物理的な大きさに対するパーセンテージで返却します．つ + まり，返却値が大きければ，それだけゴミも多いので，vacuumをかける必 + 要があるという判断の助けになるわけです． + + これだけでは情報量が少ないので，NOTICEメッセージでいろんな情報をつ + いでに出力します． + +test=# select pgstattuple('tellers'); +NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 320 (0.01MB, 18.75%) free/reusable space: 0.01MB (18.06%) overhead: 62.02% + pgstattuple +------------- + 18.75 +(1 row) + + この実行例では，19%ほどがゴミになっていることが伺えます．NOTICEメッ + セージの見方も書いておきます． + + physical length テーブルの物理的なサイズをMB単位で表示 + live tuples ゴミではないタプルに関する情報．タプル数，各 + タプルが占める領域の合計，テーブル全体に対する + 比率を表示します． + dead tuples ゴミになったタプルに関する情報． + free/reusable space 利用可能な未使用領域や再利用可能領域 + overhead 管理のための領域がテーブル全体に占める比率 + +2. pgstattupleのインストール + + PostgreSQLが/usr/local/pgsqlにインストール済であり，testデータベー + スにpgstattupleをインストールする場合の手順を示します． + + $ make + $ make install + + ユーザ定義関数を登録します． + + $ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test + + +3. pgstattupleの使い方 + + pgstattupleの呼び出し形式は以下です． + + pgstattuple(NAME) RETURNS FLOAT8 + + 第一引数: テーブル名 + + 関数の戻りはUPDATEやDELETEで作られたテーブルのゴミ領域の大きさで， + テーブルの物理的な大きさに対する割合(パーセント)で返却します． + + なお，pgstattupleはテーブルに一切ロックをかけないので，pgstattuple + を実行中に該当テーブルに更新や削除が発生すると，正しくない結果を返 + す可能性があります． + +4. pgstattupleのライセンス条件について + + pgstattuple.cの冒頭に書いてある通りです．また，pgstattuple は完全に無保 + 証です．pgstattuple を使用したことによって生じるいかなる結果に関して + も責任を負いません． + +5. 改訂履歴 + + 2001/10/01 PostgreSQL 7.2 用contrib moduleに登録 + 2001/08/30 pgstattuple バージョン 0.1リリース diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c new file mode 100644 index 00000000000..e88be905011 --- /dev/null +++ b/contrib/pgstattuple/pgstattuple.c @@ -0,0 +1,131 @@ +/* + * $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.1 2001/10/01 01:52:38 ishii Exp $ + * + * Copyright (c) 2001 Tatsuo Ishii + * + * Permission to use, copy, modify, and distribute this software and + * its documentation for any purpose, without fee, and without a + * written agreement is hereby granted, provided that the above + * copyright notice and this paragraph and the following two + * paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED + * OF THE POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + */ + +#include "postgres.h" +#include "fmgr.h" +#include "access/heapam.h" +#include "access/transam.h" + +PG_FUNCTION_INFO_V1(pgstattuple); + +extern Datum pgstattuple(PG_FUNCTION_ARGS); + +/* ---------- + * pgstattuple: + * returns the percentage of dead tuples + * + * C FUNCTION definition + * pgstattuple(NAME) returns FLOAT8 + * ---------- + */ +Datum +pgstattuple(PG_FUNCTION_ARGS) +{ + Name p = PG_GETARG_NAME(0); + + Relation rel; + HeapScanDesc scan; + HeapTuple tuple; + BlockNumber nblocks; + BlockNumber block = InvalidBlockNumber; + double table_len; + uint64 tuple_len = 0; + uint64 dead_tuple_len = 0; + uint32 tuple_count = 0; + uint32 dead_tuple_count = 0; + double tuple_percent; + double dead_tuple_percent; + + Buffer buffer = InvalidBuffer; + uint64 free_space = 0; /* free/reusable space in bytes */ + double free_percent; /* free/reusable space in % */ + + rel = heap_openr(NameStr(*p), NoLock); + nblocks = RelationGetNumberOfBlocks(rel); + scan = heap_beginscan(rel, false, SnapshotAny, 0, NULL); + + while ((tuple = heap_getnext(scan,0))) + { + if (HeapTupleSatisfiesNow(tuple->t_data)) + { + tuple_len += tuple->t_len; + tuple_count++; + } + else + { + dead_tuple_len += tuple->t_len; + dead_tuple_count++; + } + + if (!BlockNumberIsValid(block) || + block != BlockIdGetBlockNumber(&tuple->t_self.ip_blkid)) + { + block = BlockIdGetBlockNumber(&tuple->t_self.ip_blkid); + buffer = ReadBuffer(rel, block); + free_space += PageGetFreeSpace((Page)BufferGetPage(buffer)); + ReleaseBuffer(buffer); + } + } + heap_endscan(scan); + heap_close(rel, NoLock); + + table_len = (double)nblocks*BLCKSZ; + + if (nblocks == 0) + { + tuple_percent = 0.0; + dead_tuple_percent = 0.0; + free_percent = 0.0; + } + else + { + tuple_percent = (double)tuple_len*100.0/table_len; + dead_tuple_percent = (double)dead_tuple_len*100.0/table_len; + free_percent = (double)free_space*100.0/table_len; + } + + elog(NOTICE,"physical length: %.2fMB live tuples: %u (%.2fMB, %.2f%%) dead tuples: %u (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%", + + table_len/1024/1024, /* phsical length in MB */ + + tuple_count, /* number of live tuples */ + (double)tuple_len/1024/1024, /* live tuples in MB */ + tuple_percent, /* live tuples in % */ + + dead_tuple_count, /* number of dead tuples */ + (double)dead_tuple_len/1024/1024, /* dead tuples in MB */ + dead_tuple_percent, /* dead tuples in % */ + + (double)free_space/1024/1024, /* free/available space in MB */ + + free_percent, /* free/available space in % */ + + /* overhead in % */ + (nblocks == 0)?0.0: 100.0 + - tuple_percent + - dead_tuple_percent + - free_percent); + + PG_RETURN_FLOAT8(dead_tuple_percent); +} diff --git a/contrib/pgstattuple/pgstattuple.sql.in b/contrib/pgstattuple/pgstattuple.sql.in new file mode 100644 index 00000000000..7a7706fa190 --- /dev/null +++ b/contrib/pgstattuple/pgstattuple.sql.in @@ -0,0 +1,4 @@ +DROP FUNCTION pgstattuple(NAME); +CREATE FUNCTION pgstattuple(NAME) RETURNS FLOAT8 +AS 'MODULE_PATHNAME', 'pgstattuple' +LANGUAGE 'c' WITH (isstrict); |