Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application.
On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune <bradley.mcc...@noaa.gov>wrote: > The only transactions present were "<IDLE>" for current_query. I even > stopped the remote services, restarted the PostgreSQL server (assumingly, > there should be no transactions occurring now), and performed another > VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 > pgsql version. > > > On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe > <scott.marl...@gmail.com>wrote: > >> Did you have a long running trasnaction? Especially a prepared >> transaction, blocking the vacuum from reclaiming the space? >> >> On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune <bradley.mcc...@noaa.gov> >> wrote: >> > David, >> > >> > (As a preface, I have already gone forward with completely rebuilding >> the >> > database which seems to have finally fixed the problem. Rebuilding the >> > table itself had no effect, and I couldn't wait much longer to move >> > forward.) >> > >> > Yes, this seems similar, however, the key difference being that VACUUM >> FULL >> > did not alleviate the problem. The extra "bloated" disk space was still >> > considered "in use" by the data server, and so it was never returned to >> the >> > system. I have a suspicion that the server was storing the table data >> in >> > pages in an inefficient manner (by unknown means) because we had >> roughly ~5x >> > the number of pages used on that TOAST table to store the same number of >> > tuples compared to other similar databases. >> > >> > Depending on how often you have to use VACUUM FULL, you might want to >> > consider tweaking the autovacuum to be more aggressive on that hot >> table to >> > keep it in check more often. (Recycling the disk space more efficiently >> > rather than sending it back to the server only to be reallocated to the >> > database again.) >> > >> > >> > On Fri, Jul 12, 2013 at 4:09 AM, David Welton <dav...@dedasys.com> >> wrote: >> >> >> >> Hi, >> >> >> >> I have a very similar problem... details below. >> >> >> >> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.til...@noaa.gov> >> wrote: >> >> > Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in >> order >> >> > to >> >> > take advantage of autovacuum features. This server exists in a very >> >> > closed >> >> > environment (isolated network, limited root privileges; this explains >> >> > the >> >> > older software in use) and runs on RHEL5.5 (i686). After the upgrade, >> >> > the >> >> > database has constantly been growing to the tune of 5-6 GB a day. >> >> > Normally, >> >> > the database, as a whole, is ~20GB; currently, it is ~89GB. We have a >> >> > couple >> >> > other servers which run equivalent databases and actually synchronize >> >> > the >> >> > records to each other via a 3rd party application (one I do not have >> >> > access >> >> > to the inner workings). The other databases are ~20GB as they should >> be. >> >> >> >> Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit >> >> system: >> >> >> >> PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc >> >> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit >> >> >> >> > Running the following SQL, it's fairly obvious there's an issue with >> a >> >> > particular table, and, more specifically, its TOAST table. >> >> >> >> Same thing here: we have a table with around 2-3 megs of data that is >> >> blowing up to *10 gigs*. >> >> >> >> > This TOAST table is for a table called "timeseries" which saves large >> >> > records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the >> >> > records >> >> > in timeseries yields ~16GB for that column. There should be [b]no >> >> > reason[/b] >> >> > this table's TOAST table should be as large as it is. >> >> >> >> Similar situation: it's a bytea column that gets "a lot" of updates; >> >> in the order of 10's of thousands a day. >> >> >> >> > I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the >> vacuum >> >> > runs >> >> > to completion with no errors. >> >> >> >> VACUUM FULL fixes the problem for us by recouping all the wasted disk >> >> space. I don't have the knowledge to investigate much further on my >> >> own, but I'd be happy to try out a few things. The database is, >> >> unfortunately, sensitive data that I can't share, but I could probably >> >> script a similar situation... >> >> >> >> -- >> >> David N. Welton >> >> >> >> http://www.dedasys.com/ >> > >> > >> > >> > >> > -- >> > Bradley D. J. McCune >> >> >> >> -- >> To understand recursion, one must first understand recursion. >> > > > > -- > Bradley D. J. McCune > NOAA/OCWWS/HSD > Community Hydrologic Prediction System - Support > CHPS FogBugz Administrator > Office phone: (301) 713-1625 x160 > > > -- To understand recursion, one must first understand recursion.