V8.4 TOAST table problem

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

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.

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

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.

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

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.

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general