From kevink@consistentstate.com Wed Dec  24 16:37:31 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5F6EF6501F2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 12:37:31 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 40595-01-7
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 12:37:28 -0400 (AST)
X-Greylist: delayed 00:05:30.664717 by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.168])
	by mail.postgresql.org (Postfix) with ESMTP id A2C03650142
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 12:37:28 -0400 (AST)
Received: by wf-out-1314.google.com with SMTP id 25so3134457wfc.28
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 08:37:27 -0800 (PST)
Received: by 10.143.28.8 with SMTP id f8mr3653466wfj.204.1230136316698;
	Wed, 24 Dec 2008 08:31:56 -0800 (PST)
Received: from issac.consistentstate.com (c-75-71-181-91.hsd1.co.comcast.net
	[75.71.181.91])
	by mx.google.com with ESMTPS id 30sm17359824wff.32.2008.12.24.08.31.54
	(version=SSLv3 cipher=RC4-MD5); Wed, 24 Dec 2008 08:31:55 -0800 (PST)
From: Kevin Kempter <kevink@consistentstate.com>
Organization: Consistent State
To: pgsql-admin@postgresql.org
Subject: vacuum monitoring question
Date: Wed, 24 Dec 2008 09:31:53 -0700
User-Agent: KMail/1.10.3 (Linux/2.6.27.7-134.fc10.x86_64; KDE/4.1.3; x86_64; ;
	)
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="Boundary-00=_5PmUJbbWxXW56yB"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200812240931.53278.kevink@consistentstate.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200812/185
X-Sequence-Number: 31018

--Boundary-00=_5PmUJbbWxXW56yB
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi All;
I'm working on a vacuum monitor script to alert us when tables start to grow 
out of control per dead space.  Initially I excluded the system catalog 
schemas (pg_catalog, pg_toast, etc) however I wonder if maybe I should monitor 
these as well. PLus I'm looking for any input as to specifically what to look 
for - I've calculated a 'fill factor' which shows the average page fill as a 
percentage but I wonder what other metrics may help monitor possible problem 
tables effeciently..


Thoughts ?


Thanks in advance

--Boundary-00=_5PmUJbbWxXW56yB
Content-Type: text/html;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">
<html><head><meta name="qrichtext" content="1" /><style type="text/css">
p, li { white-space: pre-wrap; }
</style></head><body style=" font-family:'Sans Serif'; font-size:10pt; font-weight:400; font-style:normal;">
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Hi All;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I'm working on a vacuum monitor script to alert us when tables start to grow out of control per dead space.  Initially I excluded the system catalog schemas (pg_catalog, pg_toast, etc) however I wonder if maybe I should monitor these as well. PLus I'm looking for any input as to specifically what to look for - I've calculated a 'fill factor' which shows the average page fill as a percentage but I wonder what other metrics may help monitor possible problem tables effeciently..</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Thoughts ?</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Thanks in advance</p></body></html>
--Boundary-00=_5PmUJbbWxXW56yB--



From scott.marlowe@gmail.com Wed Dec  24 16:56:32 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id AD09264FE81
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 12:56:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 43671-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 12:56:30 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.230])
	by mail.postgresql.org (Postfix) with ESMTP id 06F8F64FD3D
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 12:56:29 -0400 (AST)
Received: by rv-out-0506.google.com with SMTP id b25so4430656rvf.43
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 08:56:29 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=5EAL9dLbugnRwk58xOQOEqrNdQ1OwTrUdrhzrI/1UBQ=;
	b=DNjGEB7AkOrDfOD6fhlMn2cLCi7X54oKrUMJvsC+AGuTXrrHC+ipx/pOHZgEKJv88l
	Zqnx7loLw+Q2PhwYOEZNyH95BRwWMFlIcKeVQwzOV+be695A6LBkLo0a4RiVtYfVDYVa
	peKQy3CO4q5AwOE0uu8sTCjw3dPHIXAAphYT0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=UqJy6M43sCx/HSkfv15D/7JGFIeF/UwlhU7soKCvy5eiAhtCjuQO/SLv+UfCnDs88d
	PH2hNiaGgMvApdStOwvfJVnDX4CvzLgh4CdD7fl+KVEoop38j6gcQSeDaEjhhELg9BxC
	ZIsieKjYjxsAIk5wUbkNmqKhWJ8tmmpS2Vdq4=
Received: by 10.114.177.1 with SMTP id z1mr5643828wae.68.1230137789124;
	Wed, 24 Dec 2008 08:56:29 -0800 (PST)
Received: by 10.114.132.17 with HTTP; Wed, 24 Dec 2008 08:56:29 -0800 (PST)
Message-ID: <dcc563d10812240856n2bd22623jae27129914db27bc@mail.gmail.com>
Date: Wed, 24 Dec 2008 09:56:29 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Kevin Kempter" <kevink@consistentstate.com>
Subject: Re: vacuum monitoring question
Cc: pgsql-admin@postgresql.org
In-Reply-To: <200812240931.53278.kevink@consistentstate.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200812240931.53278.kevink@consistentstate.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200812/186
X-Sequence-Number: 31019

On Wed, Dec 24, 2008 at 9:31 AM, Kevin Kempter
<kevink@consistentstate.com> wrote:
> Hi All;
>
> I'm working on a vacuum monitor script to alert us when tables start to grow
> out of control per dead space. Initially I excluded the system catalog
> schemas (pg_catalog, pg_toast, etc) however I wonder if maybe I should
> monitor these as well. PLus I'm looking for any input as to specifically
> what to look for - I've calculated a 'fill factor' which shows the average
> page fill as a percentage but I wonder what other metrics may help monitor
> possible problem tables effeciently..

It's a good idea to keep an eye on system catalogs, especially if you
have a fair bit of DDL going on in your usage.  I'd take a look at
what the check_postgresql.pl script does.  No need to reproduce all
that work on your own.



From kevink@consistentstate.com Wed Dec  24 19:09:55 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 65AB364FDA2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 15:09:55 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 65634-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 15:09:50 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.180])
	by mail.postgresql.org (Postfix) with ESMTP id A011F64FD3D
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 15:09:49 -0400 (AST)
Received: by wa-out-1112.google.com with SMTP id j4so1867048wah.19
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 11:09:48 -0800 (PST)
Received: by 10.114.124.1 with SMTP id w1mr5702154wac.132.1230145788190;
	Wed, 24 Dec 2008 11:09:48 -0800 (PST)
Received: from issac.consistentstate.com (c-75-71-181-91.hsd1.co.comcast.net
	[75.71.181.91])
	by mx.google.com with ESMTPS id n22sm11757803pof.2.2008.12.24.11.09.45
	(version=SSLv3 cipher=RC4-MD5); Wed, 24 Dec 2008 11:09:46 -0800 (PST)
From: Kevin Kempter <kevink@consistentstate.com>
Organization: Consistent State
To: pgsql-admin@postgresql.org
Subject: Re: vacuum monitoring question
Date: Wed, 24 Dec 2008 12:09:40 -0700
User-Agent: KMail/1.10.3 (Linux/2.6.27.7-134.fc10.x86_64; KDE/4.1.3; x86_64; ;
	)
References: <200812240931.53278.kevink@consistentstate.com>
	<dcc563d10812240856n2bd22623jae27129914db27bc@mail.gmail.com>
In-Reply-To: <dcc563d10812240856n2bd22623jae27129914db27bc@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="Boundary-00=_0joUJVqzgSDa+ax"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200812241209.40471.kevink@consistentstate.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200812/187
X-Sequence-Number: 31020

--Boundary-00=_0joUJVqzgSDa+ax
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

On Wednesday 24 December 2008 09:56:29 Scott Marlowe wrote:
> On Wed, Dec 24, 2008 at 9:31 AM, Kevin Kempter
>
> <kevink@consistentstate.com> wrote:
> > Hi All;
> >
> > I'm working on a vacuum monitor script to alert us when tables start to
> > grow out of control per dead space. Initially I excluded the system
> > catalog schemas (pg_catalog, pg_toast, etc) however I wonder if maybe I
> > should monitor these as well. PLus I'm looking for any input as to
> > specifically what to look for - I've calculated a 'fill factor' which
> > shows the average page fill as a percentage but I wonder what other
> > metrics may help monitor possible problem tables effeciently..
>
> It's a good idea to keep an eye on system catalogs, especially if you
> have a fair bit of DDL going on in your usage.  I'd take a look at
> what the check_postgresql.pl script does.  No need to reproduce all
> that work on your own.

Thanks for the feedback - where can I find the check_postgresql.pl script ?



--Boundary-00=_0joUJVqzgSDa+ax
Content-Type: text/html;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">
<html><head><meta name="qrichtext" content="1" /><style type="text/css">
p, li { white-space: pre-wrap; }
</style></head><body style=" font-family:'Sans Serif'; font-size:10pt; font-weight:400; font-style:normal;">
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">On Wednesday 24 December 2008 09:56:29 Scott Marlowe wrote:</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; On Wed, Dec 24, 2008 at 9:31 AM, Kevin Kempter</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &lt;kevink@consistentstate.com&gt; wrote:</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; Hi All;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; I'm working on a vacuum monitor script to alert us when tables start to</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; grow out of control per dead space. Initially I excluded the system</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; catalog schemas (pg_catalog, pg_toast, etc) however I wonder if maybe I</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; should monitor these as well. PLus I'm looking for any input as to</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; specifically what to look for - I've calculated a 'fill factor' which</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; shows the average page fill as a percentage but I wonder what other</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; &gt; metrics may help monitor possible problem tables effeciently..</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; It's a good idea to keep an eye on system catalogs, especially if you</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; have a fair bit of DDL going on in your usage.  I'd take a look at</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; what the check_postgresql.pl script does.  No need to reproduce all</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">&gt; that work on your own.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Thanks for the feedback - where can I find the check_postgresql.pl script ?</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"></p></body></html>
--Boundary-00=_0joUJVqzgSDa+ax--



From scott.marlowe@gmail.com Wed Dec  24 22:56:11 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0DFA36503CF
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 18:56:11 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 10096-01-4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 24 Dec 2008 18:56:07 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.236])
	by mail.postgresql.org (Postfix) with ESMTP id 0A32B65076E
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 18:49:46 -0400 (AST)
Received: by rv-out-0506.google.com with SMTP id b25so4533622rvf.43
	for <pgsql-admin@postgresql.org>; Wed, 24 Dec 2008 14:49:45 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=V1FeGSRGeEDxWnsDpNV9GFTJfAdqZgcP1Fuk8tTdOhA=;
	b=lHOmy5xpRJIU8MRQeobbkjcFCP+oRf8eVptDDZgRAVf7SUr/qDSv1XE4FUxc9iGC61
	v/UjOcxdn3SuOtAwUygCNOrBPKc7+78Q3q0GvcLFqCQYiT08mhtwRJPSxbrgJXliPi8D
	wHwFYRFl2TPE2hwgYh13aFXeZkQIOf6CG7N9g=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=uaQnP5piNDFEv0d+EnzGdHjYoixUtBBkUYGRVRAauPwzO43OQFygcSe3l5tIs8fPD9
	Q4ui/ZkvsQ4EHDhExM7JvkwOJ6m47vF/bsiFzadfq1O+6d6ZiWeLNhzr1s5aW3RpxOwy
	kRvg2ch17biDrlSj3IFmmnpR12f3h1QokCIpw=
Received: by 10.115.79.8 with SMTP id g8mr5817700wal.65.1230158985722;
	Wed, 24 Dec 2008 14:49:45 -0800 (PST)
Received: by 10.114.132.17 with HTTP; Wed, 24 Dec 2008 14:49:45 -0800 (PST)
Message-ID: <dcc563d10812241449j9e7bce9yff61eee49ef8314b@mail.gmail.com>
Date: Wed, 24 Dec 2008 15:49:45 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Kevin Kempter" <kevink@consistentstate.com>
Subject: Re: vacuum monitoring question
Cc: pgsql-admin@postgresql.org
In-Reply-To: <200812241209.40471.kevink@consistentstate.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200812240931.53278.kevink@consistentstate.com>
	<dcc563d10812240856n2bd22623jae27129914db27bc@mail.gmail.com>
	<200812241209.40471.kevink@consistentstate.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200812/188
X-Sequence-Number: 31021

On Wed, Dec 24, 2008 at 12:09 PM, Kevin Kempter
<kevink@consistentstate.com> wrote:

> Thanks for the feedback - where can I find the check_postgresql.pl script ?

http://bucardo.org/check_postgres/