DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=L0RoR9BkKnuUFSII9uZ6IvbYbrRgDfyKViAQ4EPId+FWmNKaSqaok3WRJAQMr3XBiAuUdRXr4fJVSuJ14U9bzM+3OHVMIearrNTSM4EnKSmF/pLXLhyDeRJMfFpogEgdG2ZBZsh8abfQgKy++EMWWWvhAR5FwJRdfYYInBO8RYI=;
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>
Message-ID: <1348872472.9854.YahooMailNeo@web125904.mail.ne1.yahoo.com>
Date: Fri, 28 Sep 2012 15:47:52 -0700 (PDT)
From: Greg Williamson <gwilliamson39@yahoo.com>
Reply-To: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: Database size stays constant but disk space keeps shrinking --
	postgres 9.1
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <5065C97C.2010707@pinpointresearch.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Steve --=0A=0A----- Original Message -----=0A> From: Steve Crawford <scrawf=
ord@pinpointresearch.com>=0A> To: Greg Williamson <gwilliamson39@yahoo.com>=
=0A> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>=0A> Sent=
: Friday, September 28, 2012 8:59 AM=0A> Subject: Re: [ADMIN] Database size=
 stays constant but disk space keeps shrinking -- postgres 9.1=0A> =0A> On =
09/27/2012 07:01 PM, Greg Williamson wrote:=0A>>  Steve (and others who rep=
lied):=0A>>  ...=0A>> =A0  The other is a slimmed-down version of our produ=
ction database, which =0A> gets=0A>>>  recreated hourly by a shell script w=
hich pulls data from remote =0A> servers, does a=0A>>>  pg_dump of the resu=
lting 3 gig database, and then drops it.=0A>>>  ...=0A> =0A> Could you expl=
ain this process in more detail? Are you creating a new database, =0A> read=
ing in data, dumping then dropping the whole database or just manipulating =
=0A> tables within an existing database?=0A>=A0=0A=0AWe 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.=0A=0AThen a sequence of psql comm=
ands 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; oc=
casionally we build a temp table and do some simple joins to get the final =
results.=0A=0AWhen all these commands are done, we pg_dump the new database=
, compress it and make a tar file, and finally issue a DROP DATABASE comman=
d.=0A=0ATakes about 20 minutes and runs once an hour.=0A=0A>>>  Have you ch=
ecked to see if there are any processes that have open =0A> handles to=0A>>=
>  deleted files (lsof -X | grep deleted). Deleted files won't show up =0A>=
 in du=0A>>>  but won't release their disk space until the process exits. P=
erhaps =0A> a script=0A>>>  or scripts, even one of your hourly ones, that =
terminate when the =0A> server=0A>>>  restarts? You could save the output o=
f lsof and ps immediately before =0A> and after=0A>>>  a restart and compar=
e them.=0A>>> =A0 =0A>>  lsof -X | grep deleted | wc -l=0A>> =0A>>  shows: =
835 such files.=0A>> =0A>>  A couple:=0A>>  postgres=A0  2540 postgres=A0  =
50u=A0 =A0 =A0 REG=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 8,3=A0 =A0  409600=A0 =A0=
 =A0 =0A> 93429 /var/lib/postgresql/9.1/main/base/2789=0A>>  200/11816 (del=
eted)=0A>>  postgres=A0  2540 postgres=A0  51u=A0 =A0 =A0 REG=A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 8,3=A0  18112512=A0 =0A> 49694570 /var/lib/postgresql/9=
.1/main/base/2789=0A>>  200/2791679 (deleted)=0A>>  <...>...=0A> I'll leave=
 it to you and Tom to puzzle over the the postgres-related open =0A> files.=
 Meanwhile, I'm a bit curious about the other 800+ and whether they =0A> ar=
e associated with scripts or processes that are connected to PostgreSQL.=0A=
>=A0=0A=0AThese all seem to be from two places -- repmgr (transient ) and t=
his stats application.=0A=0A> First, what is the output of "select * from p=
g_stat_activity;"? Are =0A> there connections you don't expect to see? If y=
ou force any of them closed =0A> (after checking with anyone who may be imp=
acted), do you see any file handles =0A> released or disk-space freed?=0A> =
=0A=0A=0ANothing unexpected, lots of IDLE connections (20-30, depending). W=
e just tried a round of closing half of the stat application connections an=
d it didn't seem to make a big difference.=0A=0A=0A> Second, do any of the =
processes associated with the other open-but-deleted files =0A> relate to p=
rograms or scripts that connect to PostgreSQL? Next time you do a =0A> rest=
art, do any of the processes exit or do any of the deleted files get closed=
?=0A>=A0=0A=0Arepmgr 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.=0A=0AEverythng else connects via pgbouncer, so we are also wonder=
ing if the tcp_keepalive we added might be hurting us.=0A=0A> I'm wondering=
 if you have processes that connect to PostgreSQL which =0A> terminate and =
release their file-handles when PG is restarted.=0A> =0A> Cheers,=0A> Steve=
=0A> =0A=0A=0AThanks for questions and the time -- still digging into this.=
=0A=0AGreg