Well, the issue was corrected by completely rebuilding the database a few days ago (all the way to reinitializing the database directory). With that said, I did check that table at the time, and I received an empty result set from such a SELECT statement. The same goes for max_prepared_transactions.
Perplexing. On Fri, Jul 12, 2013 at 4:35 PM, Scott Marlowe <scott.marl...@gmail.com>wrote: > So what id > select * from pg_prepared_xacts ; > show? > > > On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune > <bradley.mcc...@noaa.gov>wrote: > >> Scott, >> >> Purely idle. I compared these transactions with our other "healthy" >> databases, and they checked out. >> >> >> On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe >> <scott.marl...@gmail.com>wrote: >> >>> Prepared transactions that are sitting still do the same thing, and show >>> no connections. >>> >>> >>> On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe >>> <scott.marl...@gmail.com>wrote: >>> >>>> 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. >>>> >>> >>> >>> >>> -- >>> 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. > -- Bradley D. J. McCune
