Re: V8.4 TOAST table problem

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