Thanks, Scott. Currently, it's a bit difficult due to resources for a complete copy of the database to be useful. I won't get into the details, but it just wasn't an option at the time. With that said, I'm definitely making it a major concern of ours for such future issues, so post mortem and such is possible (probably via virtual instances). As always, I appreciate the response.
On Fri, Jul 12, 2013 at 5:34 PM, Scott Marlowe <scott.marl...@gmail.com>wrote: > It's always a good idea to keep a copy of the database for a post mortem > if possible. If you've found a bug, it's nice to find and fix it. If you > were suffering from an operational failure of some sort, then it helps to > figure that out too. > > > On Fri, Jul 12, 2013 at 2:42 PM, Bradley McCune > <bradley.mcc...@noaa.gov>wrote: > >> 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 >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> To understand recursion, one must first understand recursion. >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> To understand recursion, one must first understand recursion. >>>>> >>>> >>>> >>>> >>>> -- >>>> Bradley D. J. McCune >>>> >>>> >>>> >>> >>> >>> -- >>> To understand recursion, one must first understand recursion. >>> >> >> >> >> -- >> Bradley D. J. McCune >> > > > > -- > To understand recursion, one must first understand recursion. >
