Postgres Pro
Downloads
Home   >   mailing lists

Thread: V8.4 TOAST table problem

V8.4 TOAST table problem

From
Paul Tilles
Date:

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.

Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table.

SELECT nspname || '.' || relname AS "relation",   pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

Which produces:

relation | size
--+---------------------------------
pg_toast.pg_toast_16874 | 89 GB
fews00.warmstates | 1095 MB
...
(20 rows)

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.

I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors.

INFO: vacuuming "pg_toast.pg_toast_16874"
INFO: "pg_toast_16874": found 22483 removable, 10475318 nonremovable row versions in 10448587 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2036 bytes long.
There were 20121422 unused item pointers.
Total free space (including removable row versions) is 0 bytes. 4944885 pages are or will become empty, including 0 at the end of the table. 4944885 pages containing 0 free bytes are potential move destinations.
CPU 75.31s/29.59u sec elapsed 877.79 sec.
INFO: index "pg_toast_16874_index" now contains 10475318 row versions in 179931 pages
DETAIL: 23884 index row versions were removed.
101623 index pages have been deleted, 101623 are currently reusable.
CPU 1.35s/2.46u sec elapsed 21.07 sec.

REINDEXed the table which freed [b]some[/b] space (~1GB). I can't CLUSTER the table as there isn't enough space on disk for the process, and I'm waiting to rebuild the table entirely as I'd like to find out why it is so much bigger than equivalent databases we have.

Ran a query from the PostgreSQL wiki here - "Show Database Bloat", and this is what I get:

current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes
------------------+------------+--------------------------------+--------+-------------+---------------------------------+--------+--------------
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_synchlevel | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_localavail | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expirytime | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expiry_null | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | uniq_localintid | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | pk_timeseries | 0.1 | 0
ptrdb04 | fews00 | idx_timeseries_expiry_null | 0.6 | 0 | ? | 0.0 | 0

It looks like the database doesn't consider this space as "empty," at all, but I just don't see where all the disk space is coming from!

I suspect that this database server is deciding to use 4-5x as much disk space to save the same records pulled from the other data servers. My question is this: Is there a way I can verify the physical disk size of a row? I'd like to compare the size of one row on this database to another "healthy" database.

Thanks for any help you can provide!

Paul Tilles

Re: V8.4 TOAST table problem

From
David Welton
Date:
Hi,

I have a very similar problem... details below.

On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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/

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
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@dedasys.com> wrote:
Hi,

I have a very similar problem... details below.

On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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

Re: V8.4 TOAST table problem

From
Scott Marlowe
Date:
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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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.

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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


Re: V8.4 TOAST table problem

From
Scott Marlowe
Date:
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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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.

Re: V8.4 TOAST table problem

From
Scott Marlowe
Date:
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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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.

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
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.marlowe@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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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


Re: V8.4 TOAST table problem

From
Scott Marlowe
Date:
So what id 
select * from pg_prepared_xacts ;
show?


On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune <bradley.mccune@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.marlowe@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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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.

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
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.marlowe@gmail.com> wrote:
So what id 
select * from pg_prepared_xacts ;
show?


On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune <bradley.mccune@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.marlowe@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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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

Re: V8.4 TOAST table problem

From
Scott Marlowe
Date:
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.mccune@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.marlowe@gmail.com> wrote:
So what id 
select * from pg_prepared_xacts ;
show?


On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune <bradley.mccune@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.marlowe@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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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



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

Re: V8.4 TOAST table problem

From
David Welton
Date:
Hi,

I think I could write a script to do something similar to what is
happening if anyone is interested.  I'd want some direction as to the
best way to handle this though: it'd be easier for me to script it as
Rails code because that's what the app is.  Perhaps from that we can
get the generated SQL so as to make it easier for others to deal with.
 The operation itself is basically:

* Extract a value from a row of a table that is stored as a bytea.

* Unmarshall it into a Ruby object.

* Add to that Ruby object.

* update the row and set the value by marshalling the Ruby object.

I suspect that the actual value isn't terribly relevant, and they
how's and why's of what it is like it is are best left for a different
discussion.

--
David N. Welton

http://www.dedasys.com/

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
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.marlowe@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.mccune@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.marlowe@gmail.com> wrote:
So what id 
select * from pg_prepared_xacts ;
show?


On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune <bradley.mccune@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.marlowe@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.marlowe@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.mccune@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.marlowe@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.mccune@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 <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I have a very similar problem... details below.
>>
>> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@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.

Re: V8.4 TOAST table problem

From
Bradley McCune
Date:
David,

I'm sorry, but I'm not sure that I follow how this is pertinent to this particular thread.  Are you proposing a way to replicate the scenario we experienced of our massively bloated TOAST table?  If so, I'm not entirely sure that's doable given that the source of the issue was never clear.  There still remains a number of reasons for why that table had so much "still in use" bloat.  At this moment, it's near impossible to tell given that it is no longer a problem.

Thanks for the offer, and I apologize if I'm just slightly ignorant about your intentions.


On Mon, Jul 15, 2013 at 4:33 AM, David Welton <davidw@dedasys.com> wrote:
Hi,

I think I could write a script to do something similar to what is
happening if anyone is interested.  I'd want some direction as to the
best way to handle this though: it'd be easier for me to script it as
Rails code because that's what the app is.  Perhaps from that we can
get the generated SQL so as to make it easier for others to deal with.
 The operation itself is basically:

* Extract a value from a row of a table that is stored as a bytea.

* Unmarshall it into a Ruby object.

* Add to that Ruby object.

* update the row and set the value by marshalling the Ruby object.

I suspect that the actual value isn't terribly relevant, and they
how's and why's of what it is like it is are best left for a different
discussion.

--
David N. Welton

http://www.dedasys.com/



--
Bradley D. J. McCune

Re: V8.4 TOAST table problem

From
David Welton
Date:
Hi,

I'm talking about our own massively bloated toast table - described in
an earlier post - that I think I can replicate.  I didn't mean to
steal your thread, but the problem seems very similar, and we're using
9.1.  I don't know a lot about Postgres internals, but to me it smells
like a bug of some sort.

On Mon, Jul 15, 2013 at 7:23 PM, Bradley McCune <bradley.mccune@noaa.gov> wrote:
> David,
>
> I'm sorry, but I'm not sure that I follow how this is pertinent to this
> particular thread.  Are you proposing a way to replicate the scenario we
> experienced of our massively bloated TOAST table?  If so, I'm not entirely
> sure that's doable given that the source of the issue was never clear.
> There still remains a number of reasons for why that table had so much
> "still in use" bloat.  At this moment, it's near impossible to tell given
> that it is no longer a problem.
>
> Thanks for the offer, and I apologize if I'm just slightly ignorant about
> your intentions.
>
>
> On Mon, Jul 15, 2013 at 4:33 AM, David Welton <davidw@dedasys.com> wrote:
>>
>> Hi,
>>
>> I think I could write a script to do something similar to what is
>> happening if anyone is interested.  I'd want some direction as to the
>> best way to handle this though: it'd be easier for me to script it as
>> Rails code because that's what the app is.  Perhaps from that we can
>> get the generated SQL so as to make it easier for others to deal with.
>>  The operation itself is basically:
>>
>> * Extract a value from a row of a table that is stored as a bytea.
>>
>> * Unmarshall it into a Ruby object.
>>
>> * Add to that Ruby object.
>>
>> * update the row and set the value by marshalling the Ruby object.
>>
>> I suspect that the actual value isn't terribly relevant, and they
>> how's and why's of what it is like it is are best left for a different
>> discussion.
>>
>> --
>> David N. Welton
>>
>> http://www.dedasys.com/
>
>
>
>
> --
> Bradley D. J. McCune



--
David N. Welton

http://www.dedasys.com/