From: Greg Williamson <gwilliamson39@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Date: Thu, 27 Sep 2012 19:23:54 -0700 (PDT)
Message-ID: <1348799034.68119.YahooMailNeo@web125902.mail.ne1.yahoo.com> (raw)
In-Reply-To: <10417.1348798479@sss.pgh.pa.us>
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
<5064D7BC.1000709@pinpointresearch.com>
<1348797684.2846.YahooMailNeo@web125904.mail.ne1.yahoo.com>
<10417.1348798479@sss.pgh.pa.us>
Tom --
----- Original Message -----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Greg Williamson <gwilliamson39@yahoo.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Sent: Thursday, September 27, 2012 7:14 PM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
>
>G reg Williamson <gwilliamson39@yahoo.com> writes:
>>> Have you checked to see if there are any processes that have open
> handles to
>>> deleted files (lsof -X | grep deleted).
>
>> lsof -X | grep deleted | wc -l
>
>> shows: 835 such files.
>
>> A couple:
>> postgres 2540 postgres 50u REG 8,3 409600
> 93429 /var/lib/postgresql/9.1/main/base/2789
>> 200/11816 (deleted)
>> postgres 2540 postgres 51u REG 8,3 18112512
> 49694570 /var/lib/postgresql/9.1/main/base/2789
>> 200/2791679 (deleted)
>> <...>
>
> So, which processes are holding these open, and what are they doing
> exactly? Let's see output from ps and pg_stat_activity, maybe even
> attach to them with gdb and get stack traces.
>
>> We've a planned restart scheduled soon which will let me find any
>> scripts that might be keeping things open,
>
> A restart will destroy all the evidence, so let's not be in a hurry
> to do that before we've identified what's happening.
>
> regards, tom lane
>
Thanks for the suggestions -- I'll post back when I have more info. Many of these do not seem to have a link to any identifiable process that is still running, but some do and they have pointed me away from the hourly drop / rebuild, at least for now. Looks like the stats database may be the issue.
Greg W.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gwilliamson39@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
In-Reply-To: <1348799034.68119.YahooMailNeo@web125902.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox