From jdrouard@ankama.com Fri Apr  14 13:34:41 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (mx2.hub.org [200.46.204.254])
	by postgresql.org (Postfix) with ESMTP id 48B7011F61C2
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 14 Apr 2006 10:34:41 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: 1.386
X-Spam-Level: *
X-Spam-Status: No, score=1.386 required=5 tests=[BAYES_50=0.001,
	HTML_MESSAGE=0.001, SPF_SOFTFAIL=1.384]
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024)
	with ESMTP id OHdgCtW6DIHq
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 14 Apr 2006 10:34:25 -0300 (ADT)
X-Greylist: delayed 00:19:15.843752 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id CA82711F61E3
	for <pgsql-performance@postgresql.org>;
	Fri, 14 Apr 2006 10:34:22 -0300 (ADT)
Received: from ankama.com (unknown [193.238.151.58])
	by svr4.postgresql.org (Postfix) with ESMTP id 586BF5AF93C
	for <pgsql-performance@postgresql.org>;
	Fri, 14 Apr 2006 13:15:07 +0000 (GMT)
Received: from rogers (rsa59-2-82-233-195-195.fbx.proxad.net [82.233.195.195])
	by ankama.com (ankama.com) (MDaemon.PRO.v7.2.3.R)
	with ESMTP id md50000792057.msg
	for <pgsql-performance@postgresql.org>; Fri, 14 Apr 2006 15:31:44 +0200
Message-ID: <000c01c65fc5$41c78a70$0700a8c0@ankama.lan>
From: "Julien Drouard" <jdrouard@ankama.com>
To: <pgsql-performance@postgresql.org>
Subject: pg_toast size
Date: Fri, 14 Apr 2006 15:13:43 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0009_01C65FD6.04E78680"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Spam-Processed: ankama.com, Fri, 14 Apr 2006 15:31:44 +0200
	(not processed: message from valid local sender)
X-MDRemoteIP: 82.233.195.195
X-Return-Path: jdrouard@ankama.com
X-MDaemon-Deliver-To: pgsql-performance@postgresql.org
X-Archive-Number: 200604/331
X-Sequence-Number: 18415

This is a multi-part message in MIME format.

------=_NextPart_000_0009_01C65FD6.04E78680
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi everyone,

I've seen my pg_toast tables are becoming bigger and bigger. After =
googling I would like to modify my max_fsm_pages parameter to prevent =
that kind of problem. So I'm wondering if changing this parameter is =
enough and after that how can I reduce the size of these tables? By =
doing a full vacuum?

Thanks in advance,

Julien
------=_NextPart_000_0009_01C65FD6.04E78680
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi everyone,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I've seen my pg_toast tables are =
becoming bigger=20
and bigger. After googling I would like to modify my max_fsm_pages =
parameter to=20
prevent that kind of problem. So I'm wondering if changing this =
parameter is=20
enough and after that how can I reduce the size of these tables? By =
doing a full=20
vacuum?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Julien</FONT></DIV></BODY></HTML>

------=_NextPart_000_0009_01C65FD6.04E78680--





From jnasby@pervasive.com Tue Apr  18 21:34:48 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (mx2.hub.org [200.46.204.254])
	by postgresql.org (Postfix) with ESMTP id D233111F7742
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 18 Apr 2006 18:34:48 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: -2.353
X-Spam-Level: 
X-Spam-Status: No, score=-2.353 required=5 tests=[AWL=0.246, BAYES_00=-2.599]
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024)
	with ESMTP id RlpkpT9LgFYR
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 18 Apr 2006 18:34:46 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id 3226111F644E
	for <pgsql-performance@postgresql.org>;
	Tue, 18 Apr 2006 18:34:44 -0300 (ADT)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 708FD56423; Tue, 18 Apr 2006 16:34:44 -0500 (CDT)
Received: (hashcash-sendmail, from uid 1001); Tue, 18 Apr 2006 16:34:44 -0500
Date: Tue, 18 Apr 2006 16:34:44 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Julien Drouard <jdrouard@ankama.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_toast size
Message-ID: <20060418213443.GJ49405@pervasive.com>
References: <000c01c65fc5$41c78a70$0700a8c0@ankama.lan>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000c01c65fc5$41c78a70$0700a8c0@ankama.lan>
X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Hashcash: 
 1:20:060418:jdrouard@ankama.com::JEHOqVa4tkBOjtao:000000000000000000
	0000000000000000000000003JJp
X-Hashcash: 
 1:20:060418:pgsql-performance@postgresql.org::O5TYjhqyv7qHUf2s:00000
	0000000000000000000000000rjz
X-Archive-Number: 200604/399
X-Sequence-Number: 18483

On Fri, Apr 14, 2006 at 03:13:43PM +0200, Julien Drouard wrote:
> Hi everyone,
> 
> I've seen my pg_toast tables are becoming bigger and bigger. After googling I would like to modify my max_fsm_pages parameter to prevent that kind of problem. So I'm wondering if changing this parameter is enough and after that how can I reduce the size of these tables? By doing a full vacuum?

A full vacuum would do it. CLUSTERing the table might rewrite the toast
tables as well.

As for toast, if you do a vacuum verbose over the entire cluster, it
will tell you at the end how much space you need in the FSM. See also
http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10087
and http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10116
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461