Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9a23885

Browse files
committed
Add pgstattuple
1 parent95d4821 commit9a23885

File tree

7 files changed

+280
-1
lines changed

7 files changed

+280
-1
lines changed

‎contrib/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.28 2001/09/29 03:11:58 momjian Exp $
1+
# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.29 2001/10/01 01:52:38 ishii Exp $
22

33
subdir = contrib
44
top_builddir = ..
@@ -27,6 +27,7 @@ WANTED_DIRS = \
2727
pg_resetxlog\
2828
pgbench\
2929
pgcrypto\
30+
pgstattuple\
3031
rserv\
3132
rtree_gist\
3233
seg\

‎contrib/README

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,10 @@ pgcrypto -
137137
Cryptographic functions
138138
by Marko Kreen <marko@l-t.ee>
139139

140+
pgstattuple -
141+
A function returns the percentage of "dead" tuples in a table
142+
by Tatsuo Ishii <t-ishii@sra.co.jp>
143+
140144
retep -
141145
tools to build retep tools packages
142146
by Peter T Mount <peter@retep.org.uk>

‎contrib/pgstattuple/Makefile

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
#-------------------------------------------------------------------------
2+
#
3+
# pgstattuple Makefile
4+
#
5+
# $Id: Makefile,v 1.1 2001/10/01 01:52:38 ishii Exp $
6+
#
7+
#-------------------------------------------------------------------------
8+
9+
subdir = contrib/pgstattuple
10+
top_builddir = ../..
11+
include$(top_builddir)/src/Makefile.global
12+
13+
MODULE_big:= pgstattuple
14+
SRCS+= pgstattuple.c
15+
OBJS:=$(SRCS:.c=.o)
16+
DOCS:= README.pgstattuple README.pgstattuple.euc_jp
17+
DATA_built:= pgstattuple.sql
18+
19+
PG_CPPFLAGS:=
20+
SHLIB_LINK :=
21+
22+
include$(top_srcdir)/contrib/contrib-global.mk
Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
pgstattuple README2001/10/01 Tatsuo Ishii
2+
3+
1. What is pgstattuple?
4+
5+
pgstattuple returns the percentage of the "dead" tuples of a
6+
table. This will help users to judge if vacuum is needed.
7+
8+
In addition, pgstattuple prints more detailed information using
9+
NOTICE.
10+
11+
test=# select pgstattuple('tellers');
12+
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%
13+
pgstattuple
14+
-------------
15+
18.75
16+
(1 row)
17+
18+
Above example shows tellers tables includes 18.75% dead tuples.
19+
20+
physical lengthphysical size of the table in MB
21+
live tuplesinformation on the live tuples
22+
dead tuplesinformation on the dead tuples
23+
free/reusable spaceavailable space
24+
overheadoverhead space
25+
26+
2. Installing pgstattuple
27+
28+
$ make
29+
$ make install
30+
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
31+
32+
3. Using pgstattuple
33+
34+
pgstattuple can be called as a function:
35+
36+
pgstattuple(NAME) RETURNS FLOAT8
37+
38+
The argument is the table name. pgstattuple returns the percentage
39+
of the "dead" tuples of a table.
40+
41+
4. Notes
42+
43+
pgstattuple does not lock the target table at all. So concurrent
44+
update may affect the result.
45+
46+
pgstattuple judges a tuple is "dead" if HeapTupleSatisfiesNow()
47+
returns false.
Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
$Id: README.pgstattuple.euc_jp,v 1.1 2001/10/01 01:52:38 ishii Exp $
2+
3+
pgstattuple README2001/10/01 �а�ã��
4+
5+
1. pgstattuple�Ȥ�
6+
7+
pgstattuple�ϡ�UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭����
8+
�ơ��֥뼫�Τ�ʪ��Ū���礭�����Ф���ѡ�����ơ������ֵѤ��ޤ�����
9+
�ޤꡤ�ֵ��ͤ��礭����С�����������ߤ�¿���Τǡ�vacuum�򤫤���ɬ
10+
�פ�����Ȥ���Ƚ�Ǥν����ˤʤ�櫓�Ǥ���
11+
12+
��������ǤϾ����̤����ʤ��Τǡ�NOTICE��å������Ǥ�����ʾ�����
13+
���Ǥ˽��Ϥ��ޤ���
14+
15+
test=# select pgstattuple('tellers');
16+
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%
17+
pgstattuple
18+
-------------
19+
18.75
20+
(1 row)
21+
22+
���μ¹���Ǥϡ�19%�ۤɤ����ߤˤʤäƤ��뤳�Ȥ��Ǥ��ޤ���NOTICE���
23+
�������θ�����񤤤Ƥ����ޤ���
24+
25+
physical length�ơ��֥��ʪ��Ū�ʥ�������MBñ�̤�ɽ��
26+
live tuples���ߤǤϤʤ����ץ�˴ؤ�����󡥥��ץ������
27+
���ץ뤬�����ΰ�ι�ס��ơ��֥����Τ��Ф���
28+
��Ψ��ɽ�����ޤ���
29+
dead tuples���ߤˤʤä����ץ�˴ؤ������
30+
free/reusable space���Ѳ�ǽ��̤�����ΰ������Ѳ�ǽ�ΰ�
31+
overhead�����Τ�����ΰ褬�ơ��֥����Τ�������Ψ
32+
33+
2. pgstattuple�Υ��󥹥ȡ���
34+
35+
PostgreSQL��/usr/local/pgsql�˥��󥹥ȡ���ѤǤ��ꡤtest�ǡ����١�
36+
����pgstattuple�򥤥󥹥ȡ��뤹����μ��򼨤��ޤ���
37+
38+
$ make
39+
$ make install
40+
41+
�桼������ؿ�����Ͽ���ޤ���
42+
43+
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
44+
45+
46+
3. pgstattuple�λȤ���
47+
48+
pgstattuple�θƤӽФ������ϰʲ��Ǥ���
49+
50+
pgstattuple(NAME) RETURNS FLOAT8
51+
52+
������: �ơ��֥�̾
53+
54+
�ؿ�������UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭���ǡ�
55+
�ơ��֥��ʪ��Ū���礭�����Ф�����(�ѡ������)���ֵѤ��ޤ���
56+
57+
�ʤ���pgstattuple�ϥơ��֥�˰��ڥ��å��򤫤��ʤ��Τǡ�pgstattuple
58+
��¹���˳����ơ��֥�˹���������ȯ������ȡ��������ʤ���̤���
59+
����ǽ��������ޤ���
60+
61+
4. pgstattuple�Υ饤���󥹾��ˤĤ���
62+
63+
pgstattuple.c����Ƭ�˽񤤤Ƥ����̤�Ǥ����ޤ���pgstattuple �ϴ�����̵��
64+
�ڤǤ���pgstattuple ����Ѥ������Ȥˤ�ä������뤤���ʤ��̤˴ؤ���
65+
����Ǥ���餤�ޤ���
66+
67+
5. ��������
68+
69+
2001/10/01 PostgreSQL 7.2 ��contrib module����Ͽ
70+
2001/08/30 pgstattuple �С������ 0.1��꡼��

‎contrib/pgstattuple/pgstattuple.c

Lines changed: 131 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,131 @@
1+
/*
2+
* $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.1 2001/10/01 01:52:38 ishii Exp $
3+
*
4+
* Copyright (c) 2001 Tatsuo Ishii
5+
*
6+
* Permission to use, copy, modify, and distribute this software and
7+
* its documentation for any purpose, without fee, and without a
8+
* written agreement is hereby granted, provided that the above
9+
* copyright notice and this paragraph and the following two
10+
* paragraphs appear in all copies.
11+
*
12+
* IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
13+
* INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
14+
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
15+
* DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
16+
* OF THE POSSIBILITY OF SUCH DAMAGE.
17+
*
18+
* THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
19+
* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
20+
* A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
21+
* IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
22+
* SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
23+
*/
24+
25+
#include"postgres.h"
26+
#include"fmgr.h"
27+
#include"access/heapam.h"
28+
#include"access/transam.h"
29+
30+
PG_FUNCTION_INFO_V1(pgstattuple);
31+
32+
externDatumpgstattuple(PG_FUNCTION_ARGS);
33+
34+
/* ----------
35+
* pgstattuple:
36+
* returns the percentage of dead tuples
37+
*
38+
* C FUNCTION definition
39+
* pgstattuple(NAME) returns FLOAT8
40+
* ----------
41+
*/
42+
Datum
43+
pgstattuple(PG_FUNCTION_ARGS)
44+
{
45+
Namep=PG_GETARG_NAME(0);
46+
47+
Relationrel;
48+
HeapScanDescscan;
49+
HeapTupletuple;
50+
BlockNumbernblocks;
51+
BlockNumberblock=InvalidBlockNumber;
52+
doubletable_len;
53+
uint64tuple_len=0;
54+
uint64dead_tuple_len=0;
55+
uint32tuple_count=0;
56+
uint32dead_tuple_count=0;
57+
doubletuple_percent;
58+
doubledead_tuple_percent;
59+
60+
Bufferbuffer=InvalidBuffer;
61+
uint64free_space=0;/* free/reusable space in bytes */
62+
doublefree_percent;/* free/reusable space in % */
63+
64+
rel=heap_openr(NameStr(*p),NoLock);
65+
nblocks=RelationGetNumberOfBlocks(rel);
66+
scan=heap_beginscan(rel, false,SnapshotAny,0,NULL);
67+
68+
while ((tuple=heap_getnext(scan,0)))
69+
{
70+
if (HeapTupleSatisfiesNow(tuple->t_data))
71+
{
72+
tuple_len+=tuple->t_len;
73+
tuple_count++;
74+
}
75+
else
76+
{
77+
dead_tuple_len+=tuple->t_len;
78+
dead_tuple_count++;
79+
}
80+
81+
if (!BlockNumberIsValid(block)||
82+
block!=BlockIdGetBlockNumber(&tuple->t_self.ip_blkid))
83+
{
84+
block=BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
85+
buffer=ReadBuffer(rel,block);
86+
free_space+=PageGetFreeSpace((Page)BufferGetPage(buffer));
87+
ReleaseBuffer(buffer);
88+
}
89+
}
90+
heap_endscan(scan);
91+
heap_close(rel,NoLock);
92+
93+
table_len= (double)nblocks*BLCKSZ;
94+
95+
if (nblocks==0)
96+
{
97+
tuple_percent=0.0;
98+
dead_tuple_percent=0.0;
99+
free_percent=0.0;
100+
}
101+
else
102+
{
103+
tuple_percent= (double)tuple_len*100.0/table_len;
104+
dead_tuple_percent= (double)dead_tuple_len*100.0/table_len;
105+
free_percent= (double)free_space*100.0/table_len;
106+
}
107+
108+
elog(NOTICE,"physical length: %.2fMB live tuples: %u (%.2fMB, %.2f%%) dead tuples: %u (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",
109+
110+
table_len/1024/1024,/* phsical length in MB */
111+
112+
tuple_count,/* number of live tuples */
113+
(double)tuple_len/1024/1024,/* live tuples in MB */
114+
tuple_percent,/* live tuples in % */
115+
116+
dead_tuple_count,/* number of dead tuples */
117+
(double)dead_tuple_len/1024/1024,/* dead tuples in MB */
118+
dead_tuple_percent,/* dead tuples in % */
119+
120+
(double)free_space/1024/1024,/* free/available space in MB */
121+
122+
free_percent,/* free/available space in % */
123+
124+
/* overhead in % */
125+
(nblocks==0)?0.0:100.0
126+
-tuple_percent
127+
-dead_tuple_percent
128+
-free_percent);
129+
130+
PG_RETURN_FLOAT8(dead_tuple_percent);
131+
}
Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
DROP FUNCTION pgstattuple(NAME);
2+
CREATE FUNCTION pgstattuple(NAME) RETURNS FLOAT8
3+
AS 'MODULE_PATHNAME', 'pgstattuple'
4+
LANGUAGE 'c' WITH (isstrict);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp