From 9a23885f7243cddff2cba06a4d245ab54b1ff81e Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Mon, 1 Oct 2001 01:52:38 +0000 Subject: [PATCH] Add pgstattuple --- contrib/Makefile | 3 +- contrib/README | 4 + contrib/pgstattuple/Makefile | 22 +++++ contrib/pgstattuple/README.pgstattuple | 47 +++++++++ contrib/pgstattuple/README.pgstattuple.euc_jp | 70 ++++++++++++++ contrib/pgstattuple/pgstattuple.c | 131 ++++++++++++++++++++++++++ contrib/pgstattuple/pgstattuple.sql.in | 4 + 7 files changed, 280 insertions(+), 1 deletion(-) create mode 100644 contrib/pgstattuple/Makefile create mode 100644 contrib/pgstattuple/README.pgstattuple create mode 100644 contrib/pgstattuple/README.pgstattuple.euc_jp create mode 100644 contrib/pgstattuple/pgstattuple.c create mode 100644 contrib/pgstattuple/pgstattuple.sql.in diff --git a/contrib/Makefile b/contrib/Makefile index b83762fec6..1de0581e72 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 54792b736e..d58fbf8669 100644 --- a/contrib/README +++ b/contrib/README @@ -137,6 +137,10 @@ pgcrypto - Cryptographic functions by Marko Kreen +pgstattuple - + A function returns the percentage of "dead" tuples in a table + by Tatsuo Ishii + retep - tools to build retep tools packages by Peter T Mount diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile new file mode 100644 index 0000000000..88f1b658d4 --- /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 0000000000..c54f97e82f --- /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 0000000000..d23b995dd4 --- /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 0000000000..e88be90501 --- /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 0000000000..7a7706fa19 --- /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); -- 2.11.0