From: Greg Williamson <gwilliamson39@yahoo.com>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Date: Fri, 28 Sep 2012 15:47:52 -0700 (PDT)
Message-ID: <1348872472.9854.YahooMailNeo@web125904.mail.ne1.yahoo.com> (raw)
In-Reply-To: <5065C97C.2010707@pinpointresearch.com>
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
<5064D7BC.1000709@pinpointresearch.com>
<1348797684.2846.YahooMailNeo@web125904.mail.ne1.yahoo.com>
<5065C97C.2010707@pinpointresearch.com>
Steve --
----- Original Message -----
> From: Steve Crawford <scrawford@pinpointresearch.com>
> To: Greg Williamson <gwilliamson39@yahoo.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Sent: Friday, September 28, 2012 8:59 AM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
>
> On 09/27/2012 07:01 PM, Greg Williamson wrote:
>> Steve (and others who replied):
>> ...
>> The other is a slimmed-down version of our production database, which
> gets
>>> recreated hourly by a shell script which pulls data from remote
> servers, does a
>>> pg_dump of the resulting 3 gig database, and then drops it.
>>> ...
>
> Could you explain this process in more detail? Are you creating a new database,
> reading in data, dumping then dropping the whole database or just manipulating
> tables within an existing database?
>
We create the database, and then use shell scripts to get a pg_dump -s of a production server and use that to create tables, indexes, etc.
Then a sequence of psql commands retrieves data from production, usually a subset of whatever table is being grabbed based on a slimmed down set of userids of interest, recency, etc. Mostly these get loaded as is i nto the tables in the new database; occasionally we build a temp table and do some simple joins to get the final results.
When all these commands are done, we pg_dump the new database, compress it and make a tar file, and finally issue a DROP DATABASE command.
Takes about 20 minutes and runs once an hour.
>>> Have you checked to see if there are any processes that have open
> handles to
>>> deleted files (lsof -X | grep deleted). Deleted files won't show up
> in du
>>> but won't release their disk space until the process exits. Perhaps
> a script
>>> or scripts, even one of your hourly ones, that terminate when the
> server
>>> restarts? You could save the output of lsof and ps immediately before
> and after
>>> a restart and compare them.
>>>
>> 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)
>> <...>...
> I'll leave it to you and Tom to puzzle over the the postgres-related open
> files. Meanwhile, I'm a bit curious about the other 800+ and whether they
> are associated with scripts or processes that are connected to PostgreSQL.
>
These all seem to be from two places -- repmgr (transient ) and this stats application.
> First, what is the output of "select * from pg_stat_activity;"? Are
> there connections you don't expect to see? If you force any of them closed
> (after checking with anyone who may be impacted), do you see any file handles
> released or disk-space freed?
>
Nothing unexpected, lots of IDLE connections (20-30, depending). We just tried a round of closing half of the stat application connections and it didn't seem to make a big difference.
> Second, do any of the processes associated with the other open-but-deleted files
> relate to programs or scripts that connect to PostgreSQL? Next time you do a
> restart, do any of the processes exit or do any of the deleted files get closed?
>
repmgr is now also a suspect, although from what I can see it keeps things in that state for nly a short while. But we are investigating further.
Everythng else connects via pgbouncer, so we are also wondering if the tcp_keepalive we added might be hurting us.
> I'm wondering if you have processes that connect to PostgreSQL which
> terminate and release their file-handles when PG is restarted.
>
> Cheers,
> Steve
>
Thanks for questions and the time -- still digging into this.
Greg
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, scrawford@pinpointresearch.com
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
In-Reply-To: <1348872472.9854.YahooMailNeo@web125904.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