Steve -- ----- Original Message ----- > From: Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> > To: Greg Williamson <gwilliamson39@xxxxxxxxx> > Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx> > Sent: Friday, September 28, 2012 8:59 AM > Subject: Re: 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
