X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9E11614B280B for ; Thu, 12 Aug 2004 11:46:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31823-02 for ; Thu, 12 Aug 2004 14:46:00 +0000 (GMT) Received: from mail.softcom.dk (unknown [217.157.23.229]) by svr1.postgresql.org (Postfix) with ESMTP id D3E4414B2804 for ; Thu, 12 Aug 2004 11:45:58 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C4807B.16DD3DD4" Subject: TOAST tables keeps growing! X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Date: Thu, 12 Aug 2004 16:46:02 +0200 Message-ID: <64F70386AE9AB64D8F36DD6FC355555E570F32@hal9000.softcom.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: TOAST tables keeps growing! Thread-Index: AcSAexbgI9cb89jTRDePdpYJuW6FgQ== From: "Thomas Madsen" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/105 X-Sequence-Number: 14500 This is a multi-part message in MIME format. ------_=_NextPart_001_01C4807B.16DD3DD4 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi. =20 I'm running a production server with PostgreSQL version 7.2.1, that does email scans. That means up to tens of thousands of entries in the database (log and quarantine) each day, and an equal amount removed every night. =20 The problem is that TOAST tables keeps springing forth and consume disk space.=20 We do a VACUUM every night, but it does not reduce or stop the TOAST table growth. =20 I searched the mailing list for answers to this problem, but found only other people describing the same problem. Namely, that their max_fsm_pages were too low and that postgresql now has lost track of the surplus TOAST data pages. =20 In our postgresql.conf we ourselves had the default value of 10000 for max_fsm_pages, which I have now raised to around a million.=20 I restarted psql and did a vacuum to see if that would reduce the disk usage, but no change was seen. =20 So the question(s) is:=20 What can I do to reclaim the wasted TOAST diskspace? Can I find and eliminate the lost TOAST tuples somehow? =20 =20 Thanks! =20 Best regards, Thomas M. Madsen. =20 =20 =20 ------_=_NextPart_001_01C4807B.16DD3DD4 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
Hi.
 
I'm = running a=20 production server with PostgreSQL version 7.2.1, that does email=20 scans.
That mean= s up to=20 tens of thousands of entries in the database (log and quarantine) each day,= and=20 an equal amount removed every night.
 
The probl= em is that=20 TOAST tables keeps springing forth and consume disk space.
We do a V= ACUUM every=20 night, but it does not reduce or stop the TOAST table=20 growth.
 
I searche= d the=20 mailing list for answers to this problem, but found only other people= =20 describing the same problem.
Namely, t= hat their=20 max_fsm_pages were too low and that postgresql now has lost track of the su= rplus=20 TOAST data pages.
 =
In our=20 postgresql.conf we ourselves had the default value of 10000 for max_fsm_pag= es,=20 which I have now raised to around a million.
I restart= ed psql and=20 did a vacuum to see if that would reduce the disk usage, but no change was= =20 seen.
 =
So the qu= estion(s)=20 is:
 &nb= sp; =20 What can I do to reclaim the wasted TOAST diskspace?
    Can I find and eliminate the lost TOAST tuples=20 somehow?
 
 
Thanks!
 
Best=20 regards,
Thomas M.= =20 Madsen.
 
 
 
=00 ------_=_NextPart_001_01C4807B.16DD3DD4--