Re: V8.4 TOAST table problem

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(dot)mccune(at)noaa(dot)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(dot)marlowe(at)gmail(dot)com>wrote:
>
>> So what id
>> select * from pg_prepared_xacts ;
>> show?
>>
>>
>> On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune <bradley(dot)mccune(at)noaa(dot)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(dot)marlowe(at)gmail(dot)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(dot)marlowe(at)gmail(dot)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(dot)mccune(at)noaa(dot)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(dot)marlowe(at)gmail(dot)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(dot)mccune(at)noaa(dot)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 <davidw(at)dedasys(dot)com>
>>>>>>> wrote:
>>>>>>> >>
>>>>>>> >> Hi,
>>>>>>> >>
>>>>>>> >> I have a very similar problem... details below.
>>>>>>> >>
>>>>>>> >> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul(dot)tilles(at)noaa(dot)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
>

--
To understand recursion, one must first understand recursion.