Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BF468632B02 for ; Thu, 6 May 2010 05:49:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20396-03 for ; Thu, 6 May 2010 08:49:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 09851632A51 for ; Thu, 6 May 2010 05:49:40 -0300 (ADT) Received: by bwz8 with SMTP id 8so3336501bwz.3 for ; Thu, 06 May 2010 01:49:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=CHDaDmIeiIGQsfwxPeJwvqqW7I+ygVzFYi0ZsB6BnoE=; b=Di9JU1/4/xDixYmh1SIwbAs1Z1Ic/PiO4AwpAq6zuK3ybEIxblP0jXSfNRXERVJ+iy inznSD/tXu9Su2BELlOXug1teFhdyiK6XvW/P6HvTCDpgpMBv5bP3yGFEfzAdovKQKw6 xQb4As/yTp8aobJaXhlobj+EjJ62lQf8yHUN8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=pZMrgRcjNX8xLHtUZPrCMDsXK/j5gFtxOApaOJwx1NgJC1Qfd5u3v6bH/AR4cEYzXw tIZhkRX3qV3t9haW2zfll/hCTzbvNtNHU9QA+c90WVRFoDDDhmXGVNyVBDap2ZcbqKno mXg+ubscgtFa2UXIvfLlTWHTDljqoAe4bC+Xs= Received: by 10.204.10.15 with SMTP id n15mr5929030bkn.158.1273135779258; Thu, 06 May 2010 01:49:39 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.54.198 with HTTP; Thu, 6 May 2010 01:49:19 -0700 (PDT) In-Reply-To: References: <20100505114210.GD16616@miranda> <4BE1336A02000025000312CE@gw.wicourts.gov> From: Ian Lea Date: Thu, 6 May 2010 09:49:19 +0100 Message-ID: Subject: Re: Table space grow big - PostgreSQL To: Khangelani Gama Cc: "pgsql-admin@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201005/78 X-Sequence-Number: 34551 I'm still confused about the disk space, even more so now that more servers and figures of 1Gb and 15Gb have joined the party. Presumably this: > 3.6M ./1 > 3.6M ./16975 > 51G ./95186722 > 4.8M ./4830693 > 51G . is the output from some $ du command. And is this > -rw------- 1 postgres postgres 16K May 5 12:50 219436402 > -rw------- 1 postgres postgres 16K May 5 12:50 219436401 > -rw------- 1 postgres postgres 16K May 5 12:50 219436400 > -rw------- 1 postgres postgres 16K May 5 12:50 219436399 > -rw------- 1 postgres postgres 34M May 5 12:50 219436274 > -rw------- 1 postgres postgres 42M May 5 12:50 219436273 > -rw------- 1 postgres postgres 43M May 5 12:50 219436272 > -rw------- 1 postgres postgres 42M May 5 12:50 219436271 > -rw------- 1 postgres postgres 34M May 5 12:50 219436270 > -rw------- 1 postgres postgres 42M May 5 12:50 219436269 > -rw------- 1 postgres postgres 42M May 5 12:50 219436268 > -rw------- 1 postgres postgres 51M May 5 12:50 219436267 > -rw------- 1 postgres postgres 34M May 5 12:50 219436266 > -rw------- 1 postgres postgres 51M May 5 12:50 219436265 > -rw------- 1 postgres postgres 15M May 5 12:50 218478745 > -rw------- 1 postgres postgres 11M May 5 12:50 218478744 > -rw------- 1 postgres postgres 10M May 5 12:50 218478743 > -rw------- 1 postgres postgres 13M May 5 12:50 218478742 > -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1 the output from some $ ls command on /usr/local/pgsql/data/base/95186722? You say there is nothing else in that directory, but those sizes don't add up to 51Gb so something is messed up somewhere. Maybe this is nothing to do with postgres and a reboot and/or fsck of the disk will fix everything. -- Ian. On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama wrote: > Thanks again for the replies: > > > Responding to Kevin, Ian, and Inigo: > > > > 1. We'll try an change the order in the script > 2. Daily vacuum analyze was disabled, which was running from a different = script, we'll try to put it back. > 3. The monthly script does work. > 4. In my original email about the directory that takes more space: > > /usr/local/pgsql/data/base directory shows the following where 95186722/ = takes a lot of space =A0: > > 3.6M =A0 =A0./1 > 3.6M =A0 =A0./16975 > 51G =A0 =A0 ./95186722 > 4.8M =A0 =A0./4830693 > 51G =A0 =A0 . > > > > There is nothing else in /usr/local/pgsql/data/base/95186722/ =A0director= y except the table space names with different spaces for each one of them. = And in other servers these table space sizes are now at 1.0G, for an exampl= e there is about 15 of them which makes about 15G. > > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943= 6402 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943= 6401 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943= 6400 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943= 6399 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943= 6274 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943= 6273 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A043M May =A05 12:50 21943= 6272 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943= 6271 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943= 6270 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943= 6269 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943= 6268 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A051M May =A05 12:50 21943= 6267 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943= 6266 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A051M May =A05 12:50 21943= 6265 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A015M May =A05 12:50 21847= 8745 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A011M May =A05 12:50 21847= 8744 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A010M May =A05 12:50 21847= 8743 > -rw------- =A0 =A01 postgres postgres =A0 =A0 =A013M May =A05 12:50 21847= 8742 > -rw------- =A0 =A01 postgres postgres =A0 =A0 440M May =A05 12:50 2160819= 69.1 > > > > > > > > > > > > > -----Original Message----- > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] > Sent: Wednesday, May 05, 2010 3:59 PM > To: pgsql-admin@postgresql.org; Khangelani Gama > Subject: Re: [ADMIN] Table space grow big - PostgreSQL > > Khangelani Gama wrote: > >> There is a script that runs once a month > > Most likely that should be daily, or at least weekly. > >> "REINDEX TABLE ${table}" >> "VACUUM FULL VERBOSE ${table}" >> "VACUUM ANALYZE ${table}" > > That's the wrong order. =A0Try: > > "VACUUM FULL VERBOSE ANALYZE ${table}" > "REINDEX TABLE ${table}" > > With your current order, the VACUUM FULL bloats the indexes you've > just rebuilt. > > If you vacuum frequently enough, you should not need to use the FULL > option. > >> Dumping and restoring the database doesn't decrease the space > > Now, that's odd. =A0You're not restoring back into the same database > without dropping it first (using the "clean" option), are you? > Perhaps you have some very wide indexes, or a very large number of > small tables? > >> There is nothing set in the postgresql.conf file that has to do >> with vacuum analyze. > > Well, that wasn't the only thing I would look for; however, I'm not > sure how many of the things I usually check exist in 7.3 or work the > same way. =A0:-( > > -Kevin > > The contents of and attachments to this e-mail are intended for the addre= ssee only, and may contain the confidential information of UCS Group and/or= its subsidiaries. =A0Any review, use or dissemination thereof by anyone ot= her than the intended addressee is prohibited. =A0If you are not the intend= ed addressee please notify the writer immediately and destroy the e-mail. = =A0UCS Group Limited and its subsidiaries distance themselves from and acce= pt no liability for unauthorised use of their e-mail facilities or e-mails = sent other than strictly for business purposes. > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >